各位好:
我的问题是这个样子滴:
组织表organ:原始表:
id organ_name p_id lay struct
1 company -1
2 sale 1
3 sale_a 2
4 sale_b 2
5 et 1
6 et_a 5
希望的运行存储过程后的结果:
id organ_name p_id lay struct
1 company -1 1 1
2 sale 1 2 1.1
3 sale_a 2 3 1.1.1
4 sale_b 2 3 1.1.2
5 et 1 2 1.2
6 et_a 5 3 1.2.1我的代码是这个样子的:
WITH dataSource(id,organ_name,p_id,lay) AS
(
SELECT o.id,o.organ_name,o.p_id,1 AS lay
FROM organ o
WHERE p_id=-1
UNION ALL
SELECT d.id,d.organ_name,d.p_id,lay+1
FROM organ AS d
INNER JOIN dataSource a
ON d.p_id=a.id
)
SELECT * FROM dataSource;运行结果是:
id organ_name p_id lay
1 company -1 1
2 sale 1 2
3 sale_a 2 3
4 sale_b 2 3
5 et 1 2
6 et_a 5 3
问题:我还想把struct那种1.1 1.1.1 1.1.2也生成出来,但是想不到办法,有谁能提供个思路吗?
我的问题是这个样子滴:
组织表organ:原始表:
id organ_name p_id lay struct
1 company -1
2 sale 1
3 sale_a 2
4 sale_b 2
5 et 1
6 et_a 5
希望的运行存储过程后的结果:
id organ_name p_id lay struct
1 company -1 1 1
2 sale 1 2 1.1
3 sale_a 2 3 1.1.1
4 sale_b 2 3 1.1.2
5 et 1 2 1.2
6 et_a 5 3 1.2.1我的代码是这个样子的:
WITH dataSource(id,organ_name,p_id,lay) AS
(
SELECT o.id,o.organ_name,o.p_id,1 AS lay
FROM organ o
WHERE p_id=-1
UNION ALL
SELECT d.id,d.organ_name,d.p_id,lay+1
FROM organ AS d
INNER JOIN dataSource a
ON d.p_id=a.id
)
SELECT * FROM dataSource;运行结果是:
id organ_name p_id lay
1 company -1 1
2 sale 1 2
3 sale_a 2 3
4 sale_b 2 3
5 et 1 2
6 et_a 5 3
问题:我还想把struct那种1.1 1.1.1 1.1.2也生成出来,但是想不到办法,有谁能提供个思路吗?
--> 生成测试数据表:tb
IF NOT OBJECT_ID('[tb]') IS NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb](GUID INT IDENTITY,[col1] NVARCHAR(10),[col2] NVARCHAR(20))
INSERT [tb]
SELECT N'A','01' UNION ALL
SELECT N'B','01.01' UNION ALL
SELECT N'C','01.01.01' UNION ALL
SELECT N'F','01.01.01.01' UNION ALL
SELECT N'E','01.01.01.02' UNION ALL
SELECT N'D','01.01.01.03' UNION ALL
SELECT N'O','02' UNION ALL
SELECT N'P','02.01' UNION ALL
SELECT N'Q','02.01.01'
GO
--SELECT * FROM [tb]-->SQL查询如下:---另一种方法
;WITH T AS
(
SELECT *,PATH=CAST([COL1] AS VARCHAR(1000)) FROM TB A
WHERE NOT EXISTS(
SELECT 1 FROM TB
WHERE A.COL2 LIKE COL2+'%'
AND LEN(A.COL2)>LEN(COL2))
UNION ALL
SELECT A.*,CAST(PATH+'-->'+A.COL1 AS VARCHAR(1000))
FROM TB A
JOIN T B
ON A.COL2 LIKE B.COL2+'%' AND LEN(A.COL2)-3=LEN(B.COL2)
)SELECT * FROM T ORDER BY LEFT(COL2,2)/*GUID COL1 COL2 PATH----------- ---------- -------------------- --------------------1 A 01 A2 B 01.01 A-->B3 C 01.01.01 A-->B-->C4 F 01.01.01.01 A-->B-->C-->F5 E 01.01.01.02 A-->B-->C-->E6 D 01.01.01.03 A-->B-->C-->D7 O 02 O8 P 02.01 O-->P9 Q 02.01.01 O-->P-->Q
(9 行受影响)*/
;WITH T AS(
SELECT *,CAST(COL1 AS VARCHAR(1000)) AS PATH
FROM TB
WHERE COL2 NOT LIKE '%.%'
UNION ALL
SELECT A.*,CAST(B.PATH+'-->'+A.COL1 AS VARCHAR(1000))
FROM TB A,T B
WHERE A.COL2 LIKE B.COL2+'.[01-99][01-99]'
)SELECT * FROM T
ORDER BY LEFT(COL2,2)/*GUID COL1 COL2 PATH----------- ---------- -------------------- --------------------1 A 01 A2 B 01.01 A-->B3 C 01.01.01 A-->B-->C4 F 01.01.01.01 A-->B-->C-->F5 E 01.01.01.02 A-->B-->C-->E6 D 01.01.01.03 A-->B-->C-->D7 O 02 O8 P 02.01 O-->P9 Q 02.01.01 O-->P-->Q (9 行受影响)*/
原始表:
id organ_name p_id lay struct
1 company -1
2 sale 1
3 sale_a 2
4 sale_b 2
5 et 1
6 et_a 5
希望的运行存储过程后的结果:
id organ_name p_id lay struct
1 company -1 1 1
2 sale 1 2 1.1
3 sale_a 2 3 1.1.1
4 sale_b 2 3 1.1.2
5 et 1 2 1.2
6 et_a 5 3 1.2.1
(
SELECT o.id,o.organ_name,o.p_id,CAST('1' AS NVARCHAR(200)) AS lay
FROM organ o
WHERE p_id=-1
UNION ALL
SELECT d.id,d.organ_name,d.p_id,CAST(a.lay+'.'+RTRIM(lay+1) AS NVARCHAR(200))
FROM organ AS d
INNER JOIN dataSource a
ON d.p_id=a.id
)
SELECT * FROM dataSource;
原始表:
id organ_name p_id lay struct
1 company -1
2 sale 1
3 sale_a 2
4 sale_b 2
5 et 1
6 et_a 5
希望的运行存储过程后的结果:
id organ_name p_id lay struct
1 company -1 1 1
2 sale 1 2 1.1
3 sale_a 2 3 1.1.1
4 sale_b 2 3 1.1.2
5 et 1 2 1.2
6 et_a 5 3 1.2.1
Conversion failed when converting the nvarchar value '1.2' to data type int.
是数据类型转换的时候报错了
(
SELECT o.id,o.organ_name,o.p_id,1 AS lay,cast(o.id as varchar(10)) 'strcut'
FROM organ o
WHERE p_id=-1
UNION ALL
SELECT d.id,d.organ_name,d.p_id,lay+1,cast(o.id+'.'+d.id as varchar(10)) 'strcut'
FROM organ AS d
INNER JOIN dataSource a
ON d.p_id=a.id
)
SELECT * FROM dataSource;
你可以借鑒一下。
WITH dataSource(id,organ_name,p_id,lay) AS
(
SELECT o.id,o.organ_name,o.p_id,CAST('1' AS NVARCHAR(200)) AS lay
FROM organ o
WHERE p_id=-1
UNION ALL
SELECT d.id,d.organ_name,d.p_id,CAST(ltrim(a.lay)+'.'+RTRIM(lay+1) AS NVARCHAR(200))-- 这里加个ltrim()
FROM organ AS d
INNER JOIN dataSource a
ON d.p_id=a.id
)
SELECT * FROM dataSource;
试验了一下,还是把lay+1当成了int型
yeisman 我试了下,
The multi-part identifier "o.id" could not be bound.
看来数据类型的问题