sql
1 SELECT
2 news . news_no ,
3 news . news_category_no ,
4 news . news_title ,
5 category . category_no ,
6 category . category_name ,
7 tmp . cnt
8 FROM news
9 LEFT JOIN category ON news . news_category_no = category . category_no
10 LEFT JOIN ( SELECT like_news_no , COUNT ( * ) AS cnt FROM like_news GROUP BY like_news_no ) AS tmp
11 ON news . news_no = tmp . like_news_no
12 ORDER BY news . news_no ;
実行結果
mysql> CREATE TABLE news (
-> news_no INT,
-> news_category_no INT,
-> news_title VARCHAR(16)
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> CREATE TABLE category (
-> category_no INT,
-> category_name VARCHAR(4)
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> CREATE TABLE like_news (
-> like_no INT,
-> like_news_no INT
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> INSERT INTO news VALUES
-> (1, 1, '美味しいです'),
-> (2, 2, '甘いです'),
-> (3, 1, '不味いです');
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> INSERT INTO category VALUES
-> (1, '野菜'),
-> (2, '果物');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> INSERT INTO like_news VALUES
-> (1, 1),
-> (2, 2),
-> (3, 1),
-> (4, 3);
Query OK, 4 rows affected (0.01 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> SELECT
-> news.news_no,
-> news.news_category_no,
-> news.news_title,
-> category.category_no,
-> category.category_name,
-> tmp.cnt
-> FROM news
-> LEFT JOIN category ON news.news_category_no = category.category_no
-> LEFT JOIN (SELECT like_news_no, COUNT(*) AS cnt FROM like_news GROUP BY like_news_no) AS tmp
-> ON news.news_no = tmp. like_news_no
-> ORDER BY news.news_no;
+---------+------------------+--------------------+-------------+---------------+------+
| news_no | news_category_no | news_title | category_no | category_name | cnt |
+---------+------------------+--------------------+-------------+---------------+------+
| 1 | 1 | 美味しいです | 1 | 野菜 | 2 |
| 2 | 2 | 甘いです | 2 | 果物 | 1 |
| 3 | 1 | 不味いです | 1 | 野菜 | 1 |
+---------+------------------+--------------------+-------------+---------------+------+
3 rows in set (0.00 sec)
Update
上記の SQLだと、1つも「イイネ」されていない投稿の「イイネ数」(cnt
カラム)はNULL
になります。
そのような投稿の「イイネ数」を 0 として取得したい場合は、以下のようにすると良いでしょう。
sql
1 SELECT
2 news . news_no ,
3 news . news_category_no ,
4 news . news_title ,
5 category . category_no ,
6 category . category_name ,
7 IFNULL ( tmp . cnt , 0 ) AS cnt
8 FROM news
9 LEFT JOIN category ON news . news_category_no = category . category_no
10 LEFT JOIN ( SELECT like_news_no , COUNT ( * ) AS cnt FROM like_news GROUP BY like_news_no ) AS tmp
11 ON news . news_no = tmp . like_news_no
12 ORDER BY news . news_no ;
実行結果
mysql> INSERT INTO news VALUES (4, 2, '果物です');
Query OK, 1 row affected (0.01 sec)
mysql> SELECT
-> news.news_no,
-> news.news_category_no,
-> news.news_title,
-> category.category_no,
-> category.category_name,
-> IFNULL(tmp.cnt, 0) AS cnt
-> FROM news
-> LEFT JOIN category ON news.news_category_no = category.category_no
-> LEFT JOIN (SELECT like_news_no, COUNT(*) AS cnt FROM like_news GROUP BY like_news_no) AS tmp
-> ON news.news_no = tmp. like_news_no
-> ORDER BY news.news_no;
+---------+------------------+--------------------+-------------+---------------+-----+
| news_no | news_category_no | news_title | category_no | category_name | cnt |
+---------+------------------+--------------------+-------------+---------------+-----+
| 1 | 1 | 美味しいです | 1 | 野菜 | 2 |
| 2 | 2 | 甘いです | 2 | 果物 | 1 |
| 3 | 1 | 不味いです | 1 | 野菜 | 1 |
| 4 | 2 | 果物です | 2 | 果物 | 0 |
+---------+------------------+--------------------+-------------+---------------+-----+
4 rows in set (0.01 sec)
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。
2017/09/05 14:54