人力検索はてな
モバイル版を表示しています。PC版はこちら
i-mobile

MySQLのJOINとGROUP BY、COUNTを組み合わせた場合の出力がうまくいきません。

「仕事」と「申し込み」の以下のようなテーブルを結合して

▼tb_job(仕事)
|job_id|name|
|1|漁師|
|2|医者|
|3|農家|
|4|警官|

▼tb_contact(申し込み)
|contact_id|job_id|user|
|1|2|佐藤|
|2|2|田中|
|3|1|本村|

各仕事の申込数を以下のように出力したいと思っています。

▼理想
|job_id|name|申込数|
|1|漁師|1|
|2|医者|2|
|3|農家|0|
|4|警官|0|

そこで上記2つ「tb_contact」を「job_id」でGROUP BYして
SQLで以下のように結合するのですが

SELECT *,COUNT(*) FROM tb_contact RIGHT JOIN tb_job ON tb_contact.job_id = tb_job.job_id GROUP BY tb_contact.job_id;

下記のように申し込みがある仕事については集計できるのですが、
申し込みがない仕事については1括りにされてしまい、COUNTにも
その合計が表示されてしまいます。

▼結果
|contact_id|job_id|user|job_id|name|COUNT(*)
|NULL|NULL|NULL|3|農家|2
|3|1|本村|1|漁師|1
|1|2|佐藤|2|医者|2

「▼理想」のように出力するにはどの様なSQL文を投げれば良いのでしょうか。
ご教授のほど宜しくお願い致します。


●質問者: gelgelgel
●カテゴリ:インターネット ウェブ制作
○ 状態 :終了
└ 回答数 : 3/3件

▽最新の回答へ

1 ● うぃんど
●50ポイント

2例ほど・・・
データの量や内容によって速度が逆転したりするので、
実データで比較して選択する必要があります
(下記は、動作確認せず、いきなり書いています・・・)

(a)

SELECT a.job_id, a.name
 , (SELECT COUNT(*) FROM tb_contact b WHERE b.job_id = a.job_id) AS `申込数`
FROM tb_job a
;

(b)

SELECT a.job_id, a.name, COUNT(*) AS `申込数`
FROM tb_job a
LEFT JOIN tb_contact b ON b.job_id = a.job_id
GROUP BY a.job_id, a.name
;

2 ● oil999
●50ポイント

次のSQL文をお試しください。

SELECT tb_job.job_id,tb_job.name,count(tb_contact.job_id) AS '申込数'
FROM tb_contact RIGHT JOIN tb_job
ON tb_contact.job_id = tb_job.job_id
GROUP BY tb_job.job_id;

3 ● khazad-Lefty
●100ポイント ベストアンサー

とりあえず、一番の原因はgroup by に 「tb_contactの」job_idを指定していることです。

tb_contactのjob_idに、3や4の値はないわけで、right joinした結果も、その値は当然nullです。
その結果「group by の値はnull」として、一列になってしまいます。

ということで、元のSQL文への変更を最小限にするなら

SELECT *,COUNT(*) 
FROM tb_contact 
RIGHT JOIN tb_job 
ON tb_contact.job_id = tb_job.job_id 
GROUP BY tb_job.job_id;

となります。

補足として、
group by または集計関数 を使用する時、Selectに「group by に指定されていない集計関数以外の列」を指定できてしまうのはMYSQLの独自拡張です。
今回も最初のSQLが「通ってしまう」ことが誤解の一員になっているんじゃないかと思います。
参考:寛容なMySQLを非寛容にすること(その3) | inquisitor

それをふまえた上で「理想」と同じ出力にするなら、こんな感じで。

SELECT tb_job.job_id,tb_job.name,COUNT(tb_contact.job_id) AS `申込数`
FROM tb_contact 
RIGHT JOIN tb_job 
ON tb_contact.job_id = tb_job.job_id 
GROUP BY tb_job.job_id,tb_job.name;

ちなみに、私の場合、

  1. JOINするテーブルには別名をつける(同じテーブルを複数JOINすることも多々あるため)
  2. RIGHT JOINは使用しない(RIGHT JOINはLEFT JOINに置き換え可能で、統一したほうが可読性が良くなるため)

というのを習慣にしているので、私が書くならこんな感じになります。

SELECT A.job_id,A.name,COUNT(B.job_id) AS `申込数`
FROM tbl_job AS A
LEFT JOIN tbl_contact AS B
ON A.job_id=B.job_id
GROUP by A.job_id,A.name;
関連質問

●質問をもっと探す●



0.人力検索はてなトップ
8.このページを友達に紹介
9.このページの先頭へ
対応機種一覧
お問い合わせ
ヘルプ/お知らせ
ログイン
無料ユーザー登録
はてなトップ