I want query for show Table Result and Use MySQL query only
TABLE A
ID | NAME
-----+-------
n1 | BLACK
n2 | WHITE
n3 | RED
n4 | GREEN
TABLE B
ID | A_ID | BLABLA | TIME
---+---------+---------------+--------
1 | n1 | X | sometime
2 | n1 | X | sometime
3 | n1 | X | sometime
4 | n1 | Y | sometime
5 | n1 | Z | sometime
6 | n2 | Y | sometime
7 | n2 | Y | sometime
8 | n2 | Y | sometime
9 | n3 | X | sometime
10 | n3 | Z | sometime
11 | n3 | Z | sometime
12 | n3 | Z | sometime
My Question
I want query for show Table Result and Use MySQL query only.
TABLE RESULT
NAME | X | Y | Z
-----------+----------+---------+--开发者_如何学运维---
BLACK | 3 | 1 | 0
WHITE | 0 | 3 | 0
RED | 1 | 0 | 3
GREEN | 0 | 0 | 0
SELECT
a.name
, sum(CASE WHEN b.blabla LIKE 'X' THEN 1 ELSE 0 END) AS X
, sum(CASE WHEN b.blabla LIKE 'Y' THEN 1 ELSE 0 END) AS Y
, sum(CASE WHEN b.blabla LIKE 'Z' THEN 1 ELSE 0 END) AS Z
FROM tablea a
INNER JOIN tableb b ON (a.id = b.a_id)
GROUP BY a.name
I use like
instead of =
, because like
is always case-insensitive and =
is not.
If blabla
is an enum, then I recommend using =
.
精彩评论