SQLで一つのテーブルでカウントした数を他のテーブルに引き算する方法

Question

商品の在庫管理をするシステムで一応問題なく動いてはいるのですが効率が悪いので改善を行いたく今回はご質問させていただきました。

商品リスト
|商品リストid|商品コード|商品名|在庫|サイズ|
|:--|:--:|--:|
|1| fruits01 |りんご|５|中|
|2| fruits01 |りんご|５|大|
|3| fruits02 |みかん|５|中|
|4| fruits03 |ぶどう|５|中|

仮押さえリスト
|id|商品リストid|
|:--|:--:|
|1|2|
|2|3|
|3|3|
|4|1|
|5|3|
|6|3|
|7|3|
|8|1|
|9|1|
|10|1|
|11|1|

上記のようなテーブルが二つ用意されています。
商品リストが商品の一覧となり仮押さえリストが商品リストIDに紐付いて一旦在庫を確保しています。
なので一旦確保され在庫が０になった場合は売り切れと表示を変更するようになっています。
ただサイズ違いは両方がなくならない限り在庫ありになります。

なので上記の結果としては
りんご在庫あり
みかん在庫なし
ぶどう在庫あり
となります。

希望の返り値は商品名、在庫で
|商品名|在庫|
|:--|:--:|
|りんご|4|
|みかん|0|
|ぶどう|5|

となるのが理想です。

ですが現在はかなり無理矢理になっている気がしてもっと効率のいい方法があると思い今回質問させていただきました。
現在の状態は

```sql
SELECT
	商品コード,
	max(商品名) AS 商品名,
	SUM(ifnull(在庫,0)) AS 在庫数
FROM
	商品リスト
GROUP BY
	商品コード
```

|商品コード|商品名|在庫|
|:--|:--:|--:|
| fruits01 |りんご|10|
| fruits02 |みかん|５|
| fruits03 |ぶどう|５|

まず商品コードごとの在庫数を取得しています。
そのあとに仮押さえリストからCOUNTで取得します。
その際商品リストにJOINして商品コードを取得しています。

```sql
SELECT
	T2.商品コード,
	COUNT(*) AS cnt
FROM
	仮押さえリスト T1
LEFT JOIN
	商品リスト AS T2
ON
	T1.商品リストid = T2.商品リストid
GROUP BY
	T2.商品コード
HAVING
	(COUNT(*) > 0)
```
|商品コード|在庫|
|:--|:--:|
| fruits01 |6|
| fruits02 |5|
| fruits03 |0|

この二つのSQLをなげてそのあとに
商品コードをが紐付いているので単純にphpで引き算をして画面に表示させています。

もしSQLのみでできる方法があればご教授願いたいのですがよろしくお願いいたします。

Accepted Answer

こちらで解決いたしました。

他にも意見がありましたら本日まで、ぜひお待ちしております。


```sql
SELECT
  s.商品名
  ,sum(ifnull(s.在庫,0) - ifnull(k.仮押さえ数,0))
FROM
  商品リスト s LEFT JOIN (
    SELECT
      商品リストid
      ,count(商品リストid) AS 仮押さえ数
    FROM
      仮押さえリスト
    GROUP BY
      商品リストid
    ) k ON s.商品リストid = k.商品リストid
GROUP BY
  s.商品名
```

Answer

一筆で書くならこんな感じ。
```SQL
select v1.商品コード, v1.商品名, 商品在庫 - coalesce(仮押さえ件数, 0)
from (
        select 商品コード, 商品名, sum(在庫) as 商品在庫
        from 商品リスト 
        group by 商品コード, 商品名
     ) v1 left join (
        select 商品コード, count(*) as 仮押さえ件数
        from 仮押さえリスト t1 inner join 商品リスト t2
             on t1.商品リストid=t2.商品リストid
        group by 商品コード
     ) v2 
     on v1.商品コード=v2.商品コード
```
サブクエリーも似たような感じですけど、上記v2に対しての相関になるので、こちらの方がよさげな気がします。

Answer

```SQL
SELECT
  商品名,
  Sum(在庫) - (SELECT COUNT(商品リストid) FROM 仮押さえリスト Where 商品リストid= Goods.商品リストid) AS 在庫
 FROM 商品リスト GROUP BY 商品リストid, 商品名;
```

-- 修正
```SQL
CREATE TABLE 商品リスト
(`商品リストid` int,`商品コード` nchar(10),`商品名` nchar(4),`在庫` int,`サイズ` nchar(2));
INSERT INTO 商品リスト VALUES
( 1,' fruits01 ', 'りんご',5,'中' )
,( 2,' fruits01 ', 'りんご',5,'大' )
,( 3,' fruits02 ', 'みかん',5,'中' )
,( 4,' fruits03 ', 'ぶどう',5,'中' );

CREATE TABLE 仮押さえリスト
(id int ,商品リストid int);
INSERT INTO 仮押さえリスト VALUES
( 1,2)
,( 2,3)
,( 3,3)
,( 4,1)
,( 5,3)
,( 6,3)
,( 7,3)
,( 8,1)
,( 9,1)
,( 10,1)
,( 11,1);
```

```SQL
SELECT
  商品名,
  Sum(在庫) - (SELECT COUNT(商品コード) FROM (SELECT 商品コード FROM 仮押さえリスト JOIN 商品リスト ON 仮押さえリスト.商品リストid = 商品リスト.商品リストid) AS T Where T.商品コード = 商品リスト.商品コード) AS 在庫
 FROM 商品リスト GROUP BY 商品名;
```

Answer

まずは商品リストと仮押さえリストをidで結合して新しくできたリストを集計しましょう。

訂正2

商品リストと(商品リストid毎の仮押さえ数)を結合して集計。

```SQL
SELECT
  s.商品名
  ,sum(s.在庫 - k.仮押さえ数)
FROM
  商品リスト s JOIN (
    SELECT
      商品リストid
      ,count(商品リストid) AS 仮押さえ数
    FROM
      仮押さえリスト
    GROUP BY
      商品リストid
    ) k ON s.商品リストid = k.商品リストid
GROUP BY
  s.商品名
```

商品名	在庫
りんご	4
みかん	0
ぶどう	5

関連した質問