取某字段最大值的一行记录

如题，
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列最大值的最近的一条记录)
分不多了。。

数据库设计的时候没主键，没索引。。

20 个解决方案

#1

该回复于2015-02-04 15:25:45被管理员删除

#2

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
-----------------------
 */

#3

SELECT * FROM(
	SELECT ROW_NUMBER()OVER(PARTITION BY stcd ORDER BY z DESC,c1 DESC)RN
		,*
	FROM tb
)T
WHERE RN=1

#4

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

#5

不能用  max 那种，这个取出来的数据，可能不是同一行的， 2# 3# 的方法是常用的方法。

#6

引用 2 楼 Ekun_sky 的回复:

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
-----------------------
 */

#7

引用 2 楼 Ekun_sky 的回复:

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
-----------------------
 */

恩恩，这个好 。不过我忘了说sql用的是2000 ...没有row number方法该怎么办呢，期待你的答复

#8

引用 6 楼 C_B_Lu 的回复:

Quote: 引用 2 楼 Ekun_sky 的回复:

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
-----------------------
 */

#9

该回复于2015-02-04 17:22:06被管理员删除

#10

引用 7 楼 TEM_po 的回复:

Quote: 引用 2 楼 Ekun_sky 的回复:

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
-----------------------
 */

恩恩，这个好 。不过我忘了说sql用的是2000 ...没有row number方法该怎么办呢，期待你的答复

没有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

#11

该回复于2015-02-09 15:51:29被管理员删除

#12

引用 10 楼 lovelj2012 的回复:

Quote: 引用 7 楼 TEM_po 的回复:

Quote: 引用 2 楼 Ekun_sky 的回复:

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
-----------------------
 */

恩恩，这个好 。不过我忘了说sql用的是2000 ...没有row number方法该怎么办呢，期待你的答复

没有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

很遗憾这样是不对的。。max(z),max(tm)只是把最大值选出来而已，没有他俩可能不是同一行的

#13

引用 12 楼 TEM_po 的回复:

Quote: 引用 10 楼 lovelj2012 的回复:

Quote: 引用 7 楼 TEM_po 的回复:

Quote: 引用 2 楼 Ekun_sky 的回复:

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
-----------------------
 */

恩恩，这个好 。不过我忘了说sql用的是2000 ...没有row number方法该怎么办呢，期待你的答复

没有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

很遗憾这样是不对的。。max(z),max(tm)只是把最大值选出来而已，没有他俩可能不是同一行的

他俩可能不是同一行的。。打错

#14

/* 测试数据
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

#15

SELECT *
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

如果根据 z和tm还不能过滤出stcd相同情况的其中一列的话，还需要其它唯一性的列

#16

#15: 你少了个括号，OR 后面部分变成不判断stcd相等了。

#17

谢谢14#、15#
我自己也想了个方法

 SELECT T1.STCD,T1.Z,MAX(T2.TM)
 FROM table1 AS T2,(SELECT STCD,MAX(Z) Z from table1 group by STCD) AS T1
 WHERE T1.STCD=T2.STCD AND T1.Z=T2.Z 
 GROUP BY  T1.STCD,T1.Z

如果需要这一行的其他字段的数据、外面再加个select就可以了，因为同一stcd的同一时间只有一条数据，所以stcd、tm、z已经足够作为判断唯一列的依据。
sql处理SELECT还是很快的，我这边数据比较多，如果用EXISTS就要跑5min了。。
结贴了

#18

#15确实少了个()

#楼主，我们这边没有环境。当然怎么快怎么来吧
如果你还要其它的列，你的这个查询，还得再派生出一个表

#19

To 18#
嗯，的确是。虽然不太明白sql内部是怎样执行的，但我的方法看起来好像是中生成了两张表，然后用完抛掉了吧。。
自己也学习了跟多，附结果：

#20

14# 15#的方法也是可以的，但是可能因为没主键没索引所以才慢了吧

#21

• 获取一张表中某个字段最大值的一条记录