use tempdbif object_id('log') is not null
drop table [log]
create table [log](id int identity(1,1),
devNO varchar(30),status varchar(50),creatTime smalldatetime)insert into [log]
select 123, '在库' ,'2014-10-10 10:00:00' union all
select 123, '维修' ,'2014-10-20 10:00:00' union all
select 123, '维修' ,'2014-10-20 10:00:00' union all
select 456, '维修' ,'2014-10-21 10:00:00' union all
select 123, '在库', '2014-10-24 10:00:00' union all
select 345, '在库', '2014-10-25 10:00:00' union all
select 123, '维修', '2014-11-15 10:00:00'
---前一个月中status只有“维修”的所有数据
select * from [log] a
where
not exists(select * from [log] where devNO=a.devNO and convert(varchar(6),creatTime,112)=
convert(varchar(6),dateadd(m,-1,getdate()),112)
and status<>'维修') and
convert(varchar(6),creatTime,112)=convert(varchar(6),dateadd(m,-1,getdate()),112)
--前两个月中状态为“维修”的次数大于1的所有数据
select a.*
from [log] a
join (select devNO from [log]
where creatTime>=convert(varchar(6),dateadd(m,-2,getdate()),112)+'01' and
creatTime<convert(varchar(6),getdate(),112) +'01' and status='维修'
group by devno
having count(id)>1) b on a.devNO=b.devNO
where a.creatTime>=convert(varchar(6),dateadd(m,-2,getdate()),112)+'01' and
a. creatTime<convert(varchar(6),getdate(),112) +'01'
drop table [log]
create table [log](id int identity(1,1),
devNO varchar(30),status varchar(50),creatTime smalldatetime)insert into [log]
select 123, '在库' ,'2014-10-10 10:00:00' union all
select 123, '维修' ,'2014-10-20 10:00:00' union all
select 123, '维修' ,'2014-10-20 10:00:00' union all
select 456, '维修' ,'2014-10-21 10:00:00' union all
select 123, '在库', '2014-10-24 10:00:00' union all
select 345, '在库', '2014-10-25 10:00:00' union all
select 123, '维修', '2014-11-15 10:00:00'
---前一个月中status只有“维修”的所有数据
select * from [log] a
where
not exists(select * from [log] where devNO=a.devNO and convert(varchar(6),creatTime,112)=
convert(varchar(6),dateadd(m,-1,getdate()),112)
and status<>'维修') and
convert(varchar(6),creatTime,112)=convert(varchar(6),dateadd(m,-1,getdate()),112)
--前两个月中状态为“维修”的次数大于1的所有数据
select a.*
from [log] a
join (select devNO from [log]
where creatTime>=convert(varchar(6),dateadd(m,-2,getdate()),112)+'01' and
creatTime<convert(varchar(6),getdate(),112) +'01' and status='维修'
group by devno
having count(id)>1) b on a.devNO=b.devNO
where a.creatTime>=convert(varchar(6),dateadd(m,-2,getdate()),112)+'01' and
a. creatTime<convert(varchar(6),getdate(),112) +'01'
加这个判断应该可以
and createTime>=(SELECT dateadd(month,-1,(Select Convert(varchar(10),Getdate(),111))))
这条语句可以查询出一个月(超时时间)中状态为“维修”的记录,但是这些超时的记录中我不知道其中有没有存在一些“在库”的记录,因为这条语句好像就只是查询出“维修”的记录。我想要实现的是在这一个月中只有“维修”没有“在库”记录的数据,不知道该怎么写?谢谢!
DECLARE @date2 datetime --前月初
DECLARE @date3 datetime --本月初
SET @date3 = Convert(datetime,
Convert(varchar(7),
GetDate(),
120)+'-01',
120)
SET @date2 = DateAdd(month,-1,@date3)
SET @date1 = DateAdd(month,-1,@date2)
--SELECT @date1,@date2,@date3;WITH t1 AS (--上月维修数
SELECT devNo,
COUNT(*) c
FROM log
WHERE creatTime >= @date2
AND creatTime < @date3
AND status = '维修'
GROUP BY devNo
)
,t2 AS (--上月记录数
SELECT devNo,
COUNT(*) c
FROM log
WHERE creatTime >= @date2
AND creatTime < @date3
GROUP BY devNo
)
,t3 AS (--前两月维修次数
SELECT devNo,
COUNT(*) c
FROM log
WHERE creatTime >= @date1
AND creatTime < @date3
AND status = '维修'
GROUP BY devNo
)
,t4 AS (--符合条件的设备
SELECT t1.devNo
FROM t1
JOIN t2
ON t2.devNo = t1.devNo
JOIN t3
ON t3.devNo = t1.devNo
WHERE t1.c = t2.c --维修数=记录数,即没有在库
AND t3.c > 1
)
SELECT log.*
FROM log
JOIN t4
ON t4.devNo = log.devNo
WHERE log.creatTime >= @date1
AND log.creatTime < @date3
id devNo status creatTime
1 123 在库 2014-10-10 10:00:00
2 123 维修 2014-10-20 10:00:00
3 456 维修 2014-10-21 10:00:00
4 123 在库 2014-10-24 10:00:00
5 345 在库 2014-10-25 10:00:00
6 123 维修 2014-11-15 10:00:00
……
我想要的是这样
3 456 维修 2014-10-21 10:00:00
6 123 维修 2014-11-15 10:00:00