看似简单，却弄的我头疼了好久的sql。求高手指教

Question

create table #Tbo_Menu
(
id int identity(1,1),
title nvarchar(50),
postUrl nvarchar(255),
nodeId int,
parentId int
)
go
insert into #tbo_menu values('专业管理','#',1001,1001)
insert into #tbo_menu values('查看现有专业','#',1005,1001)
insert into #tbo_menu values('添加专业','#',1002,1001)
insert into #tbo_menu values('修改专业','#',1003,1001)
insert into #tbo_menu values('删除专业','#',1004,1001)
insert into #tbo_menu values('设定专业的学分','#',1006,1001)
go
create table #tbo_order
(
id int identity(1,1),
haveMenu nvarchar(500),
thisLevel int
)
go
insert into #tbo_order values('1001,1002,1003,1004,1005',1)
go
需要的效果就是select * from #tbo_menu where nodeid in (1001,1002,1003,1004,1005)
1001,1002,1003,1004,1005这个东西需要从#tbo_order这个表中取得。求实力高手给予解答

Answer 1

create table #Tbo_Menu
(
id int identity(1,1),
title nvarchar(50),
postUrl nvarchar(255),
nodeId int,
parentId int
)
go
insert into #tbo_menu values('专业管理','#',1001,1001)
insert into #tbo_menu values('查看现有专业','#',1005,1001)
insert into #tbo_menu values('添加专业','#',1002,1001)
insert into #tbo_menu values('修改专业','#',1003,1001)
insert into #tbo_menu values('删除专业','#',1004,1001)
insert into #tbo_menu values('设定专业的学分','#',1006,1001)
go
create table #tbo_order
(
id int identity(1,1),
haveMenu nvarchar(500),
thisLevel int
)
go
insert into #tbo_order values('1001,1002,1003,1004,1005',1)
goselect * from  #Tbo_Menu a where exists (select 1 from #tbo_order b where CHARINDEX(','+ltrim(a. nodeid)+',',','+b.haveMenu+',')>0)
drop table #Tbo_Menu, #tbo_order/*id          title                                              postUrl                                                                                                                                                                                                                                                         nodeId      parentId
----------- -------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- -----------
1           专业管理                                               #                                                                                                                                                                                                                                                               1001        1001
2           查看现有专业                                             #                                                                                                                                                                                                                                                               1005        1001
3           添加专业                                               #                                                                                                                                                                                                                                                               1002        1001
4           修改专业                                               #                                                                                                                                                                                                                                                               1003        1001
5           删除专业                                               #                                                                                                                                                                                                                                                               1004        1001
7           专业管理                                               #                                                                                                                                                                                                                                                               1001        1001
8           查看现有专业                                             #                                                                                                                                                                                                                                                               1005        1001
9           添加专业                                               #                                                                                                                                                                                                                                                               1002        1001
10          修改专业                                               #                                                                                                                                                                                                                                                               1003        1001
11          删除专业                                               #                                                                                                                                                                                                                                                               1004        1001(10 行受影响)
*/

Answer 2

select * from #tbo_menu m,  #tbo_order n where charindex(','+cast(nodeid as varchar)+',',','+n.haveMenu+',') > 0

Answer 3

--有点问题 修改下
create table #Tbo_Menu
(
id int identity(1,1),
title nvarchar(50),
postUrl nvarchar(255),
nodeId int,
parentId int
)
go
insert into #tbo_menu values('专业管理','#',1001,1001)
insert into #tbo_menu values('查看现有专业','#',1005,1001)
insert into #tbo_menu values('添加专业','#',1002,1001)
insert into #tbo_menu values('修改专业','#',1003,1001)
insert into #tbo_menu values('删除专业','#',1004,1001)
insert into #tbo_menu values('设定专业的学分','#',1006,1001)
go
create table #tbo_order
(
id int identity(1,1),
haveMenu nvarchar(500),
thisLevel int
)
go
insert into #tbo_order values('1001,1002,1003,1004,1005',1)
goselect * from #tbo_menu a,  #tbo_order b where charindex(','+ltrim(nodeid)+',',','+b.haveMenu+',') > 0drop table #Tbo_Menu, #tbo_order/*id          title                                              postUrl                                                                                                                                                                                                                                                         nodeId      parentId    id          haveMenu                                                                                                                                                                                                                                                         thisLevel
----------- -------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -----------
1           专业管理                                               #                                                                                                                                                                                                                                                               1001        1001        1           1001,1002,1003,1004,1005                                                                                                                                                                                                                                         1
2           查看现有专业                                             #                                                                                                                                                                                                                                                               1005        1001        1           1001,1002,1003,1004,1005                                                                                                                                                                                                                                         1
3           添加专业                                               #                                                                                                                                                                                                                                                               1002        1001        1           1001,1002,1003,1004,1005                                                                                                                                                                                                                                         1
4           修改专业                                               #                                                                                                                                                                                                                                                               1003        1001        1           1001,1002,1003,1004,1005                                                                                                                                                                                                                                         1
5           删除专业                                               #                                                                                                                                                                                                                                                               1004        1001        1           1001,1002,1003,1004,1005                                                                                                                                                                                                                                         1(5 行受影响)*/

Answer 4

create table #Tbo_Menu
(
id int identity(1,1),
title nvarchar(50),
postUrl nvarchar(255),
nodeId int,
parentId int
)
go
insert into #tbo_menu values('专业管理','#',1001,1001)
insert into #tbo_menu values('查看现有专业','#',1005,1001)
insert into #tbo_menu values('添加专业','#',1002,1001)
insert into #tbo_menu values('修改专业','#',1003,1001)
insert into #tbo_menu values('删除专业','#',1004,1001)
insert into #tbo_menu values('设定专业的学分','#',1006,1001)
go
create table #tbo_order
(
id int identity(1,1),
haveMenu nvarchar(500),
thisLevel int
)
go
insert into #tbo_order values('1001,1002,1003,1004,1005',1)
go
declare @sql nvarchar(max)
select @sql='select * from ##t where nodeid in('+haveMenu+')' from #tbo_order where thisLevel=1
select * into ##t from #tbo_menu
exec(@sql)
/*
id          title                                              postUrl                                                                                                                                                                                                                                                         nodeId      parentId
----------- -------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- -----------
1           专业管理                                               #                                                                                                                                                                                                                                                               1001        1001
2           查看现有专业                                             #                                                                                                                                                                                                                                                               1005        1001
3           添加专业                                               #                                                                                                                                                                                                                                                               1002        1001
4           修改专业                                               #                                                                                                                                                                                                                                                               1003        1001
5           删除专业                                               #                                                                                                                                                                                                                                                               1004        1001(5 行受影响)*/
go
drop table #tbo_order,#tbo_menu,##t

Answer 5

select * from #tbo_menu where nodeid in (
select haveMenu from #tbo_order
)

Answer 6

额呀。 我纠结了。。 给分给错了。  geniuswjt应该得不到分的。。 真不好意思额，3楼