Three table

Two storage item_id corresponding item in the table behind the id in the table, how can I write SQL to
The quantity of the quantity of the name storage1 storage2
Iron 3 1
Copper 2 4

The effect of such

CodePudding user response:

Select the name, the sum (if (s='s1', CNT, 0)) as number of storage1, sum (if (s='s2', CNT, 0)) as number of storage2 from (
Select the 's1' as s, a.n ame, sum (1) as the from CNT item a
The join storage1 b on Anderson, d=b.i id
Group by Anderson d
Union all
Select 's2 as s, a.n ame, sum (1) the as CNT from item a
The join storage2 b on Anderson, d=b.i id
Group by Anderson, d) a
Group by the name

CodePudding user response:

Select the item. The name,
Storage1=(select count (*) from storage1 where storage1. Item_id=item. The id),
Storage2=(select count (*) from storage2 where storage2. Item_id=item. Id)
From the item

CodePudding user response:

Select aa. The name, aa, aaa, bb. BBB
The from
(select a.n ame, count (b.i tem_id) aaa
The from item a, storage1 b
Where Anderson, d=b.i tem_id
Group by a.n ame) aa,
(select d.n ame, count (c.i tem_id) BBB
From the item d, storage2 c
Where d.i d=c.i tem_id
Bb group by d.n ame)
Where aa. Name=bb. Name;