日期:2014-05-17 浏览次数:20847 次
--建表语句
create table tb(ID int, NO varchar(20), Price numeric(20,2))
insert into tb
select 239 ,'TRA13120001', 490.00 union all
select 240 ,'TRA13120001', 100.00 union all
select 241 ,'TRA13120001', 490.00 union all
select 242 ,'TRA13120001', 480.00 union all
select 243 ,'TRA13120009', 490.00 union all
select 244 ,'TRA13120009', 490.00 union all
select 245 ,'TRA13120009', 430.00 union all
select 246 ,'TRA13120009', 490.00 union all
select 247 ,'TRA13120002', 450.00 union all
select 248 ,'TRA13120002', 490.00 union all
select 249 ,'TRA13120002', 490.00
go
--查询
select ID
from
(
select *,
ROW_NUMBER() over(partition by NO order by Price) as rownum
from tb
)t
where rownum = 1
order by ID
/*
ID
240
245
247
*/
--查询
select ID
from
(
select *,
(select COUNT(*) from tb t2
where t1.NO = t2.NO and t1.Price >= t2.Price and t1.ID <> t2.ID) as rownum
from tb t1
)t
where rownum = 0
order by ID
/*
ID
240
245
247
*/
----------------------------------------------------------------
--&nbs