create table test (id int not null, memberprice int, sourceid int)insert test (id,memberprice,sourceid) select 1,250,2 union all select 2,270,2 union all select 5,220,2 union all select 3,250,3 union all select 4,250,4--select * from test--就是这一句了,OK不? select * from test a where 1>(select count(*) from test where sourceid=a.sourceid and memberprice<a.memberprice) drop table test
select * FROM RoomPrice a WHERE (HotelID = '1') and id=(select top 1 id from RoomPrice where sourceid=a.sourceid and (HotelID = '1') order by memberprice )
需要考虑memberprice 可能重复,所以用top
看来楼主要的就是多个id的select * FROM RoomPrice a WHERE (HotelID = '1') and memberprice=(select min(memberprice) from RoomPrice where sourceid=a.sourceid and (HotelID = '1') )
Yang_(扬帆破浪)兄,该方法是我所有的结果。。非常感谢 select * FROM RoomPrice a WHERE (HotelID = '1') and memberprice=(select min(memberprice) from RoomPrice where sourceid=a.sourceid and (HotelID = '1') )
1 250 2
2 270 2
5 220 2
3 250 3
4 250 4
上述记录我想到到第三里不重复,且memberprice在每组中最小的值。。加入ID分组后就得不到了
(id int not null,
memberprice int,
sourceid int)insert test (id,memberprice,sourceid)
select 1,250,2
union all select 2,270,2
union all select 5,220,2
union all select 3,250,3
union all select 4,250,4--select * from test--就是这一句了,OK不?
select * from test a
where 1>(select count(*) from test where sourceid=a.sourceid and memberprice<a.memberprice)
drop table test
FROM RoomPrice a
WHERE (HotelID = '1')
and id=(select top 1 id from RoomPrice
where sourceid=a.sourceid
and (HotelID = '1')
order by memberprice
)
FROM RoomPrice a
WHERE (HotelID = '1')
and memberprice=(select min(memberprice) from RoomPrice
where sourceid=a.sourceid
and (HotelID = '1')
)
select *
FROM RoomPrice a
WHERE (HotelID = '1')
and memberprice=(select min(memberprice) from RoomPrice
where sourceid=a.sourceid
and (HotelID = '1')
)