我想在我的表中插入数据，但问题是返回多个值的用户ID coloumn。所以在这种情况下我能做些什么。 我尝试过的事情：
I want to insert data into my table but the problem is the user id coloumn which is returning multiple values. so what can i do in that case. What I have tried:
Insert into UserAccess(ClientId,UserId,ParentId,Name,ToolTip,NavigateUrl,UserRole,IsParent,Menu_Order,Status,MenuId) SELECT @ClientId ,(select USR_ID from UserDetail WHERE Client_Id=@ClientId AND (',' + USR_Role + ',') LIKE '%,2,%' AND Status=1 AND Emp_Id!=0), ParentId,Name,ToolTip,NavigateUrl,UserRole,IsParent,Menu_Order,Status,Id from UserMenu WHERE UserRole=2 AND Status=1问题是usr_Id coloumn因为起初我们只选择userid = 2的前1位用户，但现在我们的要求发生了变化，因此我们必须为每个用户ID = 2的用户插入数据。我怎样才能实现这一目标？
Problem is the usr_Id coloumn because at first we only select the top 1 user where userid =2 but now our requirments changes so we have to insert the data for every user with userid=2. How can i acheive this?
推荐答案您没有提供3天前请求的其他信息，但我会尝试演示解决方案。 我用这个脚本创建了一些演示表 You haven't provided the additional information that was requested 3 days ago but I will attempt to demonstrate the solution. I created some demo tables with this script create table #user ( id int identity(1,1), dataitem varchar(10), [status] int ) insert into #user (dataitem, [status]) values ('User1',1), ('User2',1), ('User3',1) create table #usermenu ( id int, menudetail varchar(10), [user_id] int ) insert into #usermenu (id, menudetail, [user_id]) values (1, 'Menu 1', 1), (1, 'Menu 1', 2), (1, 'Menu 1', 3)
所以现在我有3个用户，都处于状态1，并且都可以访问菜单1.如果我尝试这样的查询样式
So now I have 3 users, all at status 1 and all have access to Menu 1. If I attempt your style of query like this
SELECT (select id from #user where status = 1), id, menudetail from #usermenu我收到错误
Q uote：Msg 512，Level 16，State 1，Line 25 子查询返回的值超过1。当子查询遵循=，！=，<，< =，>，> =或子查询用作表达式时，不允许这样做。
Msg 512, Level 16, State 1, Line 25 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
您需要能够以某种方式加入表。在我的例子中，我将使用
You need to be able to join the tables in some way. In my example I would use
select a.id, b.id, menudetail from #usermenu B INNER JOIN #user A on A.ID=B.[user_id]您的代码有几个方面与我有关。 1.代码段 LIKE'％，2，％'表示您将用户角色存储在以逗号分隔的字符串中。这是非常糟糕的设计。相反，您应该将这些角色存储在另一个表中 - 这样您就不必混淆分离字符串或搜索它以查看哪些角色可用 - 它在其他地方进行了详细讨论。例如。
There are a couple of aspects of your code that concern me. 1. The snippet LIKE '%,2,%' implies that you are storing user roles in a comma-separated string. This is very bad design. Instead you should store those roles in another table - that way you don't have to mess around with separating the string or searching it to see which roles are available - it's discussed at length elsewhere. E.g.
create table #UserRoles ( [User_Id] int, roleNo int )我可能不会打扰单独的角色表，因为它会使标准化太过分了。 2.你有一个名为UserMenu的表，但它似乎包含所有的菜单信息。我会有一个表菜单，其中包含所有数据和一个单独的表，只是将User_Ids链接到他们有权访问的菜单。
I probably wouldn't bother having a separate roles table as that would be taking normalisation a tad too far. 2. You have a table called UserMenu but it appears to have all of the Menu information in it. I would have a table Menu that contains all that data and a separate table that just links User_Ids to the Menu's to which they have access.
更多推荐
如何在不应用循环的情况下将数据插入到表中。？
发布评论