SQL，自己想不出来
求一个SQL，自己想不出来
每天会有白班和晚班，统计员每天录入白班工作几小时，晚班工作几小时。
要查出1月1日到1月4日，统计员漏输了哪天哪个班次的数据。
结果是 '2014-01-02 01:01:01','晚班' 漏输
------解决方案--------------------
------解决方案--------------------
试试这个：
每天会有白班和晚班，统计员每天录入白班工作几小时，晚班工作几小时。
要查出1月1日到1月4日，统计员漏输了哪天哪个班次的数据。
Drop table [Test]
Create table [Test](
Id [int] Identity(1,1),
CDate DateTime,
bc varchar(50),
WorkTime Float
)
insert into [Test](cDate,bc,workTime)values('2014-01-01 01:01:01','白班',8)
insert into [Test](cDate,bc,workTime)values('2014-01-01 01:01:01','晚班',8)
insert into [Test](cDate,bc,workTime)values('2014-01-02 01:01:01','白班',8)
insert into [Test](cDate,bc,workTime)values('2014-01-03 01:01:01','白班',8)
insert into [Test](cDate,bc,workTime)values('2014-01-03 01:01:01','晚班',8)
insert into [Test](cDate,bc,workTime)values('2014-01-04 01:01:01','白班',8)
insert into [Test](cDate,bc,workTime)values('2014-01-04 01:01:01','晚班',8)
结果是 '2014-01-02 01:01:01','晚班' 漏输
------解决方案--------------------
--Drop table [Test]
--Create table [Test](
--Id INT Identity(1,1),
--CDate DateTime,
--bc varchar(50),
--WorkTime Float
--)
--insert into [Test](cDate,bc,workTime)values('2014-01-01 01:01:01','白班',8)
--insert into [Test](cDate,bc,workTime)values('2014-01-01 01:01:01','晚班',8)
--insert into [Test](cDate,bc,workTime)values('2014-01-02 01:01:01','白班',8)
--insert into [Test](cDate,bc,workTime)values('2014-01-03 01:01:01','白班',8)
--insert into [Test](cDate,bc,workTime)values('2014-01-03 01:01:01','晚班',8)
--insert into [Test](cDate,bc,workTime)values('2014-01-04 01:01:01','白班',8)
--insert into [Test](cDate,bc,workTime)values('2014-01-04 01:01:01','晚班',8)
SELECT cdate,CASE WHEN bc='白班' THEN '晚班' WHEN bc='晚班' THEN '白班' ELSE '白班晚班均缺失' END '缺失班次' FROM Test
WHERE CONVERT(DATE,cdate) IN (
SELECT CONVERT(DATE,cdate)cdate
FROM test a
GROUP BY CONVERT(DATE,cdate)
HAVING COUNT(1)<>2)
/*
cdate 缺失班次
----------------------- --------------
2014-01-02 01:01:01.000 晚班
*/
------解决方案--------------------
试试这个：
Drop table [Test]
go
Create table [Test](
Id [int] Identity(1,1),
CDate DateTime,
bc varchar(50),
WorkTime Float
)
insert into [Test](cDate,bc,workTime)values('2014-01-01 01:01:01','白班',8)
insert into [Test](cDate,bc,workTime)values('2014-01-01 01:01:01','晚班',8)
insert into [Test](cDate,bc,workTime)values('2014-01-02 01:01:01','白班',8)
insert into [Test](cDate,bc,workTime)values('2014-01-03 01:01:01','白班',8)
insert into [Test](cDate,bc,workTime)values('2014-01-03 01:01:01','晚班',8)
insert into [Test](cDate,bc,workTime)values('2014-01-04 01:01:01','白班',8)
insert into [Test](cDate,bc,workTime)values('2014-01-04 01:01:01','晚班',8)
go
select a.cdate,b.bc
from
(
select DATEADD(day,number,(select min(cdate) from test)) as cdate
from master..spt_values t
where t.type = 'P'
and t.number <= (select datediff(day,min(CDate),MAX(CDate)) from test)
)a
inner join
(
select distinct bc
from test
)b
on 1 = 1
left join test
on a.cdate = test.CDate and
b.bc = test.bc
where test.CDate is null
/*
cdate bc
2014-01-02 01:01:01.000 晚班
*/