MySQL 查询从 5 个表中选择连接

Question

我有 5 张 table :

tag: {tagID,tagDetails}
user: {userID,userDetails,u_status=[active or banned]}
subscribe: {tagID,userID}
item: {itemID, itemDetails,i_status=[active or banned]}
item_tag: {tagID,itemID}

我想选择这些数据:

result: {tagID, tagDetails, num_subscribers, num_items}

对于每个 tagID，num_subscribers 是 user 表下 u_status 为 'active' 且 userID 和该 tagID 存在于 subscribe 表中的用户数量

对于每个 tagID，num_items 是表 item 下 i_status 为 'active' 并且其 itemID 和该 tagID 存在于表 item_tag 中的用户数量

此外，我希望所有 tagID 都出现在结果中。如果没有用户订阅或与该 tagID 关联的项目，则记录将为

{tagID,tagDetails,0,0}

产生此结果的最佳(尽可能“人类可读”)的嵌套查询是什么？

Answer 1

这就是你想要的吗？

        SELECT T.tagID, T.tagDetails, count(distinct U.u_status), count(distinct I.i_status)
        FROM tag T
             JOIN subscribe S ON T.tagID = S.tagID
             JOIN user U ON S.userID = U.userID
             JOIN item_tag IT ON T.tagID = IT.tagID
             JOIN item I ON IT.itemID = I.itemID
        WHERE U.u_status = 'active' and I.i_status = 'active'
        GROUP BY T.tagID

        UNION

        (SELECT tagID, tagDetails, 0, 0
        FROM tag
        EXCEPT
        SELECT T.tagID, T.tagDetails, 0, 0
        FROM tag T
             JOIN subscribe S ON T.tagID = S.tagID
             JOIN user U ON S.userID = U.userID
             JOIN item_tag IT ON T.tagID = IT.tagID
             JOIN item I ON IT.itemID = I.itemID
        WHERE U.u_status = 'active' and I.i_status = 'active'
        GROUP BY T.tagID)

您可能需要摆弄括号和别名。