select a.* from xgjl a, (select max(RQ) as RQ,DM from xgjl group by DM) b where a.RQ=b.RQ and a.DM=b.DM
SELECT * FROM XGJL A INNER JOIN ( SELECT MAX(RQ) AS RQ FROM XGJL GROUP BY DM HAVING COUNT(*)>0 )B ON A.RQ=B.RQ
select * from tb_temp1 a where rq=(select max(rq) from tb_temp1 where a.dm=dm) order by dm测试过的结果: 2004-01-02 A 16 17 2004-10-01 B 15 13 2004-06-01 C 16 15
select a.* from xgjl a where a.rq = (select max(rq) from xgjl where DM = a.DM) order by a.rq
select * from xgjl a where rq=(select max(rq) from xgjl where dm=a.dm) order by dm
--生成测试数据 create table t1(RQ smalldatetime,DM varchar(1),YJG int,XJG int ) insert into t1 select '2004-01-01', 'A', 15, 16 union all select '2004-05-01', 'B', 14, 15 union all select '2004-01-02', 'A', 16, 17 union all select '2004-06-01', 'C', 16, 15 union all select '2004-10-01', 'B', 15, 13 go --测试 select * from t1 where RQ in(select max(RQ) from t1 group by DM) group by DM,RQ,YJG,XJG--删除测试数据 drop table t1--测试结果 2004-01-02 00:00:00,A,16,17 2004-10-01 00:00:00,B,15,13 2004-06-01 00:00:00,C,16,15
--生成测试数据 create table xgjl(RQ smalldatetime,DM varchar(1),YJG int,XJG int ) insert into xgjl select '2004-01-01', 'A', 15, 16 union all select '2004-05-01', 'B', 14, 15 union all select '2004-01-02', 'A', 16, 17 union all select '2004-06-01', 'C', 16, 15 union all select '2004-10-01', 'B', 15, 13 go --测试 select * from xgjl where RQ in(select max(RQ) from t1 group by DM) group by DM,RQ,YJG,XJG--删除测试数据 drop table xgjl
(select max(RQ) as RQ,DM from xgjl group by DM) b
where a.RQ=b.RQ and a.DM=b.DM
FROM XGJL A
INNER JOIN (
SELECT MAX(RQ) AS RQ
FROM XGJL
GROUP BY DM
HAVING COUNT(*)>0 )B
ON A.RQ=B.RQ
where rq=(select max(rq) from tb_temp1 where a.dm=dm) order by dm测试过的结果:
2004-01-02 A 16 17
2004-10-01 B 15 13
2004-06-01 C 16 15
a.*
from
xgjl a
where
a.rq = (select max(rq) from xgjl where DM = a.DM)
order by
a.rq
from xgjl a
where rq=(select max(rq) from xgjl where dm=a.dm)
order by dm
create table t1(RQ smalldatetime,DM varchar(1),YJG int,XJG int )
insert into t1
select '2004-01-01', 'A', 15, 16 union all
select '2004-05-01', 'B', 14, 15 union all
select '2004-01-02', 'A', 16, 17 union all
select '2004-06-01', 'C', 16, 15 union all
select '2004-10-01', 'B', 15, 13
go
--测试
select *
from t1
where RQ in(select max(RQ) from t1 group by DM)
group by DM,RQ,YJG,XJG--删除测试数据
drop table t1--测试结果
2004-01-02 00:00:00,A,16,17
2004-10-01 00:00:00,B,15,13
2004-06-01 00:00:00,C,16,15
create table xgjl(RQ smalldatetime,DM varchar(1),YJG int,XJG int )
insert into xgjl
select '2004-01-01', 'A', 15, 16 union all
select '2004-05-01', 'B', 14, 15 union all
select '2004-01-02', 'A', 16, 17 union all
select '2004-06-01', 'C', 16, 15 union all
select '2004-10-01', 'B', 15, 13
go
--测试
select *
from xgjl where RQ in(select max(RQ) from t1 group by DM)
group by DM,RQ,YJG,XJG--删除测试数据
drop table xgjl