mysql - 多选语句

Question

用户表

id   u_id   name   phone       email
1    912    abcd   23424    abcd@email.com
2    913    xyz    45342     xyz@email.com
3    916    pqrt   23239    wdiw@email.com

主题表

s_id   name
    1     eng
    2     math
    3     phy

城市表

c_id   name
    1     city1
    2     city2
    3     city3

位置表

 l_id   name
    1     loc1
    2     loc2
    3     loc3

user_to_subject 表

u_id    s_id
913      2
913      3

user_to_city 表

u_id    c_id
913      1
913      3

user_to_location 表

u_id    l_id
913      2
913      3

我想做的是选择一个包含完整细节的语句，这就是我尝试过的。

SELECT U.*, GROUP_CONCAT(S.`name`) AS subject, GROUP_CONCAT(C.`name`) AS class, GROUP_CONCAT(L.`name`) AS location FROM `user` U INNER JOIN `user_to_subject` UTS ON U.`u_id`=UTS.`u_id` INNER JOIN `subjects` S ON S.`s_id`=UTS.`s_id` INNER JOIN `user_to_class` UTC ON U.`u_id`=UTC.`u_id` INNER JOIN `city` C ON C.`c_id`=UTC.`c_id` INNER JOIN `user_to_location` UTL ON U.`u_id`=UTL.`u_id` INNER JOIN `location` L ON L.`l_id`=UTL.`l_id` WHERE U.`u_id`=913

我有重复的主题、城市和位置。不确定我在这里做错了什么。

Answer 1

对于您的情况，最简单的解决方案是使用 group_concat(distinct):

SELECT U.*,
       GROUP_CONCAT(DISTINCT S.`name`) AS subject,
       GROUP_CONCAT(DISTINCT C.`name`) AS class,
       GROUP_CONCAT(DISTINCT L.`name`) AS location
FROM `user` U INNER JOIN
     `user_to_subject` UTS
     ON U.`u_id` = UTS.`u_id` INNER JOIN
     `subjects` S
     ON S.`s_id` = UTS.`s_id` INNER JOIN
     `user_to_class` UTC
     ON U.`u_id` = UTC.`u_id` INNER JOIN
     `city` C
     ON C.`c_id` = UTC.`c_id` INNER JOIN
     `user_to_location` UTL
     ON U.`u_id` = UTL.`u_id` INNER JOIN
     `location` L
     ON L.`l_id`=UTL.`l_id`
WHERE U.`u_id` = 913;

这不是最通用的解决方案，因为它将所有中间结果生成为笛卡尔积(针对每个用户)。因此，如果您有 2 个类别、3 个位置和 4 个城市，那么会生成 24 个中间行。

24 行对于这个查询来说不是问题。但是，如果您有 200 个类(class)、300 个位置和 400 个城市，则中间结果会稍大一些。最通用的解决方案是预先聚合结果或使用相关子查询。

此外，如果您想保留缺少一个或多个属性的用户，您可能需要 LEFT JOIN 而不是 INNER JOIN。