SQL筛选器根据多个列值进行搜索

时间:2022-04-14 01:37:18

I am dealing with one table(3+ million rows,SQL Server) I need to filter results according to the two columns below:

我正在处理一个表(300多万行,SQL Server)我需要根据以下两列过滤结果:

<code>
...FromID| ToID  |Column5|....
...1001    2001
...1002    2020 
...1003    5000    
...1001    3000 
...2001    1001 
</code>

Now User1 can access records with FromID or ToId 1001. FromID|ToID 1001|2001 1001|3000 2001|1001

现在,User1可以使用FromID或ToId 1001访问记录。来自ID | ToID 1001 | 2001 1001 | 3000 2001 | 1001

User2 can access records with FromID or ToID 1002,1003,3000 FromID|ToID 1002|2020 1003|5000 1001|3000

User2可以使用FromID或ToID访问记录1002,1003,3000 FromID | ToID 1002 | 2020 1003 | 5000 1001 | 3000

What is the most efficient way to do this ? Do i need to create a view for each user ?(this is working on enterprise,user count will be max 100 ) Thanks.

最有效的方法是什么?我是否需要为每个用户创建一个视图?(这适用于企业,用户数量最多为100)谢谢。

PS. My very first question. O.o

PS。我的第一个问题。 O.o

1 个解决方案

#1


3  

Your access criteria seem to be fairly arbitrary. User1 gets 1001, user2 gets 1002, 1003, and 3000, and I assume users 3 through 99 have arbitrary access as well. In that case, I recommend that you create a table, call it useraccess for this example:

您的访问标准似乎相当随意。 User1获得1001,user2获得1002,1003和3000,我认为用户3到99也具有任意访问权限。在这种情况下,我建议您创建一个表,在此示例中将其命名为useraccess:

user |accessID
---------------
user1|1001
user2|1002
user2|1003
user2|3000
...  |...

Now when you want to know what rows a user has, you can do this:

现在,当您想知道用户拥有哪些行时,您可以执行以下操作:

SELECT t.FromID, t.ToID, [[other columns you care about]]
FROM yourtable t
JOIN useraccess a ON t.FromID = a.accessID OR t.ToID = a.accessID
WHERE a.user = 'user2'

You can either run that query dynamically or you can create a view based on it. The usual tradeoffs between views and direct queries will apply as usual.

您可以动态运行该查询,也可以基于它创建视图。视图和直接查询之间的通常权衡将照常应用。

Edit: I just saw your note that you already have a UserRights table, so you already have step 1 completed.

编辑:我刚看到你已经有一个UserRights表的注释,所以你已经完成了第1步。

#1


3  

Your access criteria seem to be fairly arbitrary. User1 gets 1001, user2 gets 1002, 1003, and 3000, and I assume users 3 through 99 have arbitrary access as well. In that case, I recommend that you create a table, call it useraccess for this example:

您的访问标准似乎相当随意。 User1获得1001,user2获得1002,1003和3000,我认为用户3到99也具有任意访问权限。在这种情况下,我建议您创建一个表,在此示例中将其命名为useraccess:

user |accessID
---------------
user1|1001
user2|1002
user2|1003
user2|3000
...  |...

Now when you want to know what rows a user has, you can do this:

现在,当您想知道用户拥有哪些行时,您可以执行以下操作:

SELECT t.FromID, t.ToID, [[other columns you care about]]
FROM yourtable t
JOIN useraccess a ON t.FromID = a.accessID OR t.ToID = a.accessID
WHERE a.user = 'user2'

You can either run that query dynamically or you can create a view based on it. The usual tradeoffs between views and direct queries will apply as usual.

您可以动态运行该查询,也可以基于它创建视图。视图和直接查询之间的通常权衡将照常应用。

Edit: I just saw your note that you already have a UserRights table, so you already have step 1 completed.

编辑:我刚看到你已经有一个UserRights表的注释,所以你已经完成了第1步。