SQLで集計した結果を加える

Question

前提

このような3つのテーブルがあり、

articleテーブル(記事を投稿するテーブル)

id	title	user_id
1	RubyonRailsに関する記事	1
2	Javaに関する記事	2
3	swiftに関する記事	2

categoryテーブル(記事カテゴリーのテーブル)

id	name
1	RubyonRails
2	Java
3	swift

category_articleのテーブル（中間テーブル）

article_id	category_id
1	1
2	2
3	3

リレーションが定義されています

実現したいこと

category_articleを起点にして

select 
category_article.article_id,
category_article. category_id,
article.user_id
from category_article
left join article on category_article.article_id = article.id

としたあとに

user_idが全体でいくつあるかを集計したものを追加するSQLの書き方はありますでしょうか？

EX

user_id 2は 全体では2つあるので countは2

article_id	category_id	user_id	count
1	1	1	1
2	2	2	2
3	3	2	2

初心者なので、ご教示いただけると幸いです

Comments

[m.ts10806]

再現確認するため、関係するテーブルのCREATE TABLE文とサンプルデータのINSERT文をご提示ください。
また、タグより「MySQL」とのことですが、バージョンは幾つでしょうか。

[hoshi-takanori]

サブクエリーを使えばできると思いますが、一つの記事に複数の category_id がある場合はどうしましょう？

Answer 1

とりあえずこんな感じでしょうか。（category と article は多対多にしてみました。）
https://www.db-fiddle.com/f/muBACYLE8v8UtVUNFGXzAL/0

データベース定義

create table article (
  id int primary key,
  title varchar(255),
  user_id int
);

insert into article values (1, 'RubyonRailsに関する記事', 1);
insert into article values (2, 'Javaに関する記事', 2);
insert into article values (3, 'swiftに関する記事', 2);

create table category (
  id int primary key,
  name varchar(255)
);

insert into category values (1, 'RubyonRails');
insert into category values (2, 'Java');
insert into category values (3, 'swift');

create table category_article (
  article_id int references article (id),
  category_id int references category (id),
  primary key (article_id, category_id)
);

insert into category_article values (1, 1);
insert into category_article values (2, 2);
insert into category_article values (3, 3);

insert into category_article values (1, 3);
insert into category_article values (3, 2);

問い合わせ

select 
category_article.article_id,
category_article. category_id,
article.user_id,
(select count(*) from article a where a.user_id = article.user_id) as count
from category_article
left join article on category_article.article_id = article.id;

実行結果

+------------+-------------+---------+-------+
| article_id | category_id | user_id | count |
+------------+-------------+---------+-------+
|          1 |           1 |       1 |     1 |
|          1 |           3 |       1 |     1 |
|          2 |           2 |       2 |     2 |
|          3 |           2 |       2 |     2 |
|          3 |           3 |       2 |     2 |
+------------+-------------+---------+-------+

Comments

[退会済みユーザー]

ありがとうございます

質問頂いてたことですが
articleに複数のcategoryを紐づくイメージになります。

なので一対多ですかね。

[hoshi-takanori]

一つの記事に複数のカテゴリーを紐づけられるということだと思いますが、通常一つのカテゴリーには複数の記事があるでしょうから、多対多になるのでは。一対多なら中間テーブルは不要ですし。