如题，
stcd z tm c1 ...
01 3 2014 a
02 2 2014 b
01 2 2014 a
03 1 2015 v
01 3 2015 d
想要的结果：
01 3 2015 d
02 2 2014 b
03 1 2015 v
(要求结果的stcd列不重复，取z列最大值的最近的一条记录)
分不多了。。
数据库设计的时候没主键，没索引。。
------解决思路----------------------
with a(stcd,z,tm,c1) as
(
select '01',3,2014,'a' union all
select '02',2,2014,'b' union all
select '01',2,2014,'a' union all
select '03',1,2015,'v' union all
select '01',3,2015,'d'
)
select b.stcd,b.z,b.tm,b.c1 from
(select *,ROW_NUMBER()over(partition by stcd order by tm desc,z desc) as tid
from a) as b where b.tid=1
/*
stcd z tm c1
----------------------
01 3 2015 d
02 2 2014 b
03 1 2015 v
-----------------------
*/
------解决思路----------------------
SELECT * FROM(
SELECT ROW_NUMBER()OVER(PARTITION BY stcd ORDER BY z DESC,c1 DESC)RN
,*
FROM tb
)T
WHERE RN=1
------解决思路----------------------
declare @table table(stcd int,z int,tm varchar(5),c1 varchar(2))
insert into @table(stcd,z,tm,c1)
select 01,3,'2014','a' union all
select 02,2,'2014','b' union all
select 01,2,'2014','a' union all
select 03,1,'2015','v' union all
select 01,3,'2015','d'
select max(stcd)stcd,max(z)z,max(tm)tm,max(c1)c1 from @table group by stcd
------解决思路----------------------
没有row_number也不要紧
with table1(stcd,z,tm,c1) as
(
select '01',3,2014,'a' union all
select '02',2,2014,'b' union all
select '01',2,2014,'a' union all
select '03',1,2015,'v' union all
select '01',3,2015,'d'
)
select t1.*
from table1 t1,(select stcd,max(z) z,max(tm) tm from table1 group by stcd) t2
where t1.stcd=t2.stcd and t1.z=t2.z and t1.tm=t2.tm
------解决思路----------------------
/* 测试数据
with a(stcd,z,tm,c1) as
(
select '01',3,2014,'a' union all
select '02',2,2014,'b' union all
select '01',2,2014,'a' union all
select '01',2,2016,'a' union all --加了一条比最大z还新的数据
select '03',1,2015,'v' union all
select '01',3,2015,'d'
)*/
SELECT *
FROM a
WHERE NOT EXISTS (SELECT *
FROM a t
WHERE t.stcd = a.stcd
AND ( t.z > a.z
OR (t.z = a.z
AND t.tm > a.tm
)
)
)
ORDER BY stcd
stcd z tm c1
---- ----------- ----------- ----
01 3 2015 d
02 2 2014 b
03 1 2015 v
------解决思路----------------------
SELECT *如果根据 z和tm还不能过滤出stcd相同情况的其中一列的话，还需要其它唯一性的列
FROM tb T1
WHERE NOT EXISTS(SELECT 1 FROM tb T2 WHERE T1.stcd=T2.stcd
AND T1.z<T2.z OR(T1.z=T2.z AND T1.tm<T2.tm))
ORDER BY stcd