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

[xianrenxw]

测试SQL

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)
)
GO
INSERT 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)
GO
INSERT 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)
GO
INSERT 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)
GO
INSERT 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)
GO
INSERT 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 a.stcd,tm,upz
from ST_WAS_R a,
 (
select stcd,max(tm) mtm
 from ST_WAS_R
 group by stcd
 )b 
 where a.stcd=b.STCD and a.tm=b.mtm

[以学习为目的]

select a.stcd,
tm,upz
from ST_WAS_R a,
(
select 
stcd,
max(tm) mtm
from 
ST_WAS_R
group by stcd
)b 
 where a.stcd=b.STCD and a.tm=b.mtm