问世间，谁是SQL高手？(已解决，结贴)

答案（供参考）：
SELECT *
  FROM [dbo].[test]
where [SID]+'#' +str([FY])+'#' +[appType]+'#' +str([Series_No])+'#' +str([Version])
in (select max([SID]+'#' +str([FY])+'#' +[AppType]+'#' +str([Series_No])+'#' +str([Version]))
FROM [dbo].[test] GROUp BY [FStaff_ID],[FApp_FY],[FApp_Type])收起

    这个正确。收起

select a.sid, a.fy, a.appType, max(b.series_no),max(b.verion),b.appvalue
from

(select *
from test t
where verion=(select max(verion) from test where sid=t.sid)
) a
inner join
(
select *
from test t
where series_no=(select max(series_no) from test where sid=t.sid)
) b
on a.sid=b.sid
group by a.sid,a.fy,a.appType,b.appValue


这效率坑爹  {:2_32:}收起

select SID,FY,appType,Series_No,max(Version),appValue
from (
select  SID,FY,appType,max(Series_No),Version,appValue from test_table
group by SID,FY,appType,Version,appValue
) B  
group by SID,FY,appType,Series_No,appValue收起

select SID,FY,appType,Series_No,Version,appValue,rm from (
select SID,FY,appType,Series_No,Version,appValue,
row_number() over(partition by SID,FY,appType order by Series_No desc,Version desc) rm
from test_table)
where rm=1收起

回复 1# junix2008


    用union
我在oracle下执行的语句，sql应该可以，但是数据量大性能可能不是特别好
select a,b,c from t
where c=(select max(c) from t)
union
select a,b,d from t
where d=(select max(d) from t)收起

执行下面的脚本就明白了：

CREATE TABLE test_table
SID varchar(50) NOT NULL,
FY int NOT NULL,
appType char(1) NOT NULL,
Series_No int NOT NULL,
Version int NOT NULL,
appValue decimal(18, 3) NULL
) ON [PRIMARY]
insert into test_table(SID,FY,appType,Series_No,Version,appValue)
values('15800', 2012, 'A', 1, 1, 72);
insert into test_table(SID,FY,appType,Series_No,Version,appValue)
values('15800', 2012, 'A', 2, 1, 74);
insert into test_table(SID,FY,appType,Series_No,Version,appValue)
values('15800', 2012, 'A', 2, 2, 83);
insert into test_table(SID,FY,appType,Series_No,Version,appValue)
values('15800', 2012, 'A', 3, 1, 65);
insert into test_table(SID,FY,appType,Series_No,Version,appValue)
values('15476', 2012, 'A', 1, 1, 46);
insert into test_table(SID,FY,appType,Series_No,Version,appValue)
values('15476', 2012, 'A', 2, 1, 76);
insert into test_table(SID,FY,appType,Series_No,Version,appValue)
values('15476', 2012, 'A', 2, 2, 68);
insert into test_table(SID,FY,appType,Series_No,Version,appValue)
values('15476', 2012, 'A', 3, 1, 83);

/*我要的结果
'15800', 2012, 'A', 3, 1, 65
'15476', 2012, 'A', 3, 1, 83
*/收起

我没看明白题意...收起

我是要找出同SID下的：（1）如果相同series no，就取最大Version那一行
（2）在最大series no的行中取version最大的记录收起