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

となるのが理想です。

ですが現在はかなり無理矢理になっている気がしてもっと効率のいい方法があると思い今回質問させていただきました。
現在の状態は

SELECT
	商品コード,
	max(商品名) AS 商品名,
	SUM(ifnull(在庫,0)) AS 在庫数
FROM
	商品リスト
GROUP BY
	商品コード

商品コード	商品名	在庫
fruits01	りんご	10
fruits02	みかん	５
fruits03	ぶどう	５

まず商品コードごとの在庫数を取得しています。
そのあとに仮押さえリストからCOUNTで取得します。
その際商品リストにJOINして商品コードを取得しています。

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のみでできる方法があればご教授願いたいのですがよろしくお願いいたします。

Answer 1

一筆で書くならこんな感じ。

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 2

こちらで解決いたしました。

他にも意見がありましたら本日まで、ぜひお待ちしております。

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.商品名

Comments

[yambejp]

普通にやるならこれでしょうね
しかし問題は「商品リスト 」がマスターデータと在庫データの両方を兼ねていることです
在庫データは別テーブルで管理するとなぜこの方法になるかわかると思いますよ

[space_sss]

コメントありがとうございます。
saziさんのやり方が商品在庫と商品リストが分かれているのでわかりやすいとも思ったのですがこちらを参考にしてそもそも在庫テーブルを分けて管理しJOINした方が整理されて良いのかなとも思いました。
やはりそちらの方が一般的なのでしょうか？
お手数ではありますがご回答いただければ幸いです。

[yambejp]

再質問の意図がわかりかねるのですが
単純に在庫データは頻繁に変動するデータで
その他のマスターデータは基本的には変動しないデータということ
それを同じテーブルで管理するのは本来のSQLのデータ管理とは
乖離があるということです。

Answer 3

SELECT
  商品名,
  Sum(在庫) - (SELECT COUNT(商品リストid) FROM 仮押さえリスト Where 商品リストid= Goods.商品リストid) AS 在庫
 FROM 商品リスト GROUP BY 商品リストid, 商品名;

-- 修正

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);

SELECT
  商品名,
  Sum(在庫) - (SELECT COUNT(商品コード) FROM (SELECT 商品コード FROM 仮押さえリスト JOIN 商品リスト ON 仮押さえリスト.商品リストid = 商品リスト.商品リストid) AS T Where T.商品コード = 商品リスト.商品コード) AS 在庫
 FROM 商品リスト GROUP BY 商品名;

Comments

[papinianus]

Create文とテストデータのinsert文をかいて。つらい。

[space_sss]

CREATE TABLE 商品リスト
(商品リストid ,商品コード,商品名,在庫,サイズ);
INSERT INTO 商品リスト VALUES
( 1,' fruits01 ', 'りんご',5,'中' )
,( 2,' fruits01 ', 'りんご',5,'大' )
,( 3,' fruits02 ', 'みかん',5,'中' )
,( 4,' fruits03 ', 'ぶどう',5,'中' );

CREATE TABLE 仮押さえリスト
(id ,商品リストid);
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);
こちらでどうでしょうか？

[space_sss]

上記の回答ですと商品IDごとに表示が出てしまうかと思うのですが....
りんご　0
りんご　4
みかん　0
ぶどう　5
と表示になってしまうかと思います。。。
一応希望としては
りんご 4
みかん 0
ぶどう 5
でしたのでもしお手数でなければご回答いただければと思います。

[papinianus]

できてたと思ってたってことはinsertのときに商品リストidを同じにしてたのかな。

修正しましたが、なんか面倒なクエリになってしまいました。

Answer 4

まずは商品リストと仮押さえリストをidで結合して新しくできたリストを集計しましょう。

訂正2

商品リストと(商品リストid毎の仮押さえ数)を結合して集計。

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.商品名

Comments

[nullpon]

おっと、質問を勘違いしていたので無視して下さい。

[nullpon]

訂正を追加しました。

[nullpon]

商品名毎に集計するように訂正。

[space_sss]

ご回答ありがとうございました！
だいぶ参考になりました！ただこれですとぶどうが引き算した際にNULLになり落ちてしまうので一部修正させていただきました！
ご回答ありがとうございます！