在单个mysql查询中获取组中的用户(仅允许) [英] Get users(only allowed) in group in single mysql Query

问题描述

我必须根据以下逻辑从下表中获取指定组中允许的用户列表.

From following tables i have to fetch the list of users allowed in the specified group upon the basis of following logic.

• 情况1:如果没有匹配项，则所有用户都符合条件(没有记录 user_group表).
• 案例2:组中确实匹配的用户(具有 排除在user_group表
中= 0)
• 情况3:除以外的组中的用户 拒绝(排除为= 1)

• Case 1 : all users qualifies when there is no match (no record in user_group table).
• Case 2 : Users in group that do match (having exclusion = 0) in user_group table
• Case 3 : Users in group other than denied (having exclusion =1)

我的用户

user_id | user_name 
  1     |  a
  2     |  b
  3     |  c

我的群组

group_id | group_code
  1      |  G1
  2      |  G2
  3      |  G3

User_Group

userid  | groupid | exclusion
  1     |   1     |  0
  1     |   2     |  0
  2     |   1     |  1 

以下是适用于案例1和&的查询. 3，但情况2失败.

Here's the query that works for case 1 & 3 but fails for case 2.

SELECT u.id, g.id, g.code, ug.exclusion FROM MyUser u
join MyGroup g 
LEFT JOIN user_group ug ON ug.group_id = g.id 
     AND ug.user_id = 1 -- works for user 2 and 3 but fails for user 1         
WHERE (ug.exclusion <> 0 OR ug.exclusion IS NULL) 

因此，在排除= 1的记录的情况下，将获取该记录，并以空条件添加其他组.问题是，对于排除= 0，也将添加空字段，这根本不是必需的.

So, in the case of record with exclusion = 1 then that record gets and other groups are added with the null condition. And the problem is the null fields also gets added for exclusion = 0 which is not required at all.

请建议我们是否仅使用一次查询就可以获取给定组的用户列表

Please suggest if we can get the list of users for given group using just single query

预期结果:

g1(a,c), g2(a,b,c), g3(b,c) 

推荐答案

这应该可行，或者可以解决.

This should work, or something close to it.

select u.user_id, g.group_id, g.code
from MyGroup g
    cross join users u 
    left join user_group allowed ON u.user_id = allowed.user_id AND g.group_id = allowed.group_id AND allowed.exclusion = 0
    left join user_group allowbydefault ON u.user_id = allowbydefault.user_id
where (allowed.user_id is not null OR allowbydefault.group_id is null)
-- and g.group_id = 1 -- uncomment to get everyone authorized for group #1
-- and u.user_id = 35 -- uncomment to get every group that user #35 is authorized for
group by u.user_id, g.group_id, g.code

我使用SQL Server对此进行了测试，但这至少应该为您提供MySQL的另一个攻击点.

I used SQL Server to test this but this should at least give you another point of attack for MySQL.

这篇关于在单个mysql查询中获取组中的用户(仅允许)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！