具有逗号分隔列值的条件 [英] Where condition with Comma Seperated Column Values

问题描述

您好，



我在sql中有以下表格



Usermaster

用户名 | 组



组列包含用户所属的逗号分隔组。



另一个名为 FolderPermission 的表格

FolderName | 用户名 | GroupName



现在，当用户登录应用程序时，我想要FolderPermission表中的所有不同的foldername。



我尝试过使用IN查询但是当用户在一个组中并且我将组权限分配给文件夹时它没有显示它，因为它是一个逗号分隔值。

下面是我试过的查询

 选择  Distinct （FolderName）来自 FolderPermission 
 其中​​ UserName 喜欢 ' ％USERNAME_GOES_HERE％' 
 或 中的> GroupName （选择 GroupName 来自 UserMaster 其中用户名= '  USERNAME_GOES_HERE'）

例如：

Usermaster

用户名|集团

user1 | group1，group2，group3

user2 | group2，group4

user3 | group5



FolderMaster

FolderName |用户名| GroupName

Folder1 | NOUSERNAME | group1，group5

Folder2 | user1 |

Folder3 | user1，user2，user3 | NOGROUPNAME



结果

当user3登录到应用程序时，他/她可以看到Folder1和Folder3



在usermaster组列中有逗号分隔值和文件夹中的用户名和组名都有逗号分隔列。

解决方案

正如我在评论中所说，这是一个真正糟糕的数据库设计。不要将数据存储为以逗号分隔的字符串。



根据您目前的情况，以您想要的格式获取数据，请尝试使用

 DECLARE @结果VARCHAR（MAX）= null 
 
 SELECT @results = COALESCE（@ results +'，'，''）+ FolderName 
 FROM 
（
选择Distinct（FolderName） ）作为FolderPermission的FolderName 
其中UserName如'％user3％'
或GroupName in（从UserMaster中选择GroupName，其中Username ='user3'）
）src 
 SELECT @results 

请注意，我已经给了 Distinct（FolderName）一个列名，这是对上面原始查询的唯一更改。



或

a更好的想法是在你在任何应用程序调用它之前显示结果之前这样做。

问题是您的表设计存在缺陷：您在列中存储了多个值，以逗号分隔。这是一个问题，因为SQL不擅长deCSVing数据。



创建一个包含组的新表：

< pre lang =text> ID GroupName
1 Group1
2 Group2
3 Group3
...

更改UserMaster表以存储多个Group条目对于每个用户，由GroupID作为外键引用：

用户名组
 user1 1 
 user1 2 
 user1 3 
 user2 2 
 user2 4 
 user3 5 

（更好的是，有一个包含UserID和UserName的表，并在所有表中引用作为外键。）

然后您只需使用IN和JOIN语句获取数据。



似乎需要额外的工作量，但实际上并不困难，它可以节省大量的时间，精力和以后冗余重复的数据。

Hello,

I have tables in sql as per below

Usermaster
Username | Group

Group Column have comma seperated groups in which user belong too.

Another table with name FolderPermission
FolderName | Username | GroupName

Now I want all the distinct foldername from FolderPermission Table when a user login to the application.

I have tried using IN query for it but when a user is in a group and I assign group permission to folder it is not showing it as it is a comma seperated value.
below is the query I tried

Select Distinct(FolderName) from FolderPermission 
Where UserName like '%USERNAME_GOES_HERE%' 
or GroupName in (select GroupName from UserMaster where Username='USERNAME_GOES_HERE')

For Example:
Usermaster
Username | Group
user1 | group1,group2,group3
user2 | group2,group4
user3 | group5

FolderMaster
FolderName | Username | GroupName
Folder1 |NOUSERNAME | group1,group5
Folder2 |user1|
Folder3 |user1,user2,user3 |NOGROUPNAME

RESULT
When user3 login to application he/she can see Folder1 and Folder3

In usermaster group column has comma seperated values and in folderpermission username and groupname has comma seperated columns.

解决方案

As I said in my comment, this is a truly awful database design. Do not store data as comma separated strings.

Given what you currently have, to get the data in the format you want try this

DECLARE @results VARCHAR(MAX) = null

SELECT @results = COALESCE(@results+',' , '') + FolderName
FROM
(
	Select Distinct(FolderName) as FolderName from FolderPermission 
	Where UserName like '%user3%' 
	or GroupName in (select GroupName from UserMaster where Username='user3')
) src
SELECT @results

Note that I've given Distinct(FolderName) a column name which is the only change to your original query above.

OR
a far better idea would be to do this before you display the results in whatever application is calling this.

The problem is that your table design is flawed: you have stored multiple values in a column, separated by commas. This is a problem, as SQL is not good at "deCSVing" data.

Create a new table which contains the Groups:

ID    GroupName
1     Group1
2     Group2
3     Group3
...

Change your UserMaster table to store multiple Group entries for each user, referenced by the GroupID as a foreign key:

Username    Group
user1           1
user1           2
user1           3
user2           2
user2           4
user3           5

(Better still, have a table with UserID and UserName and reference that throughout all your tables as a foreign key.)
You then just fetch your data using IN and JOIN statements.

It seems like a lot of extra work, but it really isn't that difficult, and it saves you huge amounts of time, effort, and redundantly duplicated data later.

这篇关于具有逗号分隔列值的条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！