笔试sql查三个表子查询
@向石4910：sql语句的面试题求解答啊,一共三个表,悬赏100啊 -
史泄19249589279…… 1,select e_name from employee where e_hiredate>to_date('2001-01-01','YYYY-MM-dd') and e_hiredate2,update employee set e_level=e_level+1,e_hiredate=sysdate where dept_id in(select dept_id from deptar where dept_name='监察部')
@向石4910：SQL 三表联查 -
史泄19249589279…… select t.*,t1.hsdwmc , from a t join b t1 on t1.hsdwmc =t.thsdwmc join c t2 on t.fzmc = t2.fzmc 我上面这样写的需要所有表都有关联才行,如果不是所有表有关联换下面这种写法 select t.*,t1.hsdwmc , t2.fzmc from a t left join b t1 on t1.hsdwmc =t.thsdwmc left join c t2 on t.fzmc = t2.fzmc
@向石4910：怎么用一句sql查询三个表并输出三个表的内容 -
史泄19249589279…… select table1.id,table1.title,table1.content ,table1.s_time from table1 union select table2.id , table2.title_a , table2.content ,table2.time from table2 union select table3.id , table3.title , table3.content ,table3.time from table3 order by s_time 肯定正确啦^_^
@向石4910：5张表,字段一模一样,每张记录的是学生的一次考试成绩,想通过一条sql查询5张表中所有name='abc'的结果 -
史泄19249589279…… select * from tb1 where name = 'abc' union select * from tb2 where name = 'abc' union select * from tb3 where name = 'abc' union select * from tb4 where name = 'abc' union select * from tb5 where name = 'abc'
@向石4910：求SQL查询语句的编写?1.查询全体学生的姓名、学号、性别和出生日期.2.查询考试成绩在65以上(含)的学生学号和成绩.3.查询“07计算机1”和“07计算... - 作业帮
史泄19249589279…… [答案] 1. 查询全体学生的姓名、学号、性别和出生日期.select 姓名,学号,性别,出生日期 from 学生信息表2. 查询考试成绩在65以上(含)的学生学号和成绩.select 学号,成绩 from 成绩表 where score>=653. 查询“07计算机1...
@向石4910：一道数据库的笔试题目有两个表 表一 AAA 种类mc 库存总量s1 A 997 B 1234 表二 BBB 种类mc 出库数量s1 A 105 A 213 B 116 B 211 B 303 A,B各剩下多少?
史泄19249589279…… SELECT mc,s1-(SELECT sum(bbb.s1) from BBB group by mc HAVING AAA.mc=BBB.mc) result from aaa思路:子查询,进行将A,B按mc种类进行求和.然后由于用了group by.所以用Having进行与aaa表进行匹配,从而实现加减算法.效果如图:
@向石4910：关于SQL DELETE嵌套子查询问题 -
史泄19249589279…… 子查询:SELECT COUNT(*)FROM stock WHERE stock.manu_code = '145378'的返回值如果是1的话,你的SQL就会是:DELETE FROM stock WHERE 1 = 1那么就会把所有数据删除的,所以SQL可以如下:DELETE FROM stock WHERE 1 = (SELECT COUNT(*) FROM stock WHERE stock.manu_code = '145378') AND stock.manu_code = '145378'
@向石4910：如何用一条SQL语句查询3个表中的内容? -
史泄19249589279…… 前提是表的结构应该是一样的或者处理成一样的~~~~ 表A, aid, aname, atext 表B, bid, bname, btext 表C, cid, cname, ctext SELECT * FROM ( SELECT aid AS id, aname AS name, atext AS [text] FROM A UNION SELECT bid AS id, bname AS ...
@向石4910：用SQL语句怎么查询三个表的结果 -
史泄19249589279…… select table_a.D,table_a.E,table_b.F (select A.D as D,sum(B.E) as E from A,B where A.D=B.D group by A.D) table_a, (select A.D as D,sum(C.F) as F from A,B where A.D=C.D group by A.D) table_b where table_a.D=table_b.D
@向石4910：三表查询,求SQl语句 -
史泄19249589279…… select a.id,a.data,t.data2,t.data3 from a left join(select cc.a_id,bb.data data2,cc.data data3 from (select a_id,data,rank() over (partition by a_id order by data) rn from C) cc full join (select a_id,data,rank() over (partition by a_id order by data) rn from B...
史泄19249589279…… 1,select e_name from employee where e_hiredate>to_date('2001-01-01','YYYY-MM-dd') and e_hiredate2,update employee set e_level=e_level+1,e_hiredate=sysdate where dept_id in(select dept_id from deptar where dept_name='监察部')
@向石4910：SQL 三表联查 -
史泄19249589279…… select t.*,t1.hsdwmc , from a t join b t1 on t1.hsdwmc =t.thsdwmc join c t2 on t.fzmc = t2.fzmc 我上面这样写的需要所有表都有关联才行,如果不是所有表有关联换下面这种写法 select t.*,t1.hsdwmc , t2.fzmc from a t left join b t1 on t1.hsdwmc =t.thsdwmc left join c t2 on t.fzmc = t2.fzmc
@向石4910：怎么用一句sql查询三个表并输出三个表的内容 -
史泄19249589279…… select table1.id,table1.title,table1.content ,table1.s_time from table1 union select table2.id , table2.title_a , table2.content ,table2.time from table2 union select table3.id , table3.title , table3.content ,table3.time from table3 order by s_time 肯定正确啦^_^
@向石4910：5张表,字段一模一样,每张记录的是学生的一次考试成绩,想通过一条sql查询5张表中所有name='abc'的结果 -
史泄19249589279…… select * from tb1 where name = 'abc' union select * from tb2 where name = 'abc' union select * from tb3 where name = 'abc' union select * from tb4 where name = 'abc' union select * from tb5 where name = 'abc'
@向石4910：求SQL查询语句的编写?1.查询全体学生的姓名、学号、性别和出生日期.2.查询考试成绩在65以上(含)的学生学号和成绩.3.查询“07计算机1”和“07计算... - 作业帮
史泄19249589279…… [答案] 1. 查询全体学生的姓名、学号、性别和出生日期.select 姓名,学号,性别,出生日期 from 学生信息表2. 查询考试成绩在65以上(含)的学生学号和成绩.select 学号,成绩 from 成绩表 where score>=653. 查询“07计算机1...
@向石4910：一道数据库的笔试题目有两个表 表一 AAA 种类mc 库存总量s1 A 997 B 1234 表二 BBB 种类mc 出库数量s1 A 105 A 213 B 116 B 211 B 303 A,B各剩下多少?
史泄19249589279…… SELECT mc,s1-(SELECT sum(bbb.s1) from BBB group by mc HAVING AAA.mc=BBB.mc) result from aaa思路:子查询,进行将A,B按mc种类进行求和.然后由于用了group by.所以用Having进行与aaa表进行匹配,从而实现加减算法.效果如图:
@向石4910：关于SQL DELETE嵌套子查询问题 -
史泄19249589279…… 子查询:SELECT COUNT(*)FROM stock WHERE stock.manu_code = '145378'的返回值如果是1的话,你的SQL就会是:DELETE FROM stock WHERE 1 = 1那么就会把所有数据删除的,所以SQL可以如下:DELETE FROM stock WHERE 1 = (SELECT COUNT(*) FROM stock WHERE stock.manu_code = '145378') AND stock.manu_code = '145378'
@向石4910：如何用一条SQL语句查询3个表中的内容? -
史泄19249589279…… 前提是表的结构应该是一样的或者处理成一样的~~~~ 表A, aid, aname, atext 表B, bid, bname, btext 表C, cid, cname, ctext SELECT * FROM ( SELECT aid AS id, aname AS name, atext AS [text] FROM A UNION SELECT bid AS id, bname AS ...
@向石4910：用SQL语句怎么查询三个表的结果 -
史泄19249589279…… select table_a.D,table_a.E,table_b.F (select A.D as D,sum(B.E) as E from A,B where A.D=B.D group by A.D) table_a, (select A.D as D,sum(C.F) as F from A,B where A.D=C.D group by A.D) table_b where table_a.D=table_b.D
@向石4910：三表查询,求SQl语句 -
史泄19249589279…… select a.id,a.data,t.data2,t.data3 from a left join(select cc.a_id,bb.data data2,cc.data data3 from (select a_id,data,rank() over (partition by a_id order by data) rn from C) cc full join (select a_id,data,rank() over (partition by a_id order by data) rn from B...
