首页 > 代码库 > sql - 面试

sql - 面试

一,关于 group by

表内容: 
2005-05-09 胜 
2005-05-09 胜 
2005-05-09 负 
2005-05-09 负 
2005-05-10 胜 
2005-05-10 负 
2005-05-10 负

 需要生成结果为:

 

Fday
2005/5/922
2005/5/1012

建表:

1 create table t_com(Fday varchar(10),Fwin_lose nchar(1))2 insert into t_com values(2005-05-09,)3 insert into t_com values(2005-05-09,)4 insert into t_com values(2005-05-09,)5 insert into t_com values(2005-05-09,)6 insert into t_com values(2005-05-10,)7 insert into t_com values(2005-05-10,)8 insert into t_com values(2005-05-10,)
View Code

答案:

1 --解释:sum中的then后的数字表示‘代表几‘,如‘1‘表示‘出现一个记录,基数加1‘.2 3 select Fday [日期],4 SUM(case when Fwin_lose= then 1 else 0 end) [],5 SUM(case when Fwin_lose= then 1 else 0 end) []6  from t_com group by Fday
View Code

 

sql - 面试