SQL 自引用查询

Question

>我有一个包含四个表的架构 用户、组、成员资格和成员资格控件

成员资格是用户和组之间的M：M。

成员

身份控件指定用户必须已经是其成员才能成为组成员的组。

例如，要成为"绿色"组的成员，用户必须已经是"蓝色"或"黄色"组的成员。

当用户是"绿色"组的成员

时，其成员身份取决于他们是"蓝色"或"黄色"组的成员。如果用户不再是"蓝色"组的成员，则其现有成员资格将保留，但是如果他们不再是"黄色"组的成员，则应删除其"绿色"成员资格。

我正在尝试计算将从违反会员资格控制的会员资格记录中删除的 sql。

http://sqlfiddle.com/#!9/302d2

根据上面的小提琴，以下会员资格是有效的：(1, 1, 1)(2, 1, 2)(3, 1, 3)（4， 1， 4）

如果删除绿色和蓝色上面的中间两个成员资格，则唯一有效的成员资格将是：（1， 1， 1）

即，如果成员资格表包含：(1, 1, 1)(1, 1, 4)最后一条记录无效，应删除，因为它违反了成员资格控制。

这是因为成员身份控件指定要成为组：4 的成员，您还需要是组：2 或组：3 的成员

Answer 1

尝试：

delete from memberships
where user_id in
  (
    select    user_id
    from      memberships
    group by  user_id
    having    sum(case when group_id = 4 then 1 else 0 end) = 1
          and sum(case when group_id = 2 then 1 else 0 end) = 0
          and sum(case when group_id = 3 then 1 else 0 end) = 0
  )
  and group_id = 4

简而言之，这会从成员资格表中删除用户属于组 4 的所有行，但不会删除组 2 或组 3（并且仅删除其组 4 行）。

Answer 2

我相信

你想做的是这样的：

Select * From Memberships
Where ((group_id = 1) OR (group_id = 2))
AND
user_id = 1

然后将这些user_ids纳入成员资格控制。这样就可以删除剩余的记录。

我不太了解MySQL语法，SQL Fiddle崩溃了，所以我无法测试它。我确实发现这篇文章很有帮助：http://www.techonthenet.com/mysql/and_or.php

基本上如何使用 AND/OR 与 Where 语句。让我知道这是否有帮助。:)

Answer 3

下面是一个查询，它假定唯一有效的成员身份颜色组合是绿-蓝和绿-黄。 这分别对应于 6 和 7 的group_id和。 在我的查询中，我计算每个用户的组成员身份总和，并将该结果JOIN回Membership表。 然后，我删除任何没有有效group_id和的记录。

DELETE
FROM Memberships m
INNER JOIN
(
    SELECT user_id, SUM(group_id) AS group_sum
    FROM Memberships
    GROUP BY user_id
) m2
ON m.user_id = m2.user_id
WHERE m2.group_sum != 6 OR m2.group_sum != 7

如果允许或不允许的整个颜色集具有唯一的总和，则此方法应该没问题。 我还假设您不需要@BrianDeMilia指出的触发器。

Answer 4

子查询标识用户有权成为其成员的组，父查询标识用户当前所属的组。

当此查询针对上面的小提琴运行时，它不会标识要从成员资格中删除的记录。如果从成员资格表 （2， 1， 2） 和 （3， 1， 3） 中删除中间两行，则查询从成员资格中标识要删除的一行。

SELECT Memberships.*
FROM Memberships
INNER JOIN MembershipControls ON Memberships.group_id = MembershipControls.group_id
WHERE Memberships.user_id = 1
AND Memberships.group_id NOT IN
(
  SELECT MembershipControls.group_id
  FROM GROUPS
  INNER JOIN Memberships ON Groups.id = Memberships.group_id
  INNER JOIN MembershipControls ON Memberships.group_id = MembershipControls.criteria_id
  WHERE Memberships.user_id = 1
  GROUP BY MembershipControls.group_id
)