34,590
社区成员
发帖
与我相关
我的任务
分享
--> 生成测试数据表: [tb]
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 '甲','02:00','37.7' UNION ALL
SELECT '甲','03:00','36.8' UNION ALL
SELECT '甲','04:00','36.7' UNION ALL
SELECT '甲','05:00','36.5' UNION ALL
SELECT '甲','06:00','39' UNION ALL
SELECT '甲','07:00','39.2' UNION ALL
SELECT '甲','08:00','38.7' UNION ALL
SELECT '甲','09:00','39.3' UNION ALL
SELECT '甲','10:00','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:00','39' UNION ALL
SELECT '丙','12:00','39.6' UNION ALL
SELECT '丙','13:00','39.7' UNION ALL
SELECT '丙','14:00','38.1' UNION ALL
SELECT '丙','15:00','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 病人,MIN(时间) a,MAX(时间) b,count(1) cnt
INTO #
FROM (
SELECT *,DATEADD(hh,-(SELECT COUNT(1) FROM tb WHERE 病人=t.病人 AND 温度>=37.4 AND 时间<=t.时间),时间) gp
FROM [tb] t
WHERE EXISTS(
SELECT 1
FROM tb
WHERE 病人=t.病人
AND ABS(DATEDIFF(hh,t.时间,时间))=1
AND 温度>=37.4
)
AND 温度>=37.4
) t
GROUP BY 病人,gp
SELECT 病人,a+'~'+b 时间段 FROM # t WHERE cnt=(SELECT MAX(cnt) FROM # WHERE 病人=t.病人)
/*
病人 时间段
---------- ---------------------
甲 06:00~10:00
丙 11:00~15:00
(2 行受影响)
*/
DROP TABLE #
;
with tb_1(病人,时间,温度 ,类型) as
(
select 病人,时间,温度 ,类型=case when 温度>37.4 then 1 else 0 end from tb
)
,
tb_2( 病人,时间,问题 ,类型,分组列) as
(
select 病人,时间,温度 ,类型,分组列=(select top 1 时间 from tb_1 b where a.病人=b.病人 and a.类型<>b.类型 and b.时间 <=a.时间 order by b.时间 desc) from tb_1 a
)
select 病人 ,MIN(时间) as [开始],MAX(时间) as [结束],COUNT(*) as [联系次数] from tb_2 where 类型=1 group by 病人 ,分组列 order by COUNT(*) desc