gistfile1.md

例えばこういうのがあって

CREATE TABLE sample (
  id      BIGINT  unsigned NOT NULL auto_increment,
  type    TINYINT unsigned NOT NULL DEFAULT 0,
  body    TEXT             NOT NULL,
  is_read TINYINT unsigned NOT NULL DEFAULT 0,
  primary key (id)
) ENGINE=InnoDB DEFAULT CHARACTER SET utf8mb4;

insert into sample
  (id, type, body, is_read)
values
  (null, 1, 'hoge01', 0),
  (null, 1, 'hoge02', 0),
  (null, 1, 'hoge03', 1),
  (null, 1, 'hoge04', 1),
  (null, 2, 'hoge05', 0),
  (null, 2, 'hoge06', 0),
  (null, 2, 'hoge07', 0),
  (null, 3, 'hoge08', 0),
  (null, 3, 'hoge09', 1),
  (null, 3, 'hoge10', 1);

mysql> select * from sample;
+----+------+--------+---------+
| id | type | body   | is_read |
+----+------+--------+---------+
|  1 |    1 | hoge01 |       0 |
|  2 |    1 | hoge02 |       0 |
|  3 |    1 | hoge03 |       1 |
|  4 |    1 | hoge04 |       1 |
|  5 |    2 | hoge05 |       0 |
|  6 |    2 | hoge06 |       0 |
|  7 |    2 | hoge07 |       0 |
|  8 |    3 | hoge08 |       0 |
|  9 |    3 | hoge09 |       1 |
| 10 |    3 | hoge10 |       1 |
+----+------+--------+---------+

このとき type で group by して group by した中で最新の body を取ってこれるか

mysql> select body, count(case when is_read = 0 then 1 else null end) as unread from sample group by type order by id desc;
+--------+--------+
| body   | unread |
+--------+--------+
| hoge08 |      1 |
| hoge05 |      3 |
| hoge01 |      2 |
+--------+--------+

これだと group by 後に id でソートされてるだけなので期待通りではない

なお、case when とかしててそもそもこんなクエリ投げるのか？って気になっている

group byしたサブクエリと自己結合したらいいのでは

SELECT * FROM (SELECT max(id) AS id, type FROM sample GROUP BY type) AS s1 JOIN sample s2 USING(id, type);
+------+------+--------+---------+
| id   | type | body   | is_read |
+------+------+--------+---------+
|    4 |    1 | hoge04 |       1 |
|    7 |    2 | hoge07 |       0 |
|   10 |    3 | hoge10 |       1 |
+------+------+--------+---------+

unreadはちょっとあれだけど is_read が 0 or 1 なら count(*) - sum(is_read) で求められそう?

SELECT * FROM (SELECT max(id) AS id, type, count(*)-sum(is_read) as unread FROM sample GROUP BY type) AS s1 JOIN sample s2 USING(id, type);
+------+------+--------+--------+---------+
| id   | type | unread | body   | is_read |
+------+------+--------+--------+---------+
|    4 |    1 |      2 | hoge04 |       1 |
|    7 |    2 |      3 | hoge07 |       0 |
|   10 |    3 |      1 | hoge10 |       1 |
+------+------+--------+--------+---------+