表格
time, no, price
11:00, a, 10
12:00, a, 11
13:00, a, 9
05:00, b, 20
06:00, b, 21
12:00, b, 22
有无高效的办法,获得相同NO,最高time的PRICE,
结果
time, no, price
13:00, a, 9
12:00, b, 22
time, no, price
11:00, a, 10
12:00, a, 11
13:00, a, 9
05:00, b, 20
06:00, b, 21
12:00, b, 22
有无高效的办法,获得相同NO,最高time的PRICE,
结果
time, no, price
13:00, a, 9
12:00, b, 22
解决方案 »
- 日累计,月累计,年累计,求高手帮解呀,邹建过来看看呀,我三天没搞定了急急!!
- 登陆的存储过程总是检测不到用户名密码错误
- 做一个去掉约束和外键的存储过程,发现结果不对,object_name()是否取当前数据库的值?
- 如何对所有存储过程进行一次性的加密?
- 一直不太明白参数如何取值!
- 存储过程返回CURSOR 在线=========
- 在存储过程中,如何将字段名作为参数?
- 请教,双表去重复记录的方法
- VFP菜单等问题
- 知道一台winnt+oracle服务器后,怎样才能搞定她地sid ??
- 对象 'DF__SCZjdPro__dss__51C5D689' 依赖于 列 'dss'。 51C5D689这个是什么?
- sql2000视图转换
with tb(time,no,price) as (
select '11:00', 'a', 10 union all
select '12:00', 'a', 11 union all
select '13:00', 'a', 9 union all
select '05:00', 'b', 20 union all
select '06:00', 'b', 21 union all
select '12:00', 'b', 22)
select * from tb a where not exists (select 1 from tb where no=a.no and a.time<time)
很好啊,能翻译成 not in的方式么,方便我理解一下exists,很少用exists,不怎么理解。
谢谢。
说不太清除,不过你用多了就明白了,就当exists是存在的意思
*
from tb As a
Where time=(Select Max(time) from tb As x
Where x.no=a.no
)
select '11:00', 'a', 10 union all
select '12:00', 'a', 11 union all
select '13:00', 'a', 9 union all
select '05:00', 'b', 20 union all
select '06:00', 'b', 21 union all
select '12:00', 'b', 22
)
select * from tb a where not exists (select 1 from tb where no=a.no and a.time < time)
select * from tb a where time not in (select time from tb where no=a.no and a.time < time)
in 和 exists的区别为什么这么大?
他们的差别是什么。
求指导、谢谢
select
*
from tb a
where time not in (select time from tb where no=a.no EXCEPT select max(time) from tb where no=a.no )
select * from tb a where not exists (select 1 from tb where no=a.no and a.time < time)not in 是表示不在列表內,這面這句是說 a表 的time列的值不在 子查詢里查詢出來的time列表(子查詢已經剔除同一個no最大的時間),也就是說查詢同一個no里最大的時間。
select
*
from tb a
where time not in (select time from tb where no=a.no EXCEPT select max(time) from tb where no=a.no )
insert into MaxTime values ('11:00','a',10) --在表格中插入数据
insert into MaxTime values ('12:00','a',11)
insert into MaxTime values ('13:00','a',9)
insert into MaxTime values ('05:00','b',20)
insert into MaxTime values ('06:00','b',21)
insert into MaxTime values ('12:00','b',22)
select time ,[no],price from MaxTime as a
where not exists(select * from MaxTime as x where x.[no] = a.[no]
and x.time>a.time)
drop table MaxTime
select * from tk a where time=(select max(time) from tk where no=a.no )select * from tk a where time>=all(select time from tk where no=a.no)