我有一个用户表，每个用户可能有很多角色，每个角色有多个权限我如何在一个查询中获得所有记录？ [英] I have a user table and each user may have many roles and each role have many permission how I get all record in one query?

问题描述

我有一个用户表，每个用户可能有很多角色，每个角色有很多权限，我如何在一个查询中获得所有记录？

out out like this



i需要完整的用户信息他们拥有多少角色以及他们拥有多少权限。



我尝试了什么：



选择user.username，user.officeID来自用户加入

userRole on user.UserID = UserRole.UserID join

UserRole.RoleID上的角色= Role.ID在Role.ID上加入RolePermission = RolePermission.RoleID加入RolePermission.ID上的权限= PermissionID

解决方案

不清楚你期望什么样的输出，但我认为你需要研究不同的连接类型



参见 T-SQL加入类型|来自SQL Server Pro的T-SQL内容



此外，您还要清楚脚本中使用正确的表字段名称的字段名称是常见的。



例如

 从 table1中选择 a.id，b.UID，x，y   left   join  table2 b  on  a.id = b.id  left   join  table3 c  on  b.UID = c.IUD 

i have a user table and each user may have many roles and each role have many permission how i get all record in one query?
out put like this

i need complete user information how many roles they have and how many permission they have.

What I have tried:

select user.username,user.officeID from user join
userRole on user.UserID=UserRole.UserID join
Role on UserRole.RoleID=Role.ID join RolePermission on Role.ID=RolePermission.RoleID join Permission on RolePermission.ID=PermissionID

解决方案

Its unclear what sort of output you are expecting but I think you need to investigate the different join types

See T-SQL Join Types | T-SQL content from SQL Server Pro

Also you get to be clear on your script where field name are common that the correct table field name is used.

eg

Select a.id, b.UID, x,y from table1 a left join table2 b on a.id = b.id left join table3 c on b.UID = c.IUD

这篇关于我有一个用户表，每个用户可能有很多角色，每个角色有多个权限我如何在一个查询中获得所有记录？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！