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步。