22,206
社区成员
发帖
与我相关
我的任务
分享
tb_tree(id int,treecode varchar)
/*
数据如:
id treecode
10000 0101
10010 010102
10013 010103
10022 01010204
20011 0102
20071 010203
*/
CREATE TABLE t1
(
id INT,
treecode VARCHAR(20)
)
INSERT INTO t1
SELECT 10000, '0101' UNION ALL
SELECT 10010, '010102' UNION ALL
SELECT 10013, '010103' UNION ALL
SELECT 10022, '01010204' UNION ALL
SELECT 20011, '0102' UNION ALL
SELECT 20071, '010203'
SELECT * FROM t1
SELECT a.* FROM t1 AS a INNER JOIN t1 AS b ON CHARINDEX(b.treecode,a.treecode)>0 AND b.id=10000
-------------------------------
id treecode
10000 0101
10010 010102
10013 010103
10022 01010204
select * from tb_tree
where treecode like (select treecode from tb_tree where id=2)+'%'
declare @id varchar(20)
set @id='10000'
select * from tb_tree a,
(select * from tb_tree where id=@id) b
where a.treecode like b.treecode+'%'