假设我有这个架构：
_________ _________
| Table 1 | | Table 2 |
|---------| _______ ________|---------|
| id_a |___ | link1 | | | id_b |
| name | | |-------| | | info |
|_________| |_____| id_a |___| | data |
| | id_b | |_________|
| |_______|
|
| _______
|______________| link2 |_______
|-------| |
| id_a | | _________
| id_c | |__| Table 3 |
|_______| |---------|
| id_c |
| email |
|_________|
通过数据库中的这些信息：
表格1
_____________
| id_a | name |
|------|------|
| 1 | foo |
|______|______|
表2
_____________________
| id_b | info | data |
|------|------|-------|
| 1 | bar | corge |
| 2 | kux | corge |
| 3 | fred | quux |
|______|______|_______|
表3
_________________
| id_c | email |
|------|----------|
| 1 | [email protected] |
| 2 | [email protected] |
| 3 | [email protected] |
| 4 | [email protected] |
| 5 | [email protected] |
|______|__________|
链接1
_____________
| id_a | id_b |
|------|------|
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
|______|______|
链接2
_____________
| id_a | id_c |
|------|------|
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 1 | 4 |
| 1 | 5 |
|______|______|
当我尝试检索所需的数据时，我使用以下语法：
SELECT t1.*,
GROUP_CONCAT(DISTINCT t2.info SEPARATOR '|') AS info,
GROUP_CONCAT(DISTINCT t2.data SEPARATOR '|') AS data,
GROUP_CONCAT(DISTINCT t3.email SEPARATOR '|') AS email
FROM table1 AS t1
LEFT JOIN link1 AS l1
ON l1.id_a = t1.id_a
LEFT JOIN table2 AS t2
ON t2.id_b = l1.id_b
LEFT JOIN link2 AS l2
ON l2.id_a = t1.id_a
LEFT JOIN table3 AS t3
ON t3.id_c = l2.id_c
WHERE t1.id_a = ?
GROUP BY t1.id_a
问题是，当我在一列中有两个或多个相同值时（例如带有“表2” /“数据”的示例），结果将不会给出相同数量的串联值：
array(
'id_a' => '1',
'info' => 'bar|kux|fred',
'data' => 'corge|quux',
'email' => '[email protected]|[email protected]|[email protected]|[email protected]|[email protected]'
)
并且由于我需要级联值完全相同的数字，以便以后在我的代码中将其用作单个输出（例如Bar Corge，Kux Corge，Fred Quux），因此我想知道是否有可能将它们作为我期望直接在请求中？
所以我回答自己。我一直在寻找CONCAT_WS。我的要求变成
SELECT t1.*,
GROUP_CONCAT(DISTINCT CONCAT_WS(
' ', t2.info, t2.data
) SEPARATOR ', ') AS info,
GROUP_CONCAT(DISTINCT t3.email SEPARATOR '|') AS email
FROM table1 AS t1
LEFT JOIN link1 AS l1
ON l1.id_a = t1.id_a
LEFT JOIN table2 AS t2
ON t2.id_b = l1.id_b
LEFT JOIN link2 AS l2
ON l2.id_a = t1.id_a
LEFT JOIN table3 AS t3
ON t3.id_c = l2.id_c
WHERE t1.id_a = ?
GROUP BY t1.id_a
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句