带条件的 SQL Group By

Question

场景：我需要为 ASP.Net 应用程序的审核员创建一份报告。我有一个程序，可以解析授权标签目录中 web.config 文件的 XML，并创建一个报告，提供哪些用户可以访问站点结构中的哪个文件夹。报告如下所示。

用户名、名字、姓氏、目录、角色、访问权限、 上次登录日期

问题：正如您从报告中看到的一些目录（中间其中包含 GISMO 的列）显示两次，同时允许和拒绝用户。我想知道是否有一种方法可以对结果进行分组，如果有一行允许目录，则不会显示拒绝的行，否则会显示拒绝的行。

或者，如果可以在 VB.net/C# 中操作，这也是一个选项。它返回那里并被输入到 Excel 电子表格中。

任何帮助表示赞赏。提前致谢。

编辑：我应该解释得更好。我仍然需要拒绝行来显示目录中是否不允许用户。但是，如果允许它们，则没有必要显示拒绝行。

Answer 1

这适用于 Oracle 数据库，因此它应该可以工作或让您接近 SQL Server，因为我知道 SQL Server
支持其主要组成部分，即 CASE 操作。

CREATE TABLE user_permissions (
     user_role   VARCHAR2(10) NOT NULL,
     dir         VARCHAR2(10) NOT NULL,
     user_access VARCHAR2(5)  NOT NULL
);

INSERT INTO user_permissions VALUES ('admin', 'dir1', 'allow');
INSERT INTO user_permissions VALUES ('admin', 'dir2', 'allow');
INSERT INTO user_permissions VALUES ('power', 'dir1', 'allow'); -- Allow and Deny dir1
INSERT INTO user_permissions VALUES ('power', 'dir1', 'deny');
INSERT INTO user_permissions VALUES ('power', 'dir2', 'deny');
COMMIT;


SELECT UNIQUE j.*
FROM (
       SELECT user_role, dir,
              MAX(CASE user_access WHEN 'allow' THEN 1 ELSE 0 END) allowFlag,
              MAX(CASE user_access WHEN 'deny'  THEN 1 ELSE 0 END) denyFlag
       FROM user_permissions
       GROUP BY user_role, dir
     ) t
JOIN user_permissions j ON (t.user_role = j.user_role AND t.dir = j.dir)
WHERE j.user_access = 'allow' OR (t.allowFlag = 0 and user_access = 'deny');

结果：

USER_ROLE  DIR        USER_ACCESS 
---------- ---------- ----------- 
admin      dir1       allow       
admin      dir2       allow       
power      dir1       allow       
power      dir2       deny        

基本上，您使用数据透视表将多行聚合成一行来描述目录的属性。获得聚合行后，就可以轻松比较已声明的属性以连接要显示的行。

Answer 2

如果您有 SQL Server 2005 或更高版本，则可以使用以下命令：

with cte as (
    select ROW_NUMBER() OVER (partition by username, directory order by access) as row, *
    from report
)
select *
from cte
where row = 1

在分区子句中，输入使“组”唯一的任何内容。

参考：
http://msdn.microsoft.com/en-us/library/ms190766.aspx

http://msdn.microsoft.com/en-us/library/ms186734.aspx

Answer 3

像这样的东西应该可以工作，但这假设您的路径没有输入，例如 /directory/directory/、/directory/Directory/、directory/directory/default.aspx 等。您最好的选择是解析数据并删除重复项在您创建的 .NET 进程级别，因为该阶段的解析通常更容易。

select derived.*,
       case when exists 
         (select top 1 1 from table_name as t2 where t2.username = derived.username and t2.directory=derived.directory and t2.access = 'allow') then 1 else 0 end as is_allowed,
       case when exists 
         (select top 1 1 from table_name as t2 where t2.username = derived.username and t2.directory=derived.directory and t2.access = 'deny') then 1 else 0 end as is_denied,
from
(
 select distinct t.username, t.firstname, t.lastname, t.directory
  from table_name as t
) as derived

Answer 4

SELECT UserName, Firstname, LastName, Directory, Roles, Access, LastLoginDate 
FROM Report R
WHERE Access = 'allow'
   OR ( Access = 'deny' 
      AND NOT EXISTS
         ( SELECT *
           FROM Report R2
           WHERE R2.Directory = R.Directory
             AND R2.UserName = R.UserName 
             AND R2.Roles = R.Roles
         )
      )

根据您的评论，应删除此行，因此仅检查 (UserName, Directory) 组合：

             AND R2.Roles = R.Roles