34,837
社区成员




表T_Result(varchar,datetime,int,int,int)
BarCode dt TP0 TP1 TestType
aaa 2013-2-3 12 13 0
aaa 2013-2-4 11 14 0
aaa 2013-3-6 15 23 1
aaa 2013-3-7 17 25 1
--------------------------------------------
表M_Result(varchar,datetime,int,int,int)
BarCode dt TP10 TP11 TestType
aaa 2013-1-3 15 17 0
aaa 2013-2-1 13 12 0
aaa 2013-1-16 21 22 1
aaa 2013-1-17 27 35 1
----------------------------------------------
想得到以下结果:
BarCode dt TP0 TP1 TP10 TP11 TestType
aaa 2013-2-4 11 14 13 12 0
aaa 2013-3-7 17 25 27 35 1
说明:按照日期排序desc,按照TestType分类,各类取时间最新的那条记录。
请教句子该如何写?谢谢啦
我可能没说清楚,你们给的代码测试了都不是我要的结果啊。
我是想把T_Result as t中的t.BarCode,t.dt,t.TP0,t.TP1,t.TestType按t.TestType分组,取各组中t.dt最大的记录;
同时还要把M_Result as m 中的m.TP10,m.TP11按照m.TestType分组,取各组中m.dt最大的记录;
然后把上面两个记录合并成在一起。不知道我这样说清楚了没,请大家再耐心帮我看看,谢谢。
with a as(select * from t_result t1 where not exists(
select 1 from t_result where testtype=t1.testtype and dt<t1.dt
)),
b as(
select * from m_result m1 where not exists(
select 1 from m_result where testtype=m1.testtype and dt<t1.dt
))
select a.barcode,a.dt,tp0,tp1,tp10,tp11,a.testtype from a join b
on a.testtype=b.testtype
select barcode,dt,tp0,tp1,tp10,tp11,testtype from(select row=row_number()over(partition by a.barcode,a.testtype order by (case when a.dt<b.dt then b.dt else a.dt end) desc),a.barcode, dt=(case when a.dt<b.dt then b.dt else a.dt end),a.tp0, a.tp1, b.tp10, b.tp11, a.testtype
from t_result a join m_result b on a.barcode=b.barcode and a.testtype=b.testtype)t where row=1
select a.BarCode, -- BarCode取哪个自己定逻辑
case when a.dt<b.dt then b.dt else a.dt end dt, a.TP0, a.TP1, b.TP10, b.TP11, a.TestType
from T_Result a, M_Result b where a.TestType=b.TestType
and not exists (select 1 from T_Result where TestType=a.TestType and dt>a.dt)
and not exists (select 1 from M_Result where TestType=b.TestType and dt>b.dt)
with cte as
(
select * from T_Result
union all
select * from M_Result
)
select * from cte as t where not exists (select 1 from cte where TestType=t.TestType and dt>t.dt)