复杂的SQL连接查询的一些条件得到列的一个

Question

我有2个表

表1：

Id | product | price 
---+---------+------ 
1 | A | 5 
1 | B | 3 
1 | C | 6 

表2：

Id | prod | subprod 
---+------+-------- 
1 | A | xxxx 
1 | A | yyyy 
1 | A | zzzz 

我的结果表应具有的所有3行从表2连同一个称为价格的新列（将从表1计算的值）

结果表应该像

Id|prod|subprod|price 
--+----+-------+----- 
1 | A | xxxx |(if subprod = xxxx in table 2 then this should have price of A from table 1) 
1 | A | yyyy |(if subprod = yyyy in table 2, then if price of B is greater than price of C then the value should be price of B else 0) 
1 | A | zzzz |(if subprod = zzzz in table 2, then if price of B is less than price of C then the value should be price of C-B else 0) 

---

Answer 1

试试这个：

select distinct tab_2.*,case when tab_2.subprod ='xxxx' then (select tab_1.price from tab_1 where product='A') 
when tab_2.subprod ='yyyy' then CASE WHEN (select price from tab_1 where product='B') > (select price from tab_1 where product='C') THEN (select price from tab_1 where product='B') else 0 end 
when tab_2.subprod ='zzzz' then CASE WHEN (select price from tab_1 where product='B') < (select price from tab_1 where product='C') THEN (select price from tab_1 where product='C') - (select price from tab_1 where product='B') else 0 end 
END AS price 
from tab_1,tab_2 
where tab_1.Id =tab_2.id 

输出： -

id prod subprod  price 
1 A  xxxx  5 
1 A  yyyy  0 
1 A  zzzz  3 

编辑：

select distinct tab_2.*,case when tab_2.subprod ='xxxx' then (select tab_1.price from tab_1 where product='A') 
when tab_2.subprod ='yyyy' then CASE WHEN b.price > c.price THEN b.price else 0 end 
when tab_2.subprod ='zzzz' then CASE WHEN b.price < c.price THEN c.price - b.price else 0 end 
END AS price 
from tab_1,tab_2,(select id,price from tab_1 where product='B') b,(select id,price from tab_1 where product='C')c 
where tab_1.Id =tab_2.id 
and b.id =tab_2.id 
and c.Id =tab_2.id