mysql:不要在 SELECT 上显示重复的结果 [英] mysql : Don't show duplicate result on SELECT

问题描述

第三次选择显示结果与前两次选择重复的结果.所以可以说我的数据集只有 5 个 ID.前两条语句产生 ids 1,2,3 enabled = 1最后一条语句我需要 ids 4,5 enabled = 0

The third select show results duplicate results of the first 2 selects. So lets say my data set just 5 ids. The first two statements produces ids 1,2,3 enabled = 1 The last statement I need ids 4,5 enabled = 0

但现在我得到 ids 1,2,3,4,5 enabled = 1 PLUS ids 1,2,3,4,5 enabled = 0

But right now i'm getting ids 1,2,3,4,5 enabled = 1 PLUS ids 1,2,3,4,5 enabled = 0

SELECT p.*, 1 as enabled
    FROM member_permissions mp
    JOIN permissions p ON p.permission_id = mp.permission_id
    WHERE member_id = 1
UNION
    SELECT pgp.*, 1 as enabled
    FROM member_permissions mp
    JOIN permission_link pl ON pl.permission_group_id = mp.permission_group_id
    JOIN permissions pgp ON pgp.permission_id = pl.permission_id
    WHERE member_id = 1
UNION
    SELECT *, 0 as enabled
    FROM permissions p

推荐答案

在这种情况下，您可以尝试使用左连接.

You may try using left join in that case.

SELECT DISTINCT
       prm.*,
       CASE
         WHEN mp.member_id IS NULL OR mp2.member_id IS NULL THEN 0
         ELSE 1
       END AS ENABLED
  FROM permissions prm
  LEFT JOIN member_permissions mp
         ON mp.permission_id = prm.permission_id
        AND mp.member_id = 1
  LEFT JOIN permission_link pl
         ON pl.permission_group_id = prm.permission_group_id
  LEFT JOIN member_permissions mp2
         ON mp2.permission_group_id = pl.permission_group_id
        AND mp2.member_id = 1

我认为不是最好的解决方案，但它可能有效

Not a best solution as I think, but it may work

这篇关于mysql:不要在 SELECT 上显示重复的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！