有三張表
后面兩張storage表中item_id對應item表中的id，請問怎么寫sql可以達到
name storage1中的數量 storage2中的數量
鐵 3 1
銅 2 4
這樣的效果
uj5u.com熱心網友回復：
select name,sum(if(s='s1',cnt,0)) as storage1中的數量,sum(if(s='s2',cnt,0)) as storage2中的數量 from (select 's1' as s, a.name, sum(1) as cnt from item a
join storage1 b on a.id=b.iid
group by a.id
union all
select 's2' as s, a.name, sum(1) as cnt from item a
join storage2 b on a.id=b.iid
group by a.id) a
group by name
uj5u.com熱心網友回復：
select item.name,storage1=(select count(*) from storage1 where storage1.item_id=item.id),
storage2=(select count(*) from storage2 where storage2.item_id=item.id)
from item
uj5u.com熱心網友回復：
select aa.name,aa.aaa,bb.bbbfrom
(select a.name,count(b.item_id) aaa
from item a,storage1 b
where a.id=b.item_id
group by a.name) aa,
(select d.name,count(c.item_id) bbb
from item d,storage2 c
where d.id=c.item_id
group by d.name) bb
where aa.name=bb.name;
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/33224.html
標籤：MySQL