时间戳

首页 > 解决方案 > Mysql LEFT JOIN 两次在不同的子表上使用相同的父主键

mysql - Mysql LEFT JOIN 两次在不同的子表上使用相同的父主键

问题描述

我有 4 张桌子：
T1 {id, user_name, email, telephone, created_at, updated_at}
T2 {id, menu_name, town, district, t1_foreign, view_budget, view_menu, created_at, updated_at}
T3 {id, t2_foreign, menu_item_name, ingredients, calories, cost_ex, cost_in, selling_in, selling_ex, image_dir, img_caption, view_item, created_at, updated_at}
T4 {id, t1_foreign, t2_foreign, created_at, updated_at}

来自的用户T1链接到T2on中的菜单T2.t1_foreign。最初的想法是，只有作为区域代表的链接用户才能看到 table 中菜单项的成本T3。但是这种情况发生了变化，因此T4创建了表以将其他用户链接到T2on T4.t2_foreign，T4.t1_foreign因此他们也将能够查看特定菜单的成本。

我正在尝试创建一个摘要查询，以便我可以查看菜单的信息以及链接用户的数量，但我得到了重复的聚合 COUNT。我发现这是由于具有相同别名的双重联接T2，但似乎无法解决这个问题。

SELECT t2.id t2.menu_name, t2.town, t2.district, t2.view_menu, t2.view_budget, 
IF(COUNT(t4.t2_foreign)=0, "1", CONCAT("1+", COUNT(t4.t2_foreign), " Others")) AS linked, // <=== aggregate function
GREATEST(t2.updated_at, t2.created_at, COALESCE(t3.updated_at, \'1000-01-01\'), COALESCE(t3.created_at, \'1000-01-01\')) AS newest
    FROM T2 AS t2
        LEFT JOIN T3 AS t3
            ON (t3.t2_foreign = t2.id)
        INNER JOIN T1 AS t1
            ON (t2.t1_foreign = t1.id)
        LEFT JOIN T4 AS t4
            ON (t4.t2_foreign = t2.id) // this creates duplicate count
    GROUP BY t2.id
    ORDER BY (newest, ASC)

我尝试了子查询连接

SELECT t2.id t2.menu_name, t2.town, t2.district, t2.view_menu, t2.view_budget, 
tt.linked, 
GREATEST(t2.updated_at, t2.created_at, COALESCE(t3.updated_at, \'1000-01-01\'), COALESCE(t3.created_at, \'1000-01-01\')) AS newest
    FROM T2 AS t2
        LEFT JOIN T3 AS t3
            ON (t3.t2_foreign = t2.id)
        INNER JOIN T1 AS t1
            ON (t2.t1_foreign = t1.id)
        LEFT JOIN (
            SELECT IF(COUNT(t4.t2_foreign)=0, "1", CONCAT("1+", COUNT(t4.t2_foreign), " Others")) AS linked
                FROM T4
            ) AS t4 ON (t4.t2_foreign = t2.id) // this give not unique table/alias error
    GROUP BY t2.id
    ORDER BY (newest, ASC)

有人可以指出正确的方向以获得正确的汇总 COUNT 结果吗？

更新：虽然不是预期的结果
在摆弄我的子查询工作后，我得到了它返回一个结果，但结果是所有记录的聚合，而不是每条记录的聚合。不得不将其更改为：

        LEFT JOIN (
            SELECT IF(COUNT(t4.t2_foreign)=0, "1", CONCAT("1+", COUNT(t4.t2_foreign), " Others")) AS linked, t4.t2_foreign
                FROM T4 as t4
            ) AS tt ON (tt.t2_foreign = t2.id)

虽然在正确的轨道上。

标签： mysqlcountleft-joininner-joinaggregate-functions

解决方案

最后不需要加入，我使用相关子查询作为SELECT评论中建议的子查询。子查询：

( SELECT IF(COUNT(t4.t2_foreign)=0, "1", CONCAT("1+", COUNT(t4.t2_foreign), " Others")) AS linked
    FROM T4 as t4
    WHERE t4.t2_foreign=t2.id
    ) AS linked

这为 T2 中的每个主键检索了表 T4 到表 T2 中链接用户数的聚合 COUNT。

推荐阅读