22,209
社区成员
发帖
与我相关
我的任务
分享
DECLARE @t_TB TABLE ([UserID] NVARCHAR(10),[Item] NVARCHAR(10),[Result] NVARCHAR(10),[Date] DATETIME);
INSERT INTO @t_TB VALUES
('1','WBC','1','2015.12.3'),
('1','WBC','2','2015.12.4'),
('1','RBC','3','2015.12.6'),
('2','WBC','4','2013.12.2'),
('2','WBC','5','2013.11.7'),
('2','RBC','6','2013.5.6'),
('2','PLT','7','2012.3.6'),
('3','WBC','8','2014.5.6'),
('3','RBC','9','2013.3.5'),
('3','RBC','10','2012.3.6'),
('3','PLT','11','2013.3.8');
DECLARE @t_TB2 TABLE ([UserID] NVARCHAR(10),[Sex] NVARCHAR(10),[Age] INT,[sick] NVARCHAR(10));
INSERT INTO @t_TB2 VALUES
('1','M','23','Y'),
('2','F','43','N'),
('3','M','22','Y');
;WITH CTE
AS
(SELECT USERID,ITEM,RESULT
FROM @t_TB T
WHERE NOT EXISTS( SELECT 1 FROM @t_TB WHERE USERID = T.USERID AND ITEM = T.ITEM AND [DATE] <T.[DATE])
)
SELECT T2.UserID,T2.Sex,T2.Age,T2.sick,MAX(CASE WHEN T1.ITEM ='WBC' THEN T1.RESULT END) AS WBC,
MAX(CASE WHEN T1.ITEM ='RBC' THEN T1.RESULT END) AS RBC,
MAX(CASE WHEN T1.ITEM ='PLT' THEN T1.RESULT END) AS PLT FROM @t_TB2 T2
INNER JOIN CTE T1 ON T1.USERID = T2.USERID
GROUP BY T2.UserID,T2.Sex,T2.Age,T2.sick
/*
UserID Sex Age sick WBC RBC PLT
---------- ---------- ----------- ---------- ---------- ---------- ----------
1 M 23 Y 1 3 NULL
2 F 43 N 5 6 7
3 M 22 Y 8 10 11
*/