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/9 | 2 | 2 |
2005/5/10 | 1 | 2 |
建表:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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','负')
答案:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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
二,关于 case 选择
题目:表中有A B C三列,用SQL语句实现:当A列大于B列时选择A列,否则选择B列;当B列大于C列时选择B列,否则选择C列。
创建表:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
create table t_selmax(FA int, FB int, FC int) insert into t_selmax values(1, 2, 3) insert into t_selmax values(3, 2, 0) insert into t_selmax values(11, 4, 3) insert into t_selmax values(1, 12, 31)
实现结果:
a_b | b_c |
2 | 3 |
3 | 2 |
11 | 4 |
12 | 31 |
答案:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
select (case when [FA] > [FB] then [FA] else [FB] end) a_b, (case when [FB] > [FC] then [FB] else [FC] end) b_c from t_selmax
三,关于评分
题目:有一张表,里面有3个字段:语文,数学,英语。其中有3条记录分别表示语文70分,数学80分,英语58分,请用一条sql语句查询出这三条记录并按以下条件显示出来:
语文 | 数学 | 英语 |
及格 | 优秀 | 不及格 |
创建表:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
create table t_grade_mark(cn float, math float, en float) insert into t_grade_mark values(78, 78, 90) insert into t_grade_mark values(33, 67, 71) insert into t_grade_mark values(66, 78, 69) insert into t_grade_mark values(87, 88, 46) insert into t_grade_mark values(70, 80, 58) insert into t_grade_mark values(90, 66, 82)
答案:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
select (case when [cn]>=80 then '优秀' when [cn] >= 60 then '及格' when [cn] < 60 then '不及格' end) [语文], (case when [math]>=80 then '优秀' when [math] >= 60 then '及格' when [math] < 60 then '不及格' end) [数学], (case when [en]>=80 then '优秀' when [en] >= 60 then '及格' when [en] < 60 then '不及格' end) [英语] from t_grade_mark where [cn]=70 and [math]=80 and [en]=58
(补充),对每个字段都进行同样的case判断,我觉得代码有点冗余度.有谁有好的方法,欢迎提出.
四,group by 和 having(华为面试)
题目:一个表中的Id有多个记录,把所有这个id的记录查出来,并显示共有多少条记录数。
建表:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
create table t_ids(id nvarchar(5), name nvarchar(20) not null, age int) insert into t_ids values('x001', 'jcson', 20) insert into t_ids values('x001', 'domy', 21) insert into t_ids values('x002', 'jim', 22) insert into t_ids values('x003', 'lucy', 19) insert into t_ids values('a001', 'hoby', 27) insert into t_ids values('a001', 'kin', 20) insert into t_ids values('a001', 'cry', 23)
实现结果:
id | 个数 |
a001 | 3 |
x001 | 2 |
答案:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
select id, COUNT(*) [个数] from t_ids group by id having COUNT(*) > 1