几种查询语句

--union all用来合并两张表的查询结果集,一个表中的Id有多个记录，把所有这个id的记录查出来，并显示共有多少条记录数。
select '编号',s1.stu_id from student s1 union all
select '合计',to_char(count(s2.stu_id)) as count from student s2
--不允许有重复的数据
select s1.stu_name from student s1 union select s2.stu_name from student s2
--可以存在重复的数据
select s1.stu_name from student s1 union all select s2.stu_name from student s2
--如果a大于b,则输出a,否则b;如果b大于c，则输出b，否则输出c
select 
(case when e.score_a > e.score_b then e.score_a else e.score_b end) as ab ,
(case when e.score_b > e.score_c then e.score_b else e.score_c end) as bc
from escore e
--查胜负
select s.s_date as d,
sum(case when s.s_content='胜' then 1 else 0 end) as 胜,
sum(case when s.s_content='负' then 1 else 0 end) as 负
from score s group by s.s_date

--查询成绩
select 
(case when t.yw >=80 then '优秀' when t.yw>=60 then '及格' else '不及格' end ) as 语文, 
(case when t.sx >=80 then '优秀' when t.sx>=60 then '及格' else '不及格' end ) as 数学, 
(case when t.yy >=80 then '优秀' when t.yy>=60 then '及格' else '不及格' end ) as 英语
from scores t

--两张表联合查询分组
select t2.depname,
sum(case when t1.month='一月份' then t1.yj else 0 end) as 一月份,
sum(case when t1.month='二月份' then t1.yj else 0 end) as 二月份,
sum(case when t1.month='三月份' then t1.yj else 0 end) as 三月份
from table2 t2 left join table1 t1 on t1.depid= t2.id group by t2.depname