对一字段进行分组，且时间最大。sql如何写

对一字段进行分组，且时间最大。sql怎么写？
测试SQL

SQL code

CREATE TABLE test.dbo.ST_WAS_R (    ID varchar(50) NOT NULL,    STCD char(8) NOT NULL,    TM datetime NOT NULL,    UPZ numeric(7,3) NOT NULL,    DWZ numeric(7,3) NOT NULL,    TGTQ numeric(9,3) NOT NULL,    SWCHRCD char(1),    SUPWPTN char(1),    SDWWPTN char(1),    MSQMT char(1),    PRIMARY KEY (ID))GOINSERT INTO test.dbo.ST_WAS_R(ID, STCD, TM, UPZ, DWZ, TGTQ, SWCHRCD, SUPWPTN, SDWWPTN, MSQMT) VALUES ('1', 'TST00001', '2012-07-17 11:00:00.0', 2.000, 1.000, 1.000, null, null, null, null)GOINSERT INTO test.dbo.ST_WAS_R(ID, STCD, TM, UPZ, DWZ, TGTQ, SWCHRCD, SUPWPTN, SDWWPTN, MSQMT) VALUES ('2', 'TST00002', '2012-07-17 00:00:00.0', 1.000, 1.000, 1.000, null, null, null, null)GOINSERT INTO test.dbo.ST_WAS_R(ID, STCD, TM, UPZ, DWZ, TGTQ, SWCHRCD, SUPWPTN, SDWWPTN, MSQMT) VALUES ('3', 'TST00001', '2012-07-17 00:00:00.0', 1.000, 1.000, 1.000, null, null, null, null)GOINSERT INTO test.dbo.ST_WAS_R(ID, STCD, TM, UPZ, DWZ, TGTQ, SWCHRCD, SUPWPTN, SDWWPTN, MSQMT) VALUES ('4', 'TST00003', '2012-07-18 00:00:00.0', 2.000, 2.000, 2.000, null, null, null, null)GOINSERT INTO test.dbo.ST_WAS_R(ID, STCD, TM, UPZ, DWZ, TGTQ, SWCHRCD, SUPWPTN, SDWWPTN, MSQMT) VALUES ('5', 'TST00004', '2012-07-15 00:00:00.0', 3.000, 3.000, 3.000, null, null, null, null)GO

结果需要 stcd,tm,upz

stcd进行分组，是唯一的，tm是时间最大的。非常 感谢 。、

[解决办法]
select stcd,tm,upz
from ST_WAS_R
where id in (select id from ST_WAS_R a,(select stcd,max(tm) tm from ST_WAS_R group by stcd) b where a.stcd=b.stcd and a.tm=b.tm
)
[解决办法]

SQL code

select a.stcd,a.tm,a.upz from ST_WAS_R a where exists (select 1 from (select MAX(tm)tm,stcd from ST_WAS_R group by stcd )aa where tm=a.tm and stcd=a.stcd)
[解决办法]
SQL code
select a.stcd,a.tm,a.upz from ST_WAS_R a where exists (select 1 from (select MAX(tm)tm,stcd from ST_WAS_R group by stcd )aa where tm=a.tm and stcd=a.stcd)/*stcd    tm    upzTST00001    2012-07-17 11:00:00.000    2.000TST00002    2012-07-17 00:00:00.000    1.000TST00003    2012-07-18 00:00:00.000    2.000TST00004    2012-07-15 00:00:00.000    3.000*/
[解决办法]
SQL code
SELECT * FROM ST_WAS_R TWHERE NOT EXISTS(SELECT 1 FROM ST_WAS_R WHERE stcd=T.stcd AND tm>T.tm)
[解决办法]
SQL code
select * from st_was_r twhere tm=(select max(tm) from st_was_r where stcd=t.stcd) 

 

查看更多 下一篇