集锦排序
日期：2014-05-17 浏览次数：20609 次
汇总排序
需要的结果是
------解决方案--------------------
------解决方案--------------------
这样看起来比较直观
------解决方案--------------------
create table test1 (id int,fid varchar(10),fdate datetime, fremark varchar(100))
insert into test1 values(1,'A1','2013-08-01','')
insert into test1 values(2,'A1','2013-08-02','')
insert into test1 values(3,'A1','2013-08-03','')
insert into test1 values(4,'A1','2013-08-02','')
insert into test1 values(5,'B1','2013-08-01','')
insert into test1 values(6,'B1','2013-08-02','')
insert into test1 values(7,'B1','2013-08-03','')
insert into test1 values(8,'B1','2013-08-02','')
需要的结果是
7 B1 2013-08-03 00:00:00.000
3 A1 2013-08-03 00:00:00.000
------解决方案--------------------
select * from test1 t
where fdate=(select max(fdate) from test where t.fid=fid)
------解决方案--------------------
这样看起来比较直观
;WITH maco AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY fid ORDER BY fdate DESC,id desc) AS rid,* FROM test1
)
SELECT id,fid,fdate FROM maco WHERE rid=1
/*
id fid fdate
----------- ---------- -----------------------
3 A1 2013-08-03 00:00:00.000
7 B1 2013-08-03 00:00:00.000
*/
------解决方案--------------------
SELECT *
FROM test1 AS A
WHERE fdate=(
SELECT TOP 1 MAX(fdate)
FROM test1 AS B
WHERE A.fid=B.fid
GROUP BY B.fid
免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
