我的系统里有三个表,分别为Account(用户信息表),Account表有Account_ID,Account_Name等字段。Role(角色信息表),Role有Role_ID,Role_Name等字段。Account_In_Role(用户所属角色表),Account_In_Role有Account_ID和Role_ID字段,是关联用户信息和所属角色的,一个用户可能有一个或多个角色名称。我想写一个查询语句,这个语句能查询出所有的用户信息和用户所属的角色信息(一个用户可能有一个或多个角色名称),把用户所属的多个角色信息AS成一个自定义的字段。然后把查询出来的角色信息绑定到datagrid中去,datagrid中有个字段显示用户所属角色信息,形式如:软件工程师,部门经理.这个查询语句应该怎么写??请赐教
asdeclare @roles nvarchar(4000)
set @roles =''
select @roles = @roles + a.Role_Name +","
from Role a
inner join Account_In_Role b on a.accountid=b.accountid
where b.accountid=@accountid return roles
然后:
select account_name as Name, getRoles(accountid) as Role
from Account
我用你所写的语句,SQL提示说function附近有错误,请问是什么原因?
FROM rb_Users INNER JOIN
rb_UserRoles ON rb_Users.UserID = rb_UserRoles.UserID INNER JOIN
rb_Roles ON rb_UserRoles.RoleID = rb_Roles.RoleIDrb_Users,用户信息表
rb_Roles,角色定义表
rb_UserRoles,用户角色信息表
是“,”分开的,而不是分几行
asdeclare @roles nvarchar(4000)
set @roles =''
select @roles = @roles + a.Role_Name +','
from Role a
inner join Account_In_Role b on a.accountid=b.accountid
where b.accountid=@accountid return roles
然后:
select account_name as Name, dbo.getRoles(accountid) as Role
from Account
asBegindeclare @roles nvarchar(4000)
set @roles =''
select @roles = @roles + a.Role_Name +','
from Role a
inner join Account_In_Role b on a.accountid=b.accountid
where b.accountid=@accountid return roles End然后:
select account_name as Name, dbo.getRoles(accountid) as Role
from Account
@Account_Type Int
AScreate function dbo.getRoles(@accountid int)returns nvarchar(4000)
asBegindeclare @roles nvarchar(4000)
set @roles =''
select @roles = @roles + a.Role_Name +','
from Role a
inner join Account_In_Role b on a.Account_ID=b.Account_ID
where b.Account_ID=@accountid return roles EndSELECT A.*,
(Select Department_name From Department Where
Department_ID=B.Department_ID)AS Department_Name,
B.Department_ID,
A.LeaveDate As LD,
dbo.getRoles(C.Account_ID) As Role_Names
From Account A, Account_In_Department B,Account_In_Role C
Where Dimission =@Account_Type
And A.Account_ID = B.Account_ID
And A.Account_ID=C.Account_ID
Order By Department_Name , A.RealName Asc
GO这是我的存储过程,但是老是提示说function附近有语法错误,@account_id没有申明