表中存储的数据
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语句的写法
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语句的写法
解决方案 »
- 全文检索问题 真是奇怪啊 急 求救啊
- 奇怪的decimal字段精度问题
- 请教~~sql 视图问题
- 如何使用一条SQL语句来确认表中是否有某字段?
- 请问各位大哥, 你们产品信息表一般怎么设计?
- 求一sql语句!
- 没分了!是因为我太菜!但请各路英雄帮帮忙,不然过明天我又要加入失业军团了,Boss给三天时间为期限,今天已是第二天了!帮帮忙吧,看在
- 英文2000server上可以用中文sqlserver吗?
- pls. introduce a set of tools for analyse database structure.thank you
- 怪异的问题!!!!大家帮忙呀 急急急
- 怎样向sql server中一次性提交多条记录?急急急急急
- 执行存贮过程返回记录集,为何存贮过程中不加:set nocount on就不会返回记录集呢?
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.55select * from @t a where not exists(select 1 from @t where hh=a.hh and rq>a.rq)
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.55select 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
xeqtr1982(vesslan) 兄的方法好像不行,我的表中有近百万条数据itblog(^ω^) 兄的方法
我试了
select * from 表名 a where not exists(select 1 from 表名 where hh=a.hh and rq>a.rq)
这条可以。
那条不行,表中还有其他许多字段,会不会影响你的sql语句结果?可能表中的数据过大执行时间长了点,有没有解决办法
http://community.csdn.net/Expert/topic/4767/4767515.xml?temp=.1402399
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.55select * from @t a where not exists(select 1 from @t where hh=a.hh and rq>a.rq)
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.55select 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
不知道慢不慢
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.55select 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时,检索的结果就会比想要的多