真的不清楚关于,id=Bid,然而你举的例子又不等啊。
1 kk 0
3 gg 1
4 ffg 16 df 4
1 kk 0
3 gg 1
4 ffg 16 df 4
解决方案 »
- 用户 'sa' 登录失败。原因: 未与信任 SQL Server 连接相关联
- 【求助】微软下载的SQL Server Management Studio Express为何没有中文版?
- 求一SQL写法!
- 一道英文题,高手进,谢谢帮忙
- 同表两个字段进行或筛选?
- 求一超级难的SQL 语句,来者有分
- 求两结构相同表的update操作同步语句,只知道表名,不知道具体列名
- 怎么得到执行一条SQL语句或一个存储过程 CPU的占用率?
- my_storeproc返回一个结果集,能否对这个结果集进行子查询?
- 关于时间的比较
- 现有输入密码和用户名,我该用什么SQL来检验其是不是SQL SEVER的用户呢?能否知道他的权限呢?
- 求SQL語句?
from tt
where id=bid and id=1
下面是用存储过程的方法:create PROCEDURE GetAll (@ParentID int) as
SET NOCOUNT ON
DECLARE @level int
CREATE TABLE #stack (item int, level int)
INSERT INTO #stack VALUES (@ParentID, 1)
CREATE TABLE #stackEx (item int, level int)
INSERT INTO #stackEx VALUES (@ParentID, 1)
SELECT @level = 1
WHILE @level > 0
BEGIN
IF EXISTS (SELECT * FROM #stack WHERE level = @level)
BEGIN
SELECT @ParentID = item
FROM #stack
WHERE level = @level
DELETE FROM #stack
WHERE level = @level
AND item = @ParentID
INSERT #stack
SELECT ID, @level + 1
FROM tablename
WHERE parentID = @ParentID
INSERT #stackEx
SELECT ID, @level + 1
FROM tablename
WHERE parentID = @ParentID
IF @@ROWCOUNT > 0
SELECT @level = @level + 1
END
ELSE
SELECT @level = @level - 1
END
Select *
from tablename
where id in ( select item from #StackEx)
谢谢各位!!!
参考:create table #(Parent_id int,item_id int )
insert into #(Parent_id,item_id) values(1,0)
insert into #(Parent_id,item_id) values(2,1)
insert into #(Parent_id,item_id) values(3,1)
insert into #(Parent_id,item_id) values(4,2)
insert into #(Parent_id,item_id) values(5,2)
insert into #(Parent_id,item_id) values(6,3)
insert into #(Parent_id,item_id) values(7,2)
insert into #(Parent_id,item_id) values(8,3)
insert into #(Parent_id,item_id) values(9,7)
insert into #(Parent_id,item_id) values(10,9)set nocount on
declare @ int,@i int
set @=2
declare @t table(Parent_id int,item_id int,iv int)
declare @t1 table(Parent_id int,item_id int)
insert into @t1
select * from # where item_id=1
set @i=1
while exists(select TOP 1 * from @t1)
begin
insert into @t(Parent_id ,item_id ,iv )
select *,@i from @t1
delete from @t1
insert into @t1
select * from # where item_id in (select Parent_id from @t where iv=@i)
set @i=@i+1
end
select * from @t
drop table #
set nocount off
SELECT * FroM tt start with id='1' connect by id =prior bid;
不知道MS里边有没有类似的语句,关注!