如何将单个值与具有逗号分隔值的所有行进行比较？ [英] how to compare single value with all rows which have comma seperated values?

问题描述

HI all



i有一个表，其中列存储逗号分隔值



roleid

1,2,4,6,7

3,4,6,7,8

2,3,4,5,6

1,2,3,5,6,7





这是分配给的角色ID用户。现在我想通过角色获得用户。



如果管理员选择角色ID 3那么它应该返回分配给角色ID 3的所有用户。我不想使用游标，因为它会影响查询的性能。我没有任何其他想法。有人可以帮助我吗？

HI all

i have one table where on column stores comma separated values

roleid
1,2,4,6,7
3,4,6,7,8
2,3,4,5,6
1,2,3,5,6,7


this are the role id which assigns to the user. now i want to get user by role.

if admin select role id 3 then it should return all the users which are assign to role id 3. i do not want to use cursor for this as it will affect the performance of the query. i don't have any other ideas. can some one please help me ?

推荐答案

假设您正在寻找具有roleid = 3的用户标识，请尝试CHARINDEX，如下所示：

Assuming you are looking for userids that have roleid=3, try CHARINDEX like this:

select userid from table1 where CHARINDEX('3', roleid ) > 0

但是，在单个字段中使用多个值是不好的做法，这是违反1NF原则的。相反，你应该有一个带有userid和roleid字段的表（两个复合主键），如下所示：

However, it is bad practice to have multiple values in a single field, that is against 1NF principle. Instead, you should have a table with userid and roleid fields (both composite primary key) like this:

userid       roleid
  1             1
  1             2
  1             4
  1             6
  1             7
  2             3
  2             4
...

您可以使用 LIKE 运算符，请参阅SQL LIKE运营商在w3schools.com [ ^ ] 。

You can use the LIKE operator, see "SQL LIKE Operator" at w3schools.com[^].

假设你的表格结构如下所示



UserId AssignedRoles

1 1,12， 3,4,5,6

2 6,3,23,1,4

5 2,11,19,12,34

Assuming your table structre is like below

UserId AssignedRoles
1 1,12,3,4,5,6
2 6,3,23,1,4
5 2,11,19,12,34

DECLARE @TABLE TABLE (UserId TINYINT, AssignedValues VARCHAR(500))
INSERT INTO @TABLE VALUES (1, '1,12,3,4,5,6')
,(2, '6,3,23,1,4' )
,(3, '2,11,19,12,34')

DECLARE @UserId INT = 3, @RoleId INT = 19


SELECT *
FROM @Table t
WHERE UserId = @UserId
AND @RoleId IN (SELECT Value
                FROM dbo.Split(AssignedValues, ',')
                )

这里dbo.Split是普通的分割函数。如果你没有在你的数据库中，它是谷歌搜索。

Here dbo.Split is the normal split function. If you dont have it in your db it is a google search away.

这篇关于如何将单个值与具有逗号分隔值的所有行进行比较？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！