主页 / dba / 问题 / 264213
Accepted
mike16889
Asked: 2020-04-03 20:21:09 +0800 CST

条件内连接

  • 772

我需要以有条件的方式基于 2 个不同的表为用户获取允许的 itemTypes。如果用户 userGroup 在表中有任何条目，它应该只显示ANDuserGroupItemTypes之间共有的项目，但如果该用户 userGroup中不存在条目，那么它应该只从userGroupItemTypesclientItemTypesuserGroupItemTypesclientItemTypes

项目类型

itID    itemType
1       Item 1
2       Item 2
3       Item 3

客户

cID     client
1       Client 1

用户组

ugID    userGroup
1       Group 1
2       Group 2

用户

uID     user     cID     ugID
1       Fred     1       1
2       Sam      1       2

客户项目类型

cID     itID
1       1
1       2
1       3

userGroupItemTypes

ugID    itID
2       1
2       2

结果应该是：

uID     itID     user     itemType
1       1        Fred     Item 1
1       2        Fred     Item 2
2       1        Sam      Item 1
2       2        Sam      Item 2
2       3        Sam      Item 3

基本上我认为查询的功能应该如下：

SELECT u.uID, it.itID, u.user, it.itemType
FROM users u
INNER JOIN clientItemTypes cit ON cit.cID = u.CID
INNER JOIN itemTypes it ON it.itID = cit.itID
IF((SELECT COUNT(*) FROM userGroupItemTypes GROUP BY uID) > 0, INNER JOIN userGroupItemTypes ugit ON ugit.ugID = u.ugID, DO NOTHING)

但我不知道如何实际实现这一点。任何帮助将不胜感激

编辑：这是一个dbfiddle

mysql join

1 个回答

  1. Best Answer

    根据 Lennarts 的建议，我使用了 UNION 来获得我需要的结果：

    SELECT u.uID, it.itID, u.user, it.itemType
FROM users u
INNER JOIN clientItemTypes cit ON cit.cID = u.CID
INNER JOIN userGroupItemTypes ugit ON ugit.ugID = u.ugID
INNER JOIN itemTypes it ON it.itID = ugit.itID
INNER JOIN (SELECT COUNT(ugit.ugID) AS total, uID FROM users u LEFT JOIN userGroupItemTypes ugit ON u.ugID = ugit.ugID GROUP BY uID) c ON c.uID = u.uID
WHERE c.total > 0
GROUP BY uID, itID
UNION
SELECT u.uID, it.itID, u.user, it.itemType
FROM users u
INNER JOIN clientItemTypes cit ON cit.cID = u.CID
INNER JOIN itemTypes it ON it.itID = cit.itID
INNER JOIN (SELECT COUNT(ugit.ugID) AS total, uID FROM users u LEFT JOIN userGroupItemTypes ugit ON u.ugID = ugit.ugID GROUP BY uID) c ON c.uID = u.uID
WHERE c.total = 0
GROUP BY uID, itID

    小提琴手

    基本上运行相同的查询两次，一次要求 userGroup 有 itemTypes，另一个要求 userGroup 没有 itemTypes，然后使用联合来组合结果。

    我不确定这是否是最有效的方法，但除非我能找到另一种方法，否则它必须这样做。

    替代解决方案（感谢 Lennart）：

    使用EXISTS和NOT EXISTS

    SELECT u.uID, it.itID, u.user, it.itemType
FROM users u
INNER JOIN clientItemTypes cit ON cit.cID = u.CID
INNER JOIN userGroupItemTypes ugit ON ugit.ugID = u.ugID
INNER JOIN itemTypes it ON it.itID = ugit.itID
WHERE EXISTS (
    SELECT 1 FROM userGroupItemTypes ugit
    WHERE u.ugID = ugit.ugID
)
GROUP BY uID, itID
UNION
SELECT u.uID, it.itID, u.user, it.itemType
FROM users u
INNER JOIN clientItemTypes cit ON cit.cID = u.CID
INNER JOIN itemTypes it ON it.itID = cit.itID
WHERE NOT EXISTS (
    SELECT 1 FROM userGroupItemTypes ugit
    WHERE u.ugID = ugit.ugID
)
GROUP BY uID, itID

    小提琴手

    • 2

相关问题