我有5张桌子:

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}


对于每个标记ID,num_subscribers是表用户下其u_status为“活动”并且其用户ID和所述标记ID存在于表subscribe中的用户数。

对于每个tagID,num_items是表项下其i_status为“活动”并且其itemID和所述tagID存在于表item_tag中的用户数

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

{tagID,tagDetails,0,0}


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

最佳答案

这是你所追求的吗?

        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)


您可能不得不摆弄括号和别名。

关于mysql - MySQL查询从5个表中选择联接,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/12669430/

10-13 00:47