求一个语句：实现下述功能。。感激不尽
编号 时间 结果一 结果二 结果三 结果四
1 2015-1-1 1 2
1 2015-1-3 3 4
1 2015-1-4 5 6
2 2015-1-1 7 8
2 2015-1-2 9 10
3 2015-1-3 11 12
备注：结果一和结果二 ，结果三和结果四 不会同时出现在同一条记录中
如果同一项结果有多条记录 则根据时间 取得最新
需要获得：
编号 结果一 结果二 结果三 结果四
1 3 4 5 6
2 9 10
3 11 12
------解决思路----------------------
DECLARE @t_TB TABLE ([no] INT,[opdate] DATETIME,[result1] int,[result2] int,[result3] int,[result4] int);
INSERT INTO @t_TB VALUES
('1','2015-1-1','0','0','1','2'),
('1','2015-1-3','3','4','0','0'),
('1','2015-1-4','0','0','5','6'),
('2','2015-1-1','7','8','0','0'),
('2','2015-1-2','9','10','0','0'),
('3','2015-1-3','0','0','11','12');
SELECT distinct [no],
max([result1]) over (partition by ([no]) order by [result1] desc,[opdate] desc) result1,
max([result2]) over (partition by ([no]) order by [result2] desc,[opdate] desc) result2,
max([result3]) over (partition by ([no]) order by [result3] desc,[opdate] desc) result3,
max([result4]) over (partition by ([no]) order by [result4] desc,[opdate] desc) result4
FROM @t_TB
/*
no result1 result2 result3 result4
----------- ----------- ----------- ----------- -----------
1 3 4 5 6
2 9 10 0 0
3 0 0 11 12
*/
------解决思路----------------------
1楼的不对，没有取最晚一天的数值。
with T (nr ,opdate,result1 ,result2 ,result3 ,result4 )as
(select '1','2015-1-1','0','0','1','2' union all
select '1','2015-1-3','3','4','0','0'union all
select '1','2015-1-4','0','0','5','6'union all
select '2','2015-1-1','7','8','0','0'union all
select '2','2015-1-2','9','10','0','0'union all
select '3','2015-1-3','0','0','11','12')
select distinct
nr
, isnull((select top 1 result1 from t where t1.nr = t.nr and t.result1 <> 0 order by t.opdate desc ), 0) as result1
, isnull((select top 1 result2 from t where t1.nr = t.nr and t.result2 <> 0 order by t.opdate desc ), 0) as result2
, isnull((select top 1 result3 from t where t1.nr = t.nr and t.result3 <> 0 order by t.opdate desc ), 0) as result3
, isnull((select top 1 result4 from t where t1.nr = t.nr and t.result4 <> 0 order by t.opdate desc ), 0) as result4
from t t1
------解决思路----------------------
-- 借 1# 的数据
-- 如果，你在这张表加上一列，标识一下，该行是结果是 12，还是34 有数据，就更省事儿了。
DECLARE @t_TB TABLE ([no] INT,[opdate] DATETIME,[result1] int,[result2] int,[result3] int,[result4] int);
INSERT INTO @t_TB VALUES
('1','2015-1-1','0','0','1','2'),
('1','2015-1-3','3','4','0','0'),
('1','2015-1-4','0','0','5','6'),
('2','2015-1-1','7','8','0','0'),
('2','2015-1-2','9','10','0','0'),
('3','2015-1-3','0','0','11','12');
with mt as (
select * ,case when isnull(result1,0) = 0 then 1 else 2 end type from @t_TB
),
mt2 as (
select no,opdate,result1,result2,result3,result4 ,type ,
ROW_NUMBER() over(partition by no , type order by opdate desc ) rn
from mt
)
select no , MAX(result1) r1,MAX(result2) r2 ,MAX(result3) r3 ,MAX(result4)r4
from mt2 where rn =1
group by no
go
(6 行受影响)
no r1 r2 r3 r4
----------- ----------- ----------- ----------- -----------
1 3 4 5 6
2 9 10 0 0
3 0 0 11 12
(3 行受影响)
------解决思路----------------------
;with cte as (
select *
,rn1=ROW_NUMBER() over(partition by id order by case when r1=0 then '1900-01-01' else ti end desc)
,rn2=ROW_NUMBER() over(partition by id order by case when r2=0 then '1900-01-01' else ti end desc)
,rn3=ROW_NUMBER() over(partition by id order by case when r3=0 then '1900-01-01' else ti end desc)
,rn4=ROW_NUMBER() over(partition by id order by case when r4=0 then '1900-01-01' else ti end desc)
from t
)
select id
,MAX(case when rn1=1 then r1 else 0 end) as r1
,MAX(case when rn2=1 then r2 else 0 end) as r2
,MAX(case when rn3=1 then r3 else 0 end) as r3
,MAX(case when rn4=1 then r4 else 0 end) as r4
from cte group by id
/*
--楼主要注意，你的题目时间大小可以设置两个结果大小不一的，这样好看是否正确
id r1 r2 r3 r4
----------- ----------- ----------- ----------- -----------
1 3 4 5 6
2 9 10 0 0
3 0 0 11 12
*/