sql经典面试题
group by
create table teacher(teacher nvarchar(10),teach_day int,teach_desc nchar(1));
insert into teacher values('老师1',2,'有')
insert into teacher values('老师1',3,'有')
insert into teacher values('老师1',3,'有')
insert into teacher values('老师2',2,'无')
insert into teacher values('老师2',2,'有')
insert into teacher values('老师2',1,'有')
insert into teacher values('老师3',1,'有')
insert into teacher values('老师3',3,'无')
teacher teach_day teach_desc
老师1 2 有
老师1 3 有
老师1 3 有
老师2 2 无
老师2 2 有
老师2 1 有
老师3 1 有
老师3 3 无
Result:
teacher monday tuesday wednesday
老师1 0 1 2
老师2 1 1 0
老师3 1 0 0
select teacher,
sum(case when teach_day=1 then 1 else 0 end)'monday',
sum(case when teach_day=2 then 1 else 0 end)'tuesday',
sum(case when teach_day=3 then 1 else 0 end)'wednesday'
from teacher where teach_desc='有' group by teacher
Linq:
from t in Teachers
where t.Teach_desc=='有'
group t by t.Content into g
select new{
teacher=g.Key,
monday=g.Sum(s=>s.Teach_day==1?1:0),
tuesday=g.Sum(s=>s.Teach_day==2?1:0),
wednesday=g.Sum(s=>s.Teach_day==3?1:0)
}
对年月日分组查询,统计每月每天每年的活跃人数,根据登陆日期
create table logininfo(name varchar(50),logindate datetime)
insert into logininfo values('dnadan',getdate())
insert into logininfo values('c','2017/9/21 7:58:05')
insert into logininfo values('d','2017/9/20 0:58:05')
insert into logininfo values('c','2017/9/21 10:58:05')
insert into logininfo values('d','2017/9/20 12:58:05')
insert into logininfo values('e','2017/9/19 23:58:05')
insert into logininfo values('f','2017/9/20 20:58:05')
insert into logininfo values('g','2017/9/21 20:58:05')
insert into logininfo values('h','2015/9/19 20:58:05')
insert into logininfo values('i','2014/9/20 20:58:05')
insert into logininfo values('j','2016/9/21 20:58:05')
统计每天的登陆人数
select convert(date,logindate)date,count(*) from logininfo group by convert(date,logindate);
Linq:
from l in Logininfos
group l by l.Logindate.Value.Date into g
select new{date=g.Key,sum=g.Count()}
统计每月登陆人数
select convert(varchar(7),logindate,126),count(*) from logininfo group by convert(varchar(7),logindate,126)
Linq:
from l in Logininfos
group l by l.Logindate.Value.Year+"/"+(l.Logindate.Value.Month<10?"0":"")+l.Logindate.Value.Month into g
select new{date=g.Key,sum=g.Count()}
这里应该使用拼接,这样可以减少对子查询的嵌套查询
临时表和全局临时表
5.在sqlserver2000中请用sql创建一张用户临时表和系统临时表，里面包含两个字段ID和IDValues,类型都是int型，并解释下两者的区别?
------------------------------------------
用户临时表:create table #xx(ID int, IDValues int)
系统临时表:create table ##xx(ID int, IDValues int)
区别:
用户临时表只对创建这个表的用户的Session可见,对其他进程是不可见的.
当创建它的进程消失时这个临时表就自动删除.
全局临时表对整个SQL Server实例都可见,但是所有访问它的Session都消失的时候,它也自动删除.
使用Left Join 拼接出结果
dept_yeji
mon dep yj
-------------------------------
一月份 01 10
一月份 02 10
一月份 03 5
二月份 02 8
二月份 04 9
三月份 03 8
dept
dep dname
--------------------------------
01 国内业务一部
02 国内业务二部
03 国内业务三部
04 国际业务部
result
dep 一月份 二月份 三月份
--------------------------------------
01 10 null null
02 10 8 null
03 5 null 8
04 null 9 null
create table dept_yeji(mon varchar(10),dep varchar(2),yj int)
create table dept(dep varchar(2),dname varchar(20))
insert into dept_yeji values('一月份','01',10)
insert into dept_yeji values('一月份','02',10)
insert into dept_yeji values('一月份','03',5)
insert into dept_yeji values('二月份','02',8)
insert into dept_yeji values('二月份','04',9)
insert into dept_yeji values('三月份','03',8)
insert into dept values('01','国内业务一部')
insert into dept values('02','国内业务二部')
insert into dept values('03','国内业务三部')
insert into dept values('04','国内业务部')
select a.dep,b.yj '一月份',c.yj '二月份',d.yj '三月份'
from dept a
left join dept_yeji b on a.dep=b.dep and b.mon='一月份'
left join dept_yeji c on a.dep=c.dep and c.mon='二月份'
left join dept_yeji d on a.dep=d.dep and d.mon='三月份'
Linq:
from a in Depts
join b in Dept_yejis on new{a.Dep,Mon="一月份"} equals new{b.Dep,b.Mon} into bs
from b in bs.DefaultIfEmpty()
join c in Dept_yejis on new{a.Dep,Mon="二月份"} equals new{c.Dep,c.Mon} into cs
from c in cs.DefaultIfEmpty()
join d in Dept_yejis on new{a.Dep,Mon="三月份"} equals new{d.Dep,d.Mon} into ds
from d in ds.DefaultIfEmpty()
select new{
a.Dep,
first=b.Yj,
second=c.Yj,
thired=d.Yj
}
综合使用
payrec
fcr_id fcr_date rank fcr_busType fcr_curr fcr_amount
1 2016-03-22 00:00:00.000 销售家具 收 cny 720
2 2015-01-12 00:00:00.000 销售家具 收 cny 12090
3 2015-01-12 00:00:00.000 支付房租 付 cny -9091
4 2015-01-12 00:00:00.000 红星转账 收 cny 100000
5 2015-01-12 00:00:00.000 购买家具100件 付 cny -10918
6 2015-01-12 00:00:00.000 购买家具30件 付 cny -4069
7 2015-01-14 00:00:00.000 购买家具150件 付 cny -20884
8 2015-01-14 11:00:00.000 购买家具100件 付 cny -10394
9 2015-01-14 10:00:00.000 销售家具 收 cny 39800
10 2015-01-14 00:00:00.000 支付欠款 付 cny -8800
11 2015-01-14 00:00:00.000 支付欠款 付 cny -40000
12 2015-01-15 00:00:00.000 红星转账 收 cny 710
13 2015-01-15 00:00:00.000 红星转账 收 cny 810
14 2015-01-15 00:00:00.000 红星转账 收 cny 13010
15 2015-01-15 00:00:00.000 红星转账 收 cny 3800
统计结果
1 2015-01-12 00:00:00.000 12090 12090
2 2015-01-12 00:00:00.000 -9091 2999
3 2015-01-12 00:00:00.000 100000 102999
4 2015-01-12 00:00:00.000 -10918 92081
5 2015-01-12 00:00:00.000 -4069 88012
6 2015-01-14 00:00:00.000 -20884 67128
7 2015-01-14 00:00:00.000 -8800 58328
8 2015-01-14 00:00:00.000 -40000 18328
9 2015-01-14 10:00:00.000 39800 58128
10 2015-01-14 11:00:00.000 -10394 47734
11 2015-01-15 00:00:00.000 710 48444
12 2015-01-15 00:00:00.000 810 49254
13 2015-01-15 00:00:00.000 13010 62264
14 2015-01-15 00:00:00.000 3800 66064
15 2016-03-22 00:00:00.000 720 66784
需要使用到临时表 和 row_num() over (order by xxx) 和 case when
create table payrec(
fcr_id int identity(1,1),
fcr_date datetime,
fcr_remark varchar(20),
fcr_busType varchar(2),
fcr_curr varchar(5),
fcr_amount decimal
)
insert into payrec values
('2016/3/22','销售家具','收','cny',720), -- 这里故意放了一条时间靠后的记录在这里,是为了用fcr_date来排序,可以用临时表先排序
('2015/1/12','销售家具','收','cny',12090),
('2015/1/12','支付房租','付','cny',-9090.84),
('2015/1/12','红星转账','收','cny',100000),
('2015/1/12','购买家具100件','付','cny',-10917.68),
('2015/1/12','购买家具30件','付','cny',-4069.22),
('2015/1/14','购买家具150件','付','cny',-20883.55),
('2015/1/14 11:00','购买家具100件','付','cny',-10394.44),
('2015/1/14 10:00','销售家具','收','cny',39800),
('2015/1/14','支付欠款','付','cny',-8800),
('2015/1/14','支付欠款','付','cny',-40000),
('2015/1/15','红星转账','收','cny',710),
('2015/1/15','红星转账','收','cny',810),
('2015/1/15','红星转账','收','cny',13010),
('2015/1/15','红星转账','收','cny',3800)
create procedure query_fcr
as
begin
select rowid=row_number() over (order by fcr_date),* into #payrec from payrec ; -- 临时表存储排序结果
select rowid,fcr_date ,
(case when fcr_amount >0 then str(fcr_amount) else '' end) '收',
(case when fcr_amount<0 then str(fcr_amount) else '' end) '付',
'余额'=(select sum(fcr_amount) from #payrec where rowid<=a.rowid) from #payrec a
end
select * from payrec
exec query_fcr