select id,min(p1) from price a,price_type b where a.room=b.room and b.p3=0 group by id union all select id,min(p2) from price a,price_type b where a.room=b.room and b.p3=1 group by id
select a.id, min(case b.p3 when 1 then p1 else p2 end) from price a, price_type b where a.room = b.room group by a.id
--假定price_type中不会出现room相同而p3不同的记录 --假定p3为1则,取p1的最小值。否则取p2的最小值 --假定结果集中id为每组id的最小值 create table price(id int,p1 int,p2 int,room varchar(10)) create table price_type(room varchar(10), p3 int) go insert price select 1, 50, 60, 'a' union all select 2, 51, 61, 'a' union all select 2, 55x, 66, 'b' union all select 2, 58, 68, 'b' insert price_type select 'a', 1 union all select 'b', 0 --查询 select id=min(id), p_ret=(case max(p3) when 0 then min(p2) else min(p1) end) from price as t1 join price_type as t2 on t1.room=t2.room group by t1.room --清除 drop table price drop table price_type
select a.id, min(case b.p3 when 1 then a.p1 else a.p2 end) from price a, price_type b where a.room = b.room group by a.id
union all
select id,min(p2) from price a,price_type b where a.room=b.room and b.p3=1 group by id
a.id,
min(case b.p3 when 1 then p1 else p2 end)
from
price a,
price_type b
where
a.room = b.room
group by
a.id
--假定p3为1则,取p1的最小值。否则取p2的最小值
--假定结果集中id为每组id的最小值
create table price(id int,p1 int,p2 int,room varchar(10))
create table price_type(room varchar(10), p3 int)
go
insert price select 1, 50, 60, 'a'
union all select 2, 51, 61, 'a'
union all select 2, 55x, 66, 'b'
union all select 2, 58, 68, 'b'
insert price_type select 'a', 1 union all select 'b', 0
--查询
select id=min(id), p_ret=(case max(p3) when 0 then min(p2) else min(p1) end)
from price as t1 join price_type as t2
on t1.room=t2.room
group by t1.room
--清除
drop table price
drop table price_type
a.id,
min(case b.p3 when 1 then a.p1 else a.p2 end)
from
price a,
price_type b
where
a.room = b.room
group by
a.id