求实现以下结果的sql

[lxw2100]

nodeid
00026
0002600150
000260015000001
000260015000002
000260015000003
00027


nodeid haschild
00026 1
0002600150 1
000260015000001 0
000260015000002 0
000260015000003 0
00027 0

有上面表的数据查询得到下面的结果：该数据有下层数据时haschild为1，否则为0；总共有三层！
请教该sql如何实现？

[htl258_Tony]

--> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
	DROP TABLE [tb]
GO
CREATE TABLE [tb] ([nodeid] [nvarchar](20))
INSERT INTO [tb]
SELECT '00026' UNION ALL
SELECT '0002600150' UNION ALL
SELECT '000260015000001' UNION ALL
SELECT '000260015000002' UNION ALL
SELECT '000260015000003' UNION ALL
SELECT '00027'


-->SQL查询如下:
SELECT *, haschild = CASE 
                          WHEN EXISTS(
                                   SELECT 1 
                                   FROM tb
                                   WHERE nodeid LIKE t.nodeid+'%'
                                   	AND LEN(nodeid)>LEN(t.nodeid)
                               ) THEN 1
                          ELSE 0
                     END 
FROM [tb] t
/*
nodeid               haschild
-------------------- -----------
00026                1
0002600150           1
000260015000001      0
000260015000002      0
000260015000003      0
00027                0

(6 行受影响)
*/

[ShenLiang2025]

--1)表和结构

IF OBJECT_ID('[tb]') IS NOT NULL
    DROP TABLE [tb]
GO
CREATE TABLE [tb] ([nodeid] [nvarchar](20))
INSERT INTO [tb]
SELECT '00026' UNION ALL
SELECT '0002600150' UNION ALL
SELECT '000260015000001' UNION ALL
SELECT '000260015000002' UNION ALL
SELECT '000260015000003' UNION ALL
SELECT '00027'

--SELECT * FROM tb

--2)In的写法

SELECT nodeid,haschild = CASE WHEN  nodeid IN
(SELECT nodeid
FROM tb a
WHERE EXISTS
(SELECT nodeid FROM tb b
WHERE CHARINDEX(a.nodeid,b.nodeid) > 0  AND a.nodeid <> b.nodeid
) )THEN 1 ELSE 0 END
FROM tb

--3)结果
nodeid               haschild
-------------------- -----------
00026                1
0002600150           1
000260015000001      0
000260015000002      0
000260015000003      0
00027                0

(6 row(s) affected)

[lxw2100]

次问题已解决！谢谢以上诸位关注！