开发者

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 =.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜