关于GROUP_CONCAT()
结构与数据:
mysql> select * from a;
+----+------+
| ID | b |
+----+------+
| 1 | 1,3 |
+----+------+
mysql> select * from b;
+----+------+
| ID | name |
+----+------+
| 1 | a |
| 2 | b |
| 3 | c |
+----+------+
a.b,是b.ID的连接字符串
===========================================================================
SQL:
mysql> SELECT a.*, GROUP_CONCAT(b.name) FROM a, b WHERE b.ID IN (a.b) GROUP BY a.ID;
+----+------+----------------------+
| ID | b | GROUP_CONCAT(b.name) |
+----+------+----------------------+
| 1 | 1,3 | a |
+----+------+----------------------+
mysql> SELECT a.*, GROUP_CONCAT(b.name) FROM a, b WHERE b.ID IN (1,3) GROUP BY a.ID;
+----+------+----------------------+
| ID | b | GROUP_CONCAT(b.name) |
+----+------+----------------------+
| 1 | 1,3 | a,c |
+----+------+----------------------+
=====================================================================
问题:
两条SQL的意图是一样的,只是前者用a.b作为条件,后者用一字符串“1,3”常量,而实际上a.b是等于“1,3”的,但结果却不同。
请问,如何修改第一条SQL以实现第二条SQL的效果?