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

MySQLのjoinで結合したテーブルを集計したいのですが、上手くできません。

// categoryのテーブル内
+-----+-----+-----+
|c_id | name|sort |
+-----+------+----+
|1 |テスト| 001 |
+-----+------+------+

// userのテーブル内
+------+-------+------+
| u_id | u_name| c_id |
+------+-------+------+
| 1 | aaa | 1 |
| 1 | bbb | 1 |
+------+-------+------+

// 試したSQL文
select user.c_id,count(*) as cnt from category JOIN user on category.id=user.c_id ORDER BY sort GROUP BY user.c_id


希望する結果は
テスト(2)

どのようなSQLを書けばいいのか、アドバイスいただければと思います。

●質問者: kt26
●カテゴリ:ウェブ制作
✍キーワード:AAA AS CNT GROUP MySQL
○ 状態 :終了
└ 回答数 : 5/5件

▽最新の回答へ

1 ● KUROX
●30ポイント

夜なので寝ぼけて間違ったことかいてたらごめんなさい。

MYSQLが標準SQLが通るDBと仮定してですけど・・。

-------------------------------------------------

データとSQLが間違ってませんか?

このデータとSQLだと、結果は1になりそうな?

userのテーブルu_idは、主キーじゃないんでしょうか?

ORDER BY sort の位置とカラム名も妥当でないような?

GROUP BY user.c_id

これでグループ化すれば、1件になるような・・。

仮にuser.u_idが1,2というデータであったとしても。

----------------------------------------------

MS-ACCESSを持っているなら、

テーブル作って、クエリ作成して・・。

クエリのところでSQL表示すれば、SQLを自動的に生成して

くれます。

◎質問者からの返答

すみません、質問で書いたuserテーブルのu_idは1と2です。主キーです。

何がしたいのかというと、要は「そのカテゴリにいくつのデータが登録されているのか?」を表示するのが目的です。

やっぱりこういうケースの場合、joinを使うのは適切ではないかも知れませんね。


2 ● KUROX
●10ポイント

データの意味がわかりました。

コメントもなんかいろいろ間違ってましたね(苦笑)。

すいません。


SELECT category.c_id, category.sort, Count(*) AS cnt, category.sort

FROM category INNER JOIN [user] ON category.c_id=user.c_id

GROUP BY category.c_id, category.sort

ORDER BY category.sort;

MS-Accessが生成したSQLですけど、大体こんな感じになる

と思います。

とりあえず、2件という結果はでました。


3 ● chuken_kenkou
●20ポイント

MySQLの質問は、できればバージョンを明記してください。

<変更項目>

(1)ORDER BYはGROUP BYの後に

(2)on category.id=user.c_id

→on category.c_id=user.c_id

(3)selectの選択リストに、nameを追加


<変更後のSQL>

select user.c_id,name,count(*) as cnt 
 from category 
 JOIN user 
 on category.c_id=user.c_id
 GROUP BY user.c_id
 ORDER BY sort

4 ● kn1967
●60ポイント

まずは頭の中で次のSQLの結果セットを想像してみてください。

SELECT a.*,b.*
FROM category a
 LEFT JOIN user b ON a.c_id=b.c_id

結果は・・・

a.c_id a.name a.sort b.u_id b.u_name b.c_id
1 テスト 001 1 bbb 1
1 テスト 001 1 aaa 1

となりますので、この表を集計するとなれば・・・

SELECT a.c_id, COUNT(*) as cnt
FROM category a
 LEFT JOIN user b ON a.c_id=b.c_id
GROUP BY a.c_id
ORDER BY MAX(a.sort);

となります。

GROUP BY句を使う場合の注意点として

GROUP指定されているフィールドはSELECT句やORDER BY句でそのまま使えるけれども

その他のフィールドは集約関数を使ってレコードを特定してやる必要がありますので

ここではMAX関数を使っています。


別の書き方として

SELECT a.c_id, (SELECT COUNT(*) FROM user b WHERE a.c_id=b.c_id) AS cnt
FROM category a;

ってなのもあります。

他にも派生タイプは多々ありますが

多くの場合は上記2つのいずれかを知っていれば事足りるでしょう。


余談ですがSQLでは予約語は大文字で書くのが一般的です。


5 ● kotaj90
●10ポイント

今実行環境が無いけど下のSQLでうまく行きませんか?

SELECT A.c_id,count(*)

FROM category A,user B

WHERE A.c_id=B.C_id

GROUP BY A.c_id

結果:

----------

1,2

-----------

関連質問


●質問をもっと探す●



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