求一sql查询语句

[xyzliuin]

表中存储的数据
rq hh ye
2006-01-02 1111 2.01
2006-01-05 1111 3.51
2006-01-10 1111 2.55
2006-01-02 2222 3.00
2006-01-04 2222 2.00
2006-01-05 3333 6.54
2006-01-06 3333 5.23
2006-01-07 3333 8.55

用sql语句查询出每一个hh日期最大的纪录，结果如下：
2006-01-10 1111 2.55
2006-01-04 2222 2.00
2006-01-07 3333 8.55
请教sql语句的写法

[itblog]

select * from 表名 a where not exists(select 1 from 表名 where hh=a.hh and rq>a.rq)

[itblog]

把上面所谓的"表名"换成你的实际表名就可以了~

[xeqtr1982]

declare @t table(rq varchar(10),hh int,ye dec(6,2))
insert into @t select '2006-01-02' ,1111 ,2.01
union all select '2006-01-05' ,1111 ,3.51
union all select '2006-01-10' ,1111 ,2.55
union all select '2006-01-02' ,2222 ,3.00
union all select '2006-01-04' ,2222 ,2.00
union all select '2006-01-05' ,3333 ,6.54
union all select '2006-01-06' ,3333 ,5.23
union all select '2006-01-07' ,3333 ,8.55

select * from @t a where not exists(select 1 from @t where hh=a.hh and rq>a.rq)

[itblog]

:)

还有一种方法:

select * from 表名 where rq in(select max(rq) from 表名 group by hh)

[xeqtr1982]

declare @t table(rq varchar(10),hh int,ye dec(6,2))
insert into @t select '2006-01-02' ,1111 ,2.01
union all select '2006-01-05' ,1111 ,3.51
union all select '2006-01-10' ,1111 ,2.55
union all select '2006-01-02' ,2222 ,3.00
union all select '2006-01-04' ,2222 ,2.00
union all select '2006-01-05' ,3333 ,6.54
union all select '2006-01-06' ,3333 ,5.23
union all select '2006-01-07' ,3333 ,8.55

select a.* from @t a,(select max(rq) as rq,hh from @t group by hh) b where a.hh=b.hh and a.rq=b.rq

[itblog]

学习一下~

[xeqtr1982]

好像就咱俩在抢　：）

[liangpei2008]

大家真快，我来给你们抢：）
（尽管抢不过你们）

[xyzliuin]

谢谢，两位
xeqtr1982(vesslan) 兄的方法好像不行，我的表中有近百万条数据

itblog(^ω^) 兄的方法
我试了
select * from 表名 a where not exists(select 1 from 表名 where hh=a.hh and rq>a.rq)
这条可以。
那条不行，表中还有其他许多字段，会不会影响你的sql语句结果？

可能表中的数据过大执行时间长了点，有没有解决办法

[Ly105]

楼上的星星 帮各忙阿
http://community.csdn.net/Expert/topic/4767/4767515.xml?temp=.1402399

[xeqtr1982]

有很多字段的就用这个
declare @t table(rq varchar(10),hh int,ye dec(6,2))
insert into @t select '2006-01-02' ,1111 ,2.01
union all select '2006-01-05' ,1111 ,3.51
union all select '2006-01-10' ,1111 ,2.55
union all select '2006-01-02' ,2222 ,3.00
union all select '2006-01-04' ,2222 ,2.00
union all select '2006-01-05' ,3333 ,6.54
union all select '2006-01-06' ,3333 ,5.23
union all select '2006-01-07' ,3333 ,8.55

select * from @t a where not exists(select 1 from @t where hh=a.hh and rq>a.rq)

[xyzliuin]

呵呵。。。sqlserver区真不错，有这么多热心人帮忙

[itblog]

:)

[xyzliuin]

up

能帮忙解决下执行效率的问题吗

[Ly105]

declare @tbl table(rq varchar(10),hh int,ye dec(6,2))
insert into @tbl select '2006-01-02' ,1111 ,2.01
union all select '2006-01-05' ,1111 ,3.51
union all select '2006-01-10' ,1111 ,2.55
union all select '2006-01-02' ,2222 ,3.00
union all select '2006-01-04' ,2222 ,2.00
union all select '2006-01-05' ,3333 ,6.54
union all select '2006-01-06' ,3333 ,5.23
union all select '2006-01-07' ,3333 ,8.55

select B.* from @tbl B ,
(select A.hh, max(A.rq) as rq from @tbl A group by A.hh) A
where A.hh=B.hh and A.rq=B.rq


不知道慢不慢

[hotforever]

select * from 表名 where (rq, hh) in(select max(rq), hh from 表名 temp group by temp.hh)

[BEN1978]

xeqtr1982(HaN) ( ) 信誉：100 2006-5-21 13:44:15 得分: 0



declare @t table(rq varchar(10),hh int,ye dec(6,2))
insert into @t select '2006-01-02' ,1111 ,2.01
union all select '2006-01-05' ,1111 ,3.51
union all select '2006-01-10' ,1111 ,2.55
union all select '2006-01-02' ,2222 ,3.00
union all select '2006-01-04' ,2222 ,2.00
union all select '2006-01-05' ,3333 ,6.54
union all select '2006-01-06' ,3333 ,5.23
union all select '2006-01-07' ,3333 ,8.55

select a.* from @t a,(select max(rq) as rq,hh from @t group by hh) b where a.hh=b.hh and a.rq=b.rq

---------------------
当相同的rq有相同hh不同ye时，检索的结果就会比想要的多

[youyu111]

本身数据混乱，老是想着通过sql 技巧来实现数据查询，为什么不直接从源头控制好产生的数据，比如，日期，一般情况下都需要精确到秒，对数据结构设计的缺陷会对系统造成的影响很难讲的

[ymyang]

select * from 表名 a where rq=(select top 1 rg from 表名 where hh=a.hh oreder by rg desc)