有个log表
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
……
想要实现的结果是，列出在当前时间的前一个月中status只有“维修”的所有数据 和 列出在当前时间的前两个月中状态为“维修”的次数大于1的所有数据。
问题比较业余，请各位大大给个SQL查询语句，谢谢！
------解决思路----------------------
use tempdb
if 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'
------解决思路----------------------
你的描述好有歧义~
你结合你的数据，说下你想要什么样的结果
------解决思路----------------------
DECLARE @date1 datetime --前前月初
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
------解决思路----------------------
SELECT * FROM TB WHERE status='维修'AND creatTime>=DATEADD(MONTH,-1,GETDATE())直接这样就可以了
------解决思路----------------------
我#9的代码，按照你的月份划分规则，调整下 @date3、@date2 的计算逻辑，其他可以不用改了。