MS SQL Server
  1. MS SQL Server
  3. SQL Group By avec une condition
Intereting Posts
SQL – sélectionne toutes les lignes et remplace les valeurs de colonne par la valeur de colonne max avec identificateur supprimer tous les espaces de la string T-SQL Erreur d'instruction de mise à jour: La sous-requête a renvoyé plus de 1 valeur ASP.Net SessionState à l'aide de SQL Server – datatables sont-elles chiffrées? Insertion en bloc dans SQL Server NullReferenceException lors de l'ajout d'un SqlParameter à un SqlParameterCollection Le slider FETCH a échoué. @@ FETCH_STATUS est -1 J'ai un problème en essayant de UNION All in SQL Server 2008 Sql Server 2005 – gérer la concurrency sur les tables SQL Server: renvoie uniquement des valeurs uniques par groupe (non distinctes) (pas une de chaque valeur) SQL Query – Sont-ils les mêmes? Les transactions consécutives finissent par promouvoir vers MSDTC Index T-SQL du premier caractère dans une string Contrôles de simultanéité avec Entity Framework et les procédures stockées Détection des cellules dans la colonne qui provoquent une erreur dans SQL

SQL Group By avec une condition

Scénario : J'ai besoin de créer un rapport pour les auditeurs pour une application ASP.Net. J'ai un programme qui parsing le XML des files web.config dans un directory pour la balise d'autorisation et crée un rapport indiquant quels users ont access à quel dossier dans la structure du site. Le rapport est illustré ci-dessous.

NomUtilisateur, Prénom, Nom, Répertoire, Rôles, Accès, LastLoginDate

entrez la description de l'image ici

Problème : Comme vous pouvez le voir dans le rapport, certains directorys (la colonne du milieu avec GISMO) apparaissent deux fois, avec à la fois autoriser et refuser un user. Je me request s'il existe un moyen de grouper les résultats de telle sorte que s'il y a une ligne qui permette un directory alors les refus ne sont pas montrés mais sinon ils le sont.

Alternativement, si cela peut être manipulé dans VB.net/C#, c'est aussi une option. Il revient là-bas et est pompé dans une feuille de calcul Excel.

Toute aide est appréciée. Merci d'avance.

Edit : j'aurais dû mieux expliquer. J'ai toujours besoin des lignes de refus pour montrer si l'user n'est pas autorisé dans le directory. Mais s'ils sont autorisés alors il n'y a aucun point montrant les lignes de refus.

Quelque chose comme ceci devrait fonctionner mais ceci suppose que vos paths n'ont pas été entrés tels que / directory / directory /, / directory / Directory /, directory / directory / default.aspx, etc. Votre meilleur pari serait d'parsingr datatables et enlever des duplicates au niveau du process .NET que vous avez créé, car l'parsing à ce stade est généralement plus facile. 

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

Cela fonctionne sur une database Oracle, il devrait donc fonctionner ou vous fermer sur SQL Server, car je sais que SQL Server prend en charge le composant principal de cette opération, l'opération 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');

Résultats: 

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

Fondamentalement, vous utilisez un tableau croisé dynamic pour agréger les lignes multiples dans une seule ligne décrivant les attributes pour le directory. Une fois que vous avez la ligne agrégée, il est facile de comparer les attributes que vous avez déclarés pour joindre les lignes que vous voulez afficher.

Si vous avez SQL Server 2005 ou plus récent, vous pouvez utiliser ceci: 

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

Dans la clause de partition, mettez tout ce qui rend un "groupe" unique.

Référence: http://msdn.microsoft.com/en-us/library/ms190766.aspx

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

 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 ) )

En fonction de vos commentaires, cette ligne doit être supprimée, ainsi seule la combinaison (UserName, Directory) est cochée: 

  AND R2.Roles = R.Roles