对一字段进行分组，且时间最大。sql如何写
日期：2014-05-18 浏览次数：20739 次
对一字段进行分组，且时间最大。sql怎么写？
测试SQL
结果需要 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
- 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)
)
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 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 upz TST00001 2012-07-17 11:00:00.000 2.000 TST00002 2012-07-17 00:00:00.000 1.000 TST00003 2012-07-18 00:00:00.000 2.000 TST00004 2012-07-15 00:00:00.000 3.000 */
------解决方案--------------------
- SQL code
SELECT * FROM ST_WAS_R T WHERE NOT EXISTS(SELECT 1 FROM ST_WAS_R WHERE stcd=T.stcd AND tm>T.tm)
------解决方案--------------------
- SQL code
select
*
from
st_was_r t
where
tm=(select max(tm) from st_was_r where stcd=t.stcd)
免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
