// 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を書けばいいのか、アドバイスいただければと思います。
夜なので寝ぼけて間違ったことかいてたらごめんなさい。
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を自動的に生成して
くれます。
データの意味がわかりました。
コメントもなんかいろいろ間違ってましたね(苦笑)。
すいません。
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件という結果はでました。
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
まずは頭の中で次の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では予約語は大文字で書くのが一般的です。
今実行環境が無いけど下の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
-----------
すみません、質問で書いたuserテーブルのu_idは1と2です。主キーです。
何がしたいのかというと、要は「そのカテゴリにいくつのデータが登録されているのか?」を表示するのが目的です。
やっぱりこういうケースの場合、joinを使うのは適切ではないかも知れませんね。