Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?

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

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 |
+----+------+--------+---------+

このとき typegroup 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 とかしててそもそもこんなクエリ投げるのか?って気になっている

@fujiwara

This comment has been minimized.

Copy link

fujiwara commented Feb 24, 2015

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 |
+------+------+--------+---------+
@fujiwara

This comment has been minimized.

Copy link

fujiwara commented Feb 24, 2015

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 |
+------+------+--------+--------+---------+
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.