病人统计

2011-06-03 05:23:40 217 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

hongyuan20022003 墨衍会员 · AI 创作全网分发关注 + 加入社区

The Number of Patient with H1N1 Virus(甲流病人初筛) 1、question:目前正是甲流盛行时期，为了更好地进行分流治疗，医院在挂号时要求对病人的体温和咳嗽情况进行检查，对于体温超过37.5度（含等于37.5度）并且咳嗽的病人初步判定为甲流病人（初筛）。现需要统计某天前来挂号就诊的病人中有多少人被初筛为甲流病人。第一行是某天前来挂号就诊的病人数n。其后有n行，每行是病人的信息，包括三个信息：姓名（字符串，不含空格，最多8个字符）、体温（float）、... 阅读详情

病人的体温每小时量一次

病人时间温度
甲 0:00 37.3
甲 1:00 37.2
甲 2:00 37.1

.. ... ...
甲 23:00 37.9

.. ... ...
乙 1:00 37.2

.. ... ...

丁 ... ...
.. ... ...

求每个病人温度连续超过37.4 的最长时段

比如某个病人一天中也许有若干时段超过 37.4 ，选出最长的。

如果某个病人没有符合条件的时段，就不显示。

比如甲的时段是 14:00-20:00

丁的时段是 1:00-2:00 等等

温度是随机变化的，没有规律。

如果只有一个小时(不连续)超过37.4 ，就不显示。

2000系统

...全文

NOI题库（1.12.T3）甲流病人初筛描述目前正是甲流盛行时期，为了更好地进行分流治疗，医院在挂号时要求对病人的体温和咳嗽情况进行检查，对于体温超过37.5度（含等于37.5度）并且咳嗽的病人初步判定为甲流病人（初筛）。现需要统计某天前来挂号就诊的病人中有多少人被初筛为甲流病人。输入第一行是某天前来挂号就诊的病人数n。（n 病人的信息，包括三个信息：姓名（字符串，不含空格，最多8个字符）、体温（阅读详情

hongyuan20022003

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

htl258_Tony 2011-06-04

打赏
举报

[Quote=引用 6 楼 hongyuan20022003 的回复:]
试了，没错。

还有更复杂的一个例子。
[/Quote]
建议升级2000系统吧，硬件配置好的话，好处多多。

hongyuan20022003 2011-06-04

打赏
举报

试了，没错。

还有更复杂的一个例子。

htl258_Tony 2011-06-04

打赏
举报

[Quote=引用 4 楼 hongyuan20022003 的回复:]
服务器: 消息 156，级别 15，状态 1，行 3
在关键字 'with' 附近有语法错误。
服务器: 消息 170，级别 15，状态 1，行 8
第 8 行: ',' 附近有语法错误。
[/Quote]
1楼试了吗，楼主要的结果是什么?

hongyuan20022003 2011-06-04

打赏
举报

服务器: 消息 156，级别 15，状态 1，行 3
在关键字 'with' 附近有语法错误。
服务器: 消息 170，级别 15，状态 1，行 8
第 8 行: ',' 附近有语法错误。

rfq 2011-06-03

打赏
举报

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'
go
select * from [tb]
;
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

htl258_Tony 2011-06-03

打赏
举报

--> 生成测试数据表: [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 #

打一壶酱油 2011-06-03

打赏
举报

;

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

病人统计

相关推荐