27,580
社区成员
发帖
与我相关
我的任务
分享
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([人员ID] int,[床位号] nvarchar(25),[人员类型] nvarchar(29),[交班类型] nvarchar(22),[诊断] nvarchar(23),[内容] nvarchar(45))
Insert #T
select 228,N'2-201',N'转出(院内)老人',N'日班',N'高血压',N'老人今日7:00在家属陪同下去闵中心医院做' union all
select 228,N'2-201',N'转出(院内)老人',N'日班',N'高血压',N'B超疑诊胆结石,脂肪肝,确切报告明日出。外院已' union all
select 228,N'2-201',N'转出(院内)老人',N'日班',N'高血压',N'于9:00回该床位' union all
select 228,N'2-201',N'转出(院内)老人',N'夜班',N'高血压',N'老人精神软,全身乏力,自述右上腹稍有腹胀,无疼痛。' union all
select 228,N'2-201',N'转出(院内)老人',N'夜班',N'高血压',N'午餐进几口普菜,无恶心,呕吐,白班小' union all
select 303,N'1-109',N'请假离院或返院老人',N'日班',N'上感',N'14:00T:36.0R:78次/分' union all
select 303,N'1-109',N'请假离院或返院老人',N'日班',N'上感',N'患者两天前发热,咳嗽,咳痰经对症疗体温恢' union all
select 303,N'1-109',N'请假离院或返院老人',N'日班',N'上感',N'复正常,老人精神状态良好,胃纳可,小便畅,今' union all
select 247,N'3-102',N'新入院老人',N'夜班',N'糖尿病',N'BP:140mmHg' union all
select 247,N'3-102',N'新入院老人',N'夜班',N'糖尿病',N'老人神清,精神萎软,近几日反复发热,伴咳'
Go
--测试数据结束
;WITH ctea AS (
Select *,ROW_NUMBER()OVER(PARTITION BY 人员ID,[交班类型] ORDER BY 人员ID) AS num,ROW_NUMBER()OVER(ORDER BY (SELECT 1)) AS rn from #T WHERE 交班类型='日班'
), cteb AS (
Select *,ROW_NUMBER()OVER(PARTITION BY 人员ID,[交班类型] ORDER BY 人员ID) AS num,ROW_NUMBER()OVER(ORDER BY (SELECT 1)) AS rn from #T WHERE 交班类型='夜班'
)
SELECT CASE WHEN ctea.人员ID IS NOT NULL THEN ctea.人员ID
ELSE cteb.人员ID
END AS 人员ID ,
CASE WHEN ctea.[床位号] IS NOT NULL THEN ctea.[床位号]
ELSE cteb.[床位号]
END AS [床位号] ,
CASE WHEN ctea.[人员类型] IS NOT NULL THEN ctea.[人员类型]
ELSE cteb.[人员类型]
END AS [人员类型] ,
ctea.[内容] ,
cteb.[内容]
FROM ctea
FULL JOIN cteb ON cteb.num = ctea.num
AND cteb.人员ID = ctea.人员ID
ORDER BY ISNULL(ctea.rn, 100000) ,
ISNULL(cteb.rn, 100000);