续 | 几道常见的SQL面试题，看你能答对几道

点击上方SQL数据库开发，关注获取SQL视频教程

上次已经发布过一些常见的面试题《几道常见的SQL面试题，看你能答对几道？》，再给大家发布几道。

3.请取出tb_send表中日期(SendTime字段)为当天的所有记录?

(SendTime字段为datetime型，包含日期与时间)

参考答案

1、这题主要考一个行列转换的问题，使用CASE WHEN即可解决。

create table tmp(Tdate varchar(10),Tresulte nchar(1));

insert into tmp values('2019-05-09','胜');
insert into tmp values('2019-05-09','胜');
insert into tmp values('2019-05-09','负');
insert into tmp values('2019-05-09','负');
insert into tmp values('2019-05-10','胜');
insert into tmp values('2019-05-10','负');
insert into tmp values('2019-05-10','负');

--方法一
select Tdate '日期',
sum(case when Tresulte ='胜' then 1 else 0 end)'胜',
sum(case when Tresulte ='负' then 1 else 0 end)'负' 
from tmp group by Tdate ;
--方法二
select N.Tdate '日期',N.勝,M.負
from (
select Tdate ,count(*) '胜' from tmp where Tresulte ='胜' group by Tdate ) N
inner join
(
select Tdate ,count(*) '负' from tmp where Tresulte ='负' group by Tdate ) M
on N.Tdate =M.Tdate ;

select 
(case when a>b then a else b end ),
(case when b>c then b esle c end)
from table_name

select * from tb
where datediff(dd,SendTime,getdate())=0

4、这个是对数据进行判断，也可以使用CASE WHEN来进行求解。

select
(case when 语文>=80 then '优秀'
      when 语文>=60 then '及格'
      else '不及格') as 语文,
(case when 数学>=80 then '优秀'
      when 数学>=60 then '及格'
      else '不及格') as 数学,
(case when 英语>=80 then '优秀'
      when 英语>=60 then '及格'
      else '不及格') as 英语,
from table

5、这一题就不解释了，还是CASE WHEN

select a.dep,
sum(case when b.mon=1 then b.yj else 0 end) as '一月份',
sum(case when b.mon=2 then b.yj else 0 end) as '二月份',
sum(case when b.mon=3 then b.yj else 0 end) as '三月份',
sum(case when b.mon=4 then b.yj else 0 end) as '四月份',
sum(case when b.mon=5 then b.yj else 0 end) as '五月份',
sum(case when b.mon=6 then b.yj else 0 end) as '六月份',
sum(case when b.mon=7 then b.yj else 0 end) as '七月份',
sum(case when b.mon=8 then b.yj else 0 end) as '八月份',
sum(case when b.mon=9 then b.yj else 0 end) as '九月份',
sum(case when b.mon=10 then b.yj else 0 end) as '十月份',
sum(case when b.mon=11 then b.yj else 0 end) as '十一月份',
sum(case when b.mon=12 then b.yj else 0 end) as '十二月份',
from table2 a left join table1 b on a.dep=b.dep

--方法一
select id,Count（*)
from tb
group by id 
having count(*)>1;
--方法二
select * from
(select count(ID) as count 
from table 
group by ID)T
where T.count>1

--连接查询
SELECT b.YEAR, SUM(a.salary) salary
FROM hello a, hello b
WHERE a.YEAR <= b.YEAR GROUP BY b.YEAR

--子查询
select year ,
(select sum(salary)
from hello as B
where B.year<=A.year )
from hello as A

• Oracle数据库常用十一大操作指令
  

• 项目中常用的 19 条 MySQL 优化技巧
  

• 24个必须掌握的数据库面试问题~
  

  
  

点击「阅读原文」了解SQL训练营