mysql - 使用 Mysql - 左连接三个表的问题得到重复的 group_concat 结果
问题描述
我有以下表格,我想通过 left join 和 group_contact 加入
主表
+--------+---------+
| cedula | nombre |
+--------+---------+
| 002 | Eduardo |
| Hola | Carlos |
+--------+---------+
maintable_msel_sex1
+--------+------+--------------------------------------+
| cedula | sex1 | rowuuid |
+--------+------+--------------------------------------+
| 002 | 1 | 4ccc2b41-e72a-4d55-b167-17a41c18b29b |
| 002 | 2 | 89323daa-4ad4-4b43-9ecc-22d4fdd8fef1 |
| Hola | 1 | 9e7b4da0-2a93-4c82-80eb-3172bb163536 |
+--------+------+--------------------------------------+
maintable_msel_sex2
+--------+------+--------------------------------------+
| cedula | sex2 | rowuuid |
+--------+------+--------------------------------------+
| 002 | 2 | 122123d3-aa5e-42b5-8ddd-0e5d700bd92c |
| 002 | 1 | 462c8138-37b1-42bd-a0c8-4a63d9e67dda |
| Hola | 1 | 8d96c9c8-4633-4425-8f27-59591bee8954 |
| Hola | 2 | bbe42e16-fc9d-4e1d-825d-1eb59161f2fb |
+--------+------+--------------------------------------+
maintable_msel_crops
+--------+-------+--------------------------------------+
| cedula | crops | rowuuid |
+--------+-------+--------------------------------------+
| 002 | 3 | 404e7f5c-fb07-4d43-85a8-6c86c5407546 |
| Hola | 3 | 423d1d06-9be4-4d47-a5be-8407db4c258b |
| 002 | 4 | 6d27aa7f-b6d2-426b-9c05-c20f88d97d7b |
| 002 | 1 | 7673127b-72ed-45fd-8688-8f6942abeaa0 |
| Hola | 1 | 80f84fbe-ad47-4178-8577-eaa82562abaa |
+--------+-------+--------------------------------------+
我尝试使用以下选择加入他们:
SELECT maintable.cedula,GROUP_CONCAT(maintable_msel_sex1.sex1) as sex1, GROUP_CONCAT(maintable_msel_sex2.sex2) as sex2, GROUP_CONCAT(maintable_msel_crops.crops) as crops
FROM maintable
LEFT JOIN maintable_msel_sex1 ON maintable.cedula = maintable_msel_sex1.cedula
LEFT JOIN maintable_msel_sex2 ON maintable.cedula = maintable_msel_sex2.cedula
LEFT JOIN maintable_msel_crops ON maintable.cedula = maintable_msel_crops.cedula
GROUP BY maintable.cedula;
但我得到:
+--------+-------------------------+-------------------------+-------------------------+
| cedula | sex1 | sex2 | crops |
+--------+-------------------------+-------------------------+-------------------------+
| 002 | 1,1,1,1,1,1,2,2,2,2,2,2 | 1,1,1,2,2,2,1,1,1,2,2,2 | 1,3,4,1,3,4,1,3,4,1,3,4 |
| Hola | 1,1,1,1 | 1,1,2,2 | 1,3,1,3 |
+--------+-------------------------+-------------------------+-------------------------+
我想得到:
+--------+-------------------------+-------------------------+-------------------------+
| cedula | sex1 | sex2 | crops |
+--------+-------------------------+-------------------------+-------------------------+
| 002 | 1,2 | 1,2 | 1,3,4 |
| Hola | 1 | 1,2 | 1,3 |
+--------+-------------------------+-------------------------+-------------------------+
我尝试使用 INNER JOIN 和 RIGHT JOIN 并且只使用一个 JOIN 和 () 中的多个表,但我只是没有得到这样的结果。
我可以在 group_concat 中使用 distinct 作为补丁,但我不明白为什么我会得到重复的结果。
任何帮助表示赞赏
解决方案
如果我正确理解您的问题,您只想在GROUP_CONCAT
. 为此,您可以使用DISTINCT
:
SELECT
maintable.cedula,
GROUP_CONCAT(DISTINCT maintable_msel_sex1.sex1) as sex1,
GROUP_CONCAT(DISTINCT maintable_msel_sex2.sex2) as sex2,
GROUP_CONCAT(DISTINCT maintable_msel_crops.crops) as crops
FROM maintable
LEFT JOIN maintable_msel_sex1 ON maintable.cedula = maintable_msel_sex1.cedula
LEFT JOIN maintable_msel_sex2 ON maintable.cedula = maintable_msel_sex2.cedula
LEFT JOIN maintable_msel_crops ON maintable.cedula = maintable_msel_crops.cedula
GROUP BY maintable.cedula;
有关更多信息,请参阅GROUP_CONCAT() 上的 MySQL DISTINCT和http://dev.mysql.com/doc/refman/5.0/en/group-by-functions.html#function_group-concat
推荐阅读
- java - Windows 10 更改 java 版本?
- python - 有没有办法通过python中的参数在列表中搜索对象?
- winapi - GetDoubleClickTime 意外返回 0
- wicket - 使用 CaptchImageResource 创建的 Captch 图像不会立即与其余网页内容一起显示,而是在大约 5 秒延迟后显示
- html - 离子选择中的字符串不会改变,除非它被点击
- ffmpeg - 当有多个drawtext目标时,如何编写函数“avfilter_graph_send_command”(ffmpeg)的*args?
- c++ - 移动语义与 const 引用
- android - 评分栏在小屏幕上无法正常工作
- javascript - SyntaxError:JSON 输入角度意外结束
- qt - 具有统一 vec3 数组的 Qt3D 着色器图