where 0>(select count... where not exists(...) where checksum(hotelid,price)=(select top 1 checksum(hotelid,price) from ... where hotelid=a.hotelid order by price) select a.* from tb a inner join (select hotelid,min(price) mp from .. group by hotelid) b where a.hotelid=b.hotelid and price=mp ... 写法太多了.
declare @ttest table(id int,hotelid int,roomid int,price int) insert @ttest select 1, 1, 201 , 200 insert @ttest select 2, 1, 202 , 100 insert @ttest select 3, 1, 203 , 300 insert @ttest select 4, 2, 204 , 90 insert @ttest select 5, 2, 205 , 190 insert @ttest select 6, 2, 206 , 290 insert @ttest select 7, 3, 207 , 120 insert @ttest select 8, 3, 208 , 320 select a.* from @ttest a inner join (select hotelid,min(price) as price from @ttest group by hotelid) b on a.hotelid=b.hotelid and a.price=b.price order by b.price id hotelid roomid price ----------- ----------- ----------- ----------- 4 2 204 90 2 1 202 100 7 3 207 120
insert @ttest select 1, 1, 201 , 200
insert @ttest select 2, 1, 202 , 100
insert @ttest select 3, 1, 203 , 300
insert @ttest select 4, 2, 204 , 90
insert @ttest select 5, 2, 205 , 190
insert @ttest select 6, 2, 206 , 290
insert @ttest select 7, 3, 207 , 120
insert @ttest select 8, 3, 208 , 320
select *
from @ttest a
where not exists(select 1 from @ttest where hotelid = a.hotelid and price < a.price)
order by price/*id hotelid roomid price
----------- ----------- ----------- -----------
4 2 204 90
2 1 202 100
7 3 207 120
*/
insert @ttest select 1, 1, 201 , 200
insert @ttest select 2, 1, 202 , 100
insert @ttest select 3, 1, 203 , 300
insert @ttest select 4, 2, 204 , 90
insert @ttest select 5, 2, 205 , 190
insert @ttest select 6, 2, 206 , 290
insert @ttest select 7, 3, 207 , 120
insert @ttest select 8, 3, 208 , 320 select *from(
select * ,row_number()over(partition by hotelid order by price) as rowno
from @ttest ) a where rowno=1
where not exists(...)
where checksum(hotelid,price)=(select top 1 checksum(hotelid,price) from ... where hotelid=a.hotelid order by price)
select a.* from tb a
inner join
(select hotelid,min(price) mp from .. group by hotelid) b
where a.hotelid=b.hotelid and price=mp
...
写法太多了.
declare @ttest table(id int,hotelid int,roomid int,price int)
insert @ttest select 1, 1, 201 , 200
insert @ttest select 2, 1, 202 , 100
insert @ttest select 3, 1, 203 , 300
insert @ttest select 4, 2, 204 , 90
insert @ttest select 5, 2, 205 , 190
insert @ttest select 6, 2, 206 , 290
insert @ttest select 7, 3, 207 , 120
insert @ttest select 8, 3, 208 , 320 select a.* from @ttest a inner join (select hotelid,min(price) as price from @ttest group by hotelid) b
on a.hotelid=b.hotelid and a.price=b.price order by b.price
id hotelid roomid price
----------- ----------- ----------- -----------
4 2 204 90
2 1 202 100
7 3 207 120
declare @ttest table(id int,hotelid int,roomid int,price int)
insert @ttest select 1, 1, 201 , 200
insert @ttest select 2, 1, 202 , 100
insert @ttest select 3, 1, 203 , 300
insert @ttest select 4, 2, 204 , 90
insert @ttest select 5, 2, 205 , 190
insert @ttest select 6, 2, 206 , 290
insert @ttest select 7, 3, 207 , 120
insert @ttest select 8, 3, 208 , 320
select hotelid, min(price)as price
from @ttest
group by hotelid
order by min(price)/*hotelid price
----------- -----------
2 90
1 100
3 120(所影响的行数为 3 行)
*/
用order by min(price)