今天java新手群中有人出了这样一道面试题:
一张表数据如下
1900-1-1 胜
1900-1-1 胜
1900-1-1 负
1900-1-2 胜
1900-1-2 胜
写出一条SQL语句,使检索结果如下:
胜 负
1900-1-1 21
1900-1-2 20
create table test(Date varchar(50) null, Result varchar(50) null)
select distinct Date, sum(case Result when '胜' then 1 else 0 end) as '胜', sum(case Result when '负' then 1 else 0 end) as '负' from test group by date
select a.date,a.a1 胜,b.b1 负 from (select date,count(date) a1 from test where Result ='胜' group by date) a, (select date,count(date) b1 from test where Result ='负' group by date) b where a.date=b.date
我们不妨来复习一下CASE WHEN的语法。
CASE WHEN有两种用法,一种是类似上面例子中那样的简单CASE函数:
CASE result WHEN '胜' THEN 1 WHEN '负' THEN 2 ELSE 0 END
CASE WHEN result='胜' THEN 1 WHEN result='负' THEN 2 ELSE 0 END
select (case Gender when 1 then '男' when 2 then '女' else '其他' end) as Gender from Table1