select user,price,datime
from
(select distinct user,price,datime from table a where not exists (select 1 from table where a.user=user and a.price<price ) --一个用户只保留一条记录
) a
order by price desc,datime acs
from
(select distinct user,price,datime from table a where not exists (select 1 from table where a.user=user and a.price<price ) --一个用户只保留一条记录
) a
order by price desc,datime acs
from table1
where not exists(select * from table1 a where a.[user]=table1.[user] and a.price<table1.price) --每个用户只取价格最低的一条记录出来排名
order by price ,datime
--价格从低到高,当价格相同时按时间从早到晚排序
========================================
什么时候升的?怎么没见散分?
insert @t
select '张三',10,'2007-07-05 08:00:00' union all
select '李四',10,'2007-07-05 08:00:01' union all
select '王五',12,'2007-07-05 08:00:02' union all
select '张三',10,'2007-07-05 08:00:03' union all
select '李四',10,'2007-07-05 08:00:04' select name,price,max(dt) as dt from @t as a
where not exists(select 1 from @t where name = a.name and price > a.price)
group by name,price
order by price DESC,dt DESC/*结果
name price dt
---------- ----------- -------------------------
王五 12 2007-07-05 08:00:02.000
李四 10 2007-07-05 08:00:04.000
张三 10 2007-07-05 08:00:03.000
*/