病人的体温 随机测量病人 时间 温度
甲 0:10 37.3
甲 1:20 37.2
甲 4:30 37.1.. ... ...
甲 23:55 37.9.. ... ...
乙 11:01 37.2
.. ... ...丁 ... ...
.. ... ...
求 每个病人 温度连续超过37.4 的最长时段(按次数计)比如 某个病人一天中 也许有 若干时段 超过 37.4 ,选出最长的。如果只有一个小时(不连续)超过37.4 ,就不显示。比如 甲的时段 是 14:01-20:10 丁的时段 是 11:01-2:00 等等 温度是随机变化的,没有规律。2000系统
甲 0:10 37.3
甲 1:20 37.2
甲 4:30 37.1.. ... ...
甲 23:55 37.9.. ... ...
乙 11:01 37.2
.. ... ...丁 ... ...
.. ... ...
求 每个病人 温度连续超过37.4 的最长时段(按次数计)比如 某个病人一天中 也许有 若干时段 超过 37.4 ,选出最长的。如果只有一个小时(不连续)超过37.4 ,就不显示。比如 甲的时段 是 14:01-20:10 丁的时段 是 11:01-2:00 等等 温度是随机变化的,没有规律。2000系统
insert into tw select '甲','0:10:00',37.2
insert into tw select '甲','1:20:00',37.1
insert into tw select '甲','3:30:00',37.5
insert into tw select '甲','4:10:00',37.6
insert into tw select '甲','5:30:00',37.2
insert into tw select '甲','6:15:00',37.6
insert into tw select '甲','7:20:00',37.5
insert into tw select '甲','8:40:00',37.6
insert into tw select '甲','10:10:00',37.3
insert into tw select '甲','15:10:00',37.2
insert into tw select '乙','9:10:00',37.2
insert into tw select '乙','10:5:00',37.5
insert into tw select '乙','10:40:00',37.6
insert into tw select '乙','10:50:00',37.2
go
;with c1 as(
select rank()over(partition by br order by sj)cn,* from tw
),c2 as(
select * from c1 a where tw>=37.4 and not exists(select 1 from c1 where br=a.br and tw>37.4 and cn=a.cn-1)
union all
select * from c1 a where tw>=37.4 and not exists(select 1 from c1 where br=a.br and tw>37.4 and cn=a.cn+1)
),c3 as(
select rank() over(partition by br order by cn)cn,br,sj,tw from c2
),c4 as(
select a.br,datediff(mi,a.sj,b.sj)sj from c3 a inner join c3 b on a.br=b.br and a.cn=b.cn-1
where a.cn%2=1
)select br,max(sj)sj from c4 where sj>=60 group by br
/*
br sj
---------- -----------
甲 145(1 行受影响)
*/
go
drop table tw
insert into tw select '甲','0:10:00',37.2
insert into tw select '甲','1:20:00',37.1
insert into tw select '甲','3:30:00',37.5
insert into tw select '甲','4:10:00',37.6
insert into tw select '甲','5:30:00',37.2
insert into tw select '甲','6:15:00',37.6
insert into tw select '甲','7:20:00',37.5
insert into tw select '甲','8:40:00',37.6
insert into tw select '甲','10:10:00',37.3
insert into tw select '甲','15:10:00',37.2
insert into tw select '乙','9:10:00',37.2
insert into tw select '乙','10:5:00',37.5
insert into tw select '乙','10:40:00',37.6
insert into tw select '乙','10:50:00',37.2
go
;with c1 as(
select rank()over(partition by br order by sj)cn,* from tw
),c2 as(
select * from c1 a where tw>=37.4 and not exists(select 1 from c1 where br=a.br and tw>37.4 and cn=a.cn-1)
union all
select * from c1 a where tw>=37.4 and not exists(select 1 from c1 where br=a.br and tw>37.4 and cn=a.cn+1)
),c3 as(
select rank() over(partition by br order by cn)cn,br,sj,tw from c2
),c4 as(
select a.br,a.sj sj1,b.sj sj2,datediff(mi,a.sj,b.sj)sjc from c3 a inner join c3 b on a.br=b.br and a.cn=b.cn-1
where a.cn%2=1
)select br,convert(varchar(5),sj1,108)sj1,convert(varchar(5),sj2,108)sj2 from c4 a where sjc>=60 and not exists(select 1 from c4 where br=a.br and sjc>a.sjc)
/*
br sj1 sj2
---------- ----- -----
甲 06:15 08:40(1 行受影响)*/
go
drop table tw
如果不是要这样,最好提供完整的测试数据,然后贴出应该得出的结果。这样就很好理解了。
SELECT '甲','01:00','37.2' UNION ALL
SELECT '甲','01:11','37.7' UNION ALL
SELECT '甲','03:00','36.8' UNION ALL
SELECT '甲','03:30','36.7' UNION ALL
SELECT '甲','05:00','36.5' UNION ALL
SELECT '甲','06:10','39' UNION ALL
SELECT '甲','07:31','39.2' UNION ALL
SELECT '甲','08:10','38.7' UNION ALL
SELECT '甲','09:02','39.3' UNION ALL
SELECT '甲','10:50','39.4' UNION ALL
SELECT '甲','11:00','36.6' UNION ALL
SELECT '甲','12:00','36.7' UNION ALL
SELECT '甲','13:00','39.9' UNION ALL
SELECT '甲','14:00','37.3' UNION ALL
SELECT '甲','15:00','38.9' UNION ALL
SELECT '甲','16:00','37.1' UNION ALL
SELECT '甲','17:00','39.6' UNION ALL
SELECT '甲','18:00','37.6' UNION ALL
SELECT '甲','19:00','36.6' UNION ALL
SELECT '甲','20:00','37' UNION ALL
SELECT '甲','21:00','38.2' UNION ALL
SELECT '甲','22:00','37.7' UNION ALL
SELECT '甲','23:00','38.8' UNION ALL
SELECT '乙','00:00','36.8' UNION ALL
SELECT '乙','01:00','38.6' UNION ALL
SELECT '乙','02:00','36.8' UNION ALL
SELECT '乙','03:00','36.5' UNION ALL
SELECT '乙','04:00','36.8' UNION ALL
SELECT '乙','05:00','37.4' UNION ALL
SELECT '乙','06:00','36.8' UNION ALL
SELECT '乙','07:00','39.1' UNION ALL
SELECT '乙','08:00','36.9' UNION ALL
SELECT '乙','09:00','36.8' UNION ALL
SELECT '乙','10:00','38.2' UNION ALL
SELECT '乙','11:00','37.3' UNION ALL
SELECT '乙','12:00','38.9' UNION ALL
SELECT '乙','13:00','36.9' UNION ALL
SELECT '乙','14:00','39.4' UNION ALL
SELECT '乙','15:00','36.8' UNION ALL
SELECT '乙','16:00','39.6' UNION ALL
SELECT '乙','17:00','36.2' UNION ALL
SELECT '乙','18:00','37.4' UNION ALL
SELECT '乙','19:00','36.9' UNION ALL
SELECT '乙','20:00','36.8' UNION ALL
SELECT '乙','21:00','37.4' UNION ALL
SELECT '乙','22:00','36.8' UNION ALL
SELECT '乙','23:00','36.6' UNION ALL
SELECT '丙','00:00','38.7' UNION ALL
SELECT '丙','01:00','37.4' UNION ALL
SELECT '丙','02:00','39.3' UNION ALL
SELECT '丙','03:00','37.5' UNION ALL
SELECT '丙','04:00','36.8' UNION ALL
SELECT '丙','05:00','36.7' UNION ALL
SELECT '丙','06:00','36.6' UNION ALL
SELECT '丙','07:00','38.7' UNION ALL
SELECT '丙','08:00','39.3' UNION ALL
SELECT '丙','09:00','37.6' UNION ALL
SELECT '丙','10:00','37.3' UNION ALL
SELECT '丙','11:01','39' UNION ALL
SELECT '丙','11:20','39.6' UNION ALL
SELECT '丙','13:00','39.7' UNION ALL
SELECT '丙','14:00','38.1' UNION ALL
SELECT '丙','15:20','39.7' UNION ALL
SELECT '丙','16:00','36.5' UNION ALL
SELECT '丙','17:00','38.9' UNION ALL
SELECT '丙','18:00','38.8' UNION ALL
SELECT '丙','19:00','39.1' UNION ALL
SELECT '丙','20:00','36.6' UNION ALL
SELECT '丙','21:00','38.6' UNION ALL
SELECT '丙','22:00','37.6' UNION ALL
SELECT '丙','23:00','37.8'病人 时间段
---------- ---------------------
甲 06:10~10:50 5次
丙 11:01~15:20 5次和上次差不多, 只是 测量间隔 不再是一小时一次,也不固定时间了。 ABS(DATEDIFF(hh,t.时间,时间))=1 不能用了,
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([病人] [nvarchar](10),[时间] [nvarchar](10),[温度] [numeric](3,1))
INSERT INTO [tb]
SELECT '甲','00:00','38' UNION ALL
SELECT '甲','01:00','37.2' UNION ALL
SELECT '甲','01:11','37.7' UNION ALL
SELECT '甲','03:00','36.8' UNION ALL
SELECT '甲','03:30','36.7' UNION ALL
SELECT '甲','05:00','36.5' UNION ALL
SELECT '甲','06:10','39' UNION ALL
SELECT '甲','07:31','39.2' UNION ALL
SELECT '甲','08:10','38.7' UNION ALL
SELECT '甲','09:02','39.3' UNION ALL
SELECT '甲','10:50','39.4' UNION ALL
SELECT '甲','11:00','36.6' UNION ALL
SELECT '甲','12:00','36.7' UNION ALL
SELECT '甲','13:00','39.9' UNION ALL
SELECT '甲','14:00','37.3' UNION ALL
SELECT '甲','15:00','38.9' UNION ALL
SELECT '甲','16:00','37.1' UNION ALL
SELECT '甲','17:00','39.6' UNION ALL
SELECT '甲','18:00','37.6' UNION ALL
SELECT '甲','19:00','36.6' UNION ALL
SELECT '甲','20:00','37' UNION ALL
SELECT '甲','21:00','38.2' UNION ALL
SELECT '甲','22:00','37.7' UNION ALL
SELECT '甲','23:00','38.8' UNION ALL
SELECT '乙','00:00','36.8' UNION ALL
SELECT '乙','01:00','38.6' UNION ALL
SELECT '乙','02:00','36.8' UNION ALL
SELECT '乙','03:00','36.5' UNION ALL
SELECT '乙','04:00','36.8' UNION ALL
SELECT '乙','05:00','37.4' UNION ALL
SELECT '乙','06:00','36.8' UNION ALL
SELECT '乙','07:00','39.1' UNION ALL
SELECT '乙','08:00','36.9' UNION ALL
SELECT '乙','09:00','36.8' UNION ALL
SELECT '乙','10:00','38.2' UNION ALL
SELECT '乙','11:00','37.3' UNION ALL
SELECT '乙','12:00','38.9' UNION ALL
SELECT '乙','13:00','36.9' UNION ALL
SELECT '乙','14:00','39.4' UNION ALL
SELECT '乙','15:00','36.8' UNION ALL
SELECT '乙','16:00','39.6' UNION ALL
SELECT '乙','17:00','36.2' UNION ALL
SELECT '乙','18:00','37.4' UNION ALL
SELECT '乙','19:00','36.9' UNION ALL
SELECT '乙','20:00','36.8' UNION ALL
SELECT '乙','21:00','37.4' UNION ALL
SELECT '乙','22:00','36.8' UNION ALL
SELECT '乙','23:00','36.6' UNION ALL
SELECT '丙','00:00','38.7' UNION ALL
SELECT '丙','01:00','37.4' UNION ALL
SELECT '丙','02:00','39.3' UNION ALL
SELECT '丙','03:00','37.5' UNION ALL
SELECT '丙','04:00','36.8' UNION ALL
SELECT '丙','05:00','36.7' UNION ALL
SELECT '丙','06:00','36.6' UNION ALL
SELECT '丙','07:00','38.7' UNION ALL
SELECT '丙','08:00','39.3' UNION ALL
SELECT '丙','09:00','37.6' UNION ALL
SELECT '丙','10:00','37.3' UNION ALL
SELECT '丙','11:01','39' UNION ALL
SELECT '丙','11:20','39.6' UNION ALL
SELECT '丙','13:00','39.7' UNION ALL
SELECT '丙','14:00','38.1' UNION ALL
SELECT '丙','15:20','39.7' UNION ALL
SELECT '丙','16:00','36.5' UNION ALL
SELECT '丙','17:00','38.9' UNION ALL
SELECT '丙','18:00','38.8' UNION ALL
SELECT '丙','19:00','39.1' UNION ALL
SELECT '丙','20:00','36.6' UNION ALL
SELECT '丙','21:00','38.6' UNION ALL
SELECT '丙','22:00','37.6' UNION ALL
SELECT '丙','23:00','37.8'-->SQL查询如下:
SELECT *,id=IDENTITY(INT) INTO #t FROM tb tSELECT 病人,MIN(时间) a,MAX(时间) b,count(1) cnt
INTO #
FROM (
SELECT *,id-(SELECT COUNT(1) FROM #t WHERE 病人=t.病人 AND 温度>=37.4 AND 时间<=t.时间) gp
FROM #t t
WHERE EXISTS(
SELECT 1
FROM #t
WHERE 病人=t.病人
AND ABS(id-t.id)=1
AND 温度>=37.4
)
AND 温度>=37.4
) t
GROUP BY 病人,gpSELECT 病人,a+'~'+b 时间段,cnt 次数 FROM # t WHERE cnt=(SELECT MAX(cnt) FROM # WHERE 病人=t.病人)DROP TABLE #,#t
/*
病人 时间段 次数
---------- --------------------- -----------
甲 06:10~10:50 5
丙 11:01~15:20 5(2 行受影响)
*/
我头一次见这样的方法,想不明白:(SELECT COUNT(1) FROM #t WHERE 病人=t.病人 AND 温度>=37.4 AND 时间<=t.时间)