求传一参数,返回数据的问题?
求传一参数,返回数据的问题?
表结构如下:
表A
boardid parentid title
4 0 a
5 4 b
6 4 c
....
表B
id topic boardid
1 aa 4
2 bb 5
3 cc 6
.......
实现:
传一参数(表A中的boardid)
若boardid=4 下有子数据即(parentid=4)
列出表B中的boarid=4 =5 =6数据
存储过程怎么写.
[解决办法]
select
*
from
表B
where
boardid in(select boardid from 表A where paretnid=4 union select 4)
[解决办法]
select * from 表B
where boarid=4 or boardid in( select boardid from 表A where paretnid=4)
是没有问题的，
create table a(
boardid int,parentid int,title varchar(10)
)
insert into a select 4, 0, 'a '
union select 5, 4, 'b '
union select 6, 4, 'c '
create table b(
[id] int identity(1,1), topic varchar(10), boardid int
)
insert into b select 'aa ', 4
union select 'bb ', 5
union select 'cc ', 6
select * from b where boardid=4 or boardid in(select boardid from a where parentid=4)
drop table a
drop table b
--结果
1aa4
2bb5
3cc6
[解决办法]
create table 表A(boardid int, parentid int,title varchar(10))
insert into 表A select 4, 0, 'a '
union all select 5, 4, 'b '
union all select 6, 4, 'c '
union all select 7, 5, 'c '
union all select 8, 6, 'c '
union all select 9, 7, 'c '
union all select 10, 0, 'c '
create table 表B(id int, topic varchar(10), boardid int)
insert into 表B select 1, 'aa ', 4
union all select 2, 'bb ', 5
union all select 3, 'cc ', 6
union all select 4, 'cc ', 7
union all select 5, 'cc ', 8
union all select 6, 'cc ', 9
union all select 7, 'cc ', 10
union all select 8, 'cc ', 11
union all select 9, 'cc ', 12
select boardid into # from 表A where parentid=4 or boardid=4
while((select count(1) from 表A where parentid in(select * from #) or boardid in(select * from #))>
(select count(1) from(select boardid from # group by boardid)a))
insert into # select boardid from 表A where parentid in(select * from #) or boardid in(select * from #)
select * from 表B where boardid in(select boardid from # group by boardid)
