34,590
社区成员
发帖
与我相关
我的任务
分享
select productid,shugaopath=stuff((select '/'+shugaopath from shugao where productid=a.productid for xml path('')),1,1,''),
shugaopath2=stuff((select '/'+shugaopath2 from shugao where productid=a.productid for xml path('')),1,1,''),
shugaopath3=stuff((select '/'+shugaopath3 from shugao where productid=a.productid for xml path('')),1,1,'')
from shugao a
group by productid
productid shugaopath shugaopath2 shugaopath3
2 c/d/n m/n/n t/a/b
3 a/d/n m/t/n t/a/n
莫非2K不支持?!根据新数据,测试并无问题.
--1)<b>修改之</b>表结构
DROP TABLE for_csdn
Go
CREATE TABLE for_csdn
(ProName varchar(30),
proid int,
path1 varchar(30),
path2 varchar(30),
path3 varchar(30)
)
INSERT INTO for_csdn VALUES('1' ,2 ,'4' ,'5' ,'7')
INSERT INTO for_csdn VALUES('1' ,2 ,'3' ,'6' ,'8')
INSERT INTO for_csdn VALUES('1' ,2 ,'6' ,'6' ,'9')
INSERT INTO for_csdn VALUES('2',3 ,'8' ,'5' ,'7')
INSERT INTO for_csdn VALUES('2' ,3 ,'3' ,'7' ,'8')
INSERT INTO for_csdn VALUES('2' ,3, '6' ,'6' ,'6')
INSERT INTO for_csdn VALUES('3' ,4, '4' ,'9' ,'1')
INSERT INTO for_csdn VALUES('3' ,4, '3' ,'0' ,'5')
INSERT INTO for_csdn VALUES('4' ,5, '6' ,'s' ,'7')
INSERT INTO for_csdn VALUES('4' ,6, '6' ,'f' ,'t')
INSERT INTO for_csdn VALUES('4' ,6, '6' ,'t' ,'f')
--2)查询
SELECT ProName,
proid = CASE WHEN proid + '/' = replicate(SUBSTRING(proid,1,CHARINDEX('/',proid)),
LEN (proid) - LEN(REPLACE(proid,'/','')) +1)
THEN SUBSTRING(proid,1,CHARINDEX('/',proid)-1) ELSE
proid END,
path1 = CASE WHEN path1 + '/' = replicate(SUBSTRING(path1,1,CHARINDEX('/',path1)),
LEN (path1) - LEN(REPLACE(path1,'/','')) +1)
THEN SUBSTRING(path1,1,CHARINDEX('/',path1)-1) ELSE
path1 END,
path2 = CASE WHEN path2 + '/' = replicate(SUBSTRING(path2,1,CHARINDEX('/',path2)),
LEN (path2) - LEN(REPLACE(path2,'/','')) +1)
THEN SUBSTRING(path2,1,CHARINDEX('/',path2)-1) ELSE
path2 END,
path3 = CASE WHEN path3 + '/' = replicate(SUBSTRING(path3,1,CHARINDEX('/',path3)),
LEN (path3) - LEN(REPLACE(path3,'/','')) +1)
THEN SUBSTRING(path3,1,CHARINDEX('/',path3)-1) ELSE
path3 END
FROM @t_testd
--3)结果
ProName proid path1 path2 path3
-------------------- -------------------- -------------------- -------------------- --------------------
1 2 4/3/6 5/6/6 7/8/9
2 3 8/3/6 5/7/6 7/8/6
3 4 4/3 9/0 1/5
4 5/6/6 6 s/f/t 7/t/f
--1)数据
SELECT * FROM for_csdn
ProName proid path1 path2 path3
----------- ----------- ----------- ----------- -----------
1 2 4 5 7
1 2 3 6 8
1 2 6 6 9
2 3 8 5 7
2 3 3 7 8
2 3 6 6 6
--2)代码段,第2版本(Shenliang1985):
SET NOCOUNT ON
DECLARE @t_testd TABLE (
t_id INT Identity(1,1),
ProName varchar(20),
proid varchar(20),
path1 varchar(20),
path2 varchar(20),
path3 varchar(20)
)
INSERT @t_testd(ProName)
SELECT DISTINCT ProName FROM for_csdn
--SELECT * FROM @t_testd
DECLARE @t_count INT
SET @t_count=1
---SELECT COUNT(*)FROM @t_testd
WHILE @t_count <=(SELECT COUNT(*)FROM @t_testd)
BEGIN
DECLARE @sql VARCHAR(8000)
DECLARE @sql_1 VARCHAR(8000)
DECLARE @sql_2 VARCHAR(8000)
DECLARE @sql_3 VARCHAR(8000)
DECLARE @sql_4 VARCHAR(8000)
SELECT @sql =ISNULL(@sql +'/' , '') + CAST (A.proid AS varchar(32)) FROM for_csdn A
INNER JOIN @t_testd B
ON A.ProName=B.ProName
AND B.t_id=@t_count
SELECT @sql_1 =ISNULL(@sql_1 +'/' , '') + CAST (A.path1 AS varchar(32)) FROM for_csdn A
JOIN @t_testd B
ON A.ProName=B.ProName
AND B.t_id=@t_count
SELECT @sql_2 =ISNULL(@sql_2 +'/' , '') + CAST (A.path2 AS varchar(32)) FROM for_csdn A
JOIN @t_testd B
ON A.ProName=B.ProName
AND B.t_id=@t_count
SELECT @sql_3 =ISNULL(@sql_3 +'/' , '') + CAST (A.path3 AS varchar(32)) FROM for_csdn A
JOIN @t_testd B
ON A.ProName=B.ProName
AND B.t_id=@t_count
----SELECT @sql
UPDATE @t_testd
SET proid = @sql,
path1 = @sql_1,
path2 = @sql_2,
path3 = @sql_3
WHERE t_id=@t_count
SET @t_count=@t_count+1
SELECT @sql=NULL
SELECT @sql_1=NULL
SELECT @sql_2=NULL
SELECT @sql_3=NULL
--SELECT @t_count
END
-------用REVERSE和中间值判断
SELECT ProName,
proid = CASE WHEN REVERSE(proid) = proid AND SUBSTRING(proid,CAST(ROUND(CAST(LEN(proid) AS FLOAT)/2,0,0)AS INT),CHARINDEX('/',proid)-1) = SUBSTRING(proid,1,CHARINDEX('/',proid)-1)
THEN SUBSTRING(proid,1,CHARINDEX('/',proid)-1) ELSE proid END,
path1 = CASE WHEN REVERSE(path1) = path1 AND SUBSTRING(path1,CAST(ROUND(CAST(LEN(path1) AS FLOAT)/2,0,0)AS INT),CHARINDEX('/',path1)-1) = SUBSTRING(path1,1,CHARINDEX('/',path1)-1)
THEN SUBSTRING(path1,1,CHARINDEX('/',path1)-1) ELSE path1 END,
path2 = CASE WHEN REVERSE(path2) = path2 AND SUBSTRING(path2,CAST(ROUND(CAST(LEN(path2) AS FLOAT)/2,0,0)AS INT),CHARINDEX('/',path2)-1) = SUBSTRING(path2,1,CHARINDEX('/',path2)-1)
THEN SUBSTRING(path2,1,CHARINDEX('/',path2)-1) ELSE path2 END,
path3 = CASE WHEN REVERSE(path3) = path3 AND SUBSTRING(path3,CAST(ROUND(CAST(LEN(path3) AS FLOAT)/2,0,0)AS INT),CHARINDEX('/',path3)-1) = SUBSTRING(path3,1,CHARINDEX('/',path3)-1)
THEN SUBSTRING(path3,1,CHARINDEX('/',path3)-1) ELSE path3 END
FROM @t_testd
--3)结果
ProName proid path1 path2 path3
-------------------- -------------------- -------------------- -------------------- --------------------
1 2 4/3/6 5/6/6 7/8/9
2 3 8/3/6 5/7/6 7/8/6
--0)修改之
SELECT * FROM @t_testd
----结果1
t_id ProName proid path1 path2 path3
----------- -------------------- -------------------- -------------------- -------------------- --------------------
1 1 2/2/2 4/3/6 5/6/6 7/8/9
2 2 3/3/3 8/3/6 5/7/6 7/8/6
----修改成
SELECT ProName,
proid = CASE WHEN proid + '/' = replicate(SUBSTRING(proid,1,CHARINDEX('/',proid)),
LEN (proid) - LEN(REPLACE(proid,'/','')) +1)
THEN SUBSTRING(proid,1,CHARINDEX('/',proid)-1) ELSE
proid END,
path1 = CASE WHEN path1 + '/' = replicate(SUBSTRING(path1,1,CHARINDEX('/',path1)),
LEN (path1) - LEN(REPLACE(path1,'/','')) +1)
THEN SUBSTRING(path1,1,CHARINDEX('/',path1)-1) ELSE
path1 END,
path2 = CASE WHEN path2 + '/' = replicate(SUBSTRING(path2,1,CHARINDEX('/',path2)),
LEN (path2) - LEN(REPLACE(path2,'/','')) +1)
THEN SUBSTRING(path2,1,CHARINDEX('/',path2)-1) ELSE
path2 END,
path3 = CASE WHEN path3 + '/' = replicate(SUBSTRING(path3,1,CHARINDEX('/',path3)),
LEN (path3) - LEN(REPLACE(path3,'/','')) +1)
THEN SUBSTRING(path3,1,CHARINDEX('/',path3)-1) ELSE
path3 END
FROM @t_testd
------结果2
ProName proid path1 path2 path3
-------------------- -------------------- -------------------- -------------------- --------------------
1 2 4/3/6 5/6/6 7/8/9
2 3 8/3/6 5/7/6 7/8/6
--1)表和数据
CREATE TABLE for_csdn
(ProName int,
proid int,
path1 int,
path2 int,
path3 int
)
INSERT INTO for_csdn VALUES(1 ,2 ,4 ,5 ,7)
INSERT INTO for_csdn VALUES(1 ,2 ,3 ,6 ,8)
INSERT INTO for_csdn VALUES(1 ,2 ,6 ,6 ,9)
INSERT INTO for_csdn VALUES(2 ,3 ,8 ,5 ,7)
INSERT INTO for_csdn VALUES(2 ,3 ,3 ,7 ,8)
INSERT INTO for_csdn VALUES(2 ,3, 6 ,6 ,6)
--2)代码段,先处理如下(待改进):
SET NOCOUNT ON
DECLARE @t_testd TABLE (
t_id INT Identity(1,1),
ProName varchar(20),
proid varchar(20),
path1 varchar(20),
path2 varchar(20),
path3 varchar(20)
)
INSERT @t_testd(ProName)
SELECT DISTINCT ProName FROM for_csdn
--SELECT * FROM @t_testd
DECLARE @t_count INT
SET @t_count=1
---SELECT COUNT(*)FROM @t_testd
WHILE @t_count <=(SELECT COUNT(*)FROM @t_testd)
BEGIN
DECLARE @sql VARCHAR(8000)
DECLARE @sql_1 VARCHAR(8000)
DECLARE @sql_2 VARCHAR(8000)
DECLARE @sql_3 VARCHAR(8000)
DECLARE @sql_4 VARCHAR(8000)
SELECT @sql =ISNULL(@sql +'/' , '') + CAST (A.proid AS varchar(32)) FROM for_csdn A
JOIN @t_testd B
ON A.ProName=B.ProName
AND B.t_id=@t_count
SELECT @sql_1 =ISNULL(@sql_1 +'/' , '') + CAST (A.path1 AS varchar(32)) FROM for_csdn A
JOIN @t_testd B
ON A.ProName=B.ProName
AND B.t_id=@t_count
SELECT @sql_2 =ISNULL(@sql_2 +'/' , '') + CAST (A.path2 AS varchar(32)) FROM for_csdn A
JOIN @t_testd B
ON A.ProName=B.ProName
AND B.t_id=@t_count
SELECT @sql_3 =ISNULL(@sql_3 +'/' , '') + CAST (A.path3 AS varchar(32)) FROM for_csdn A
JOIN @t_testd B
ON A.ProName=B.ProName
AND B.t_id=@t_count
----SELECT @sql
UPDATE @t_testd
SET proid = @sql,
path1 = @sql_1,
path2 = @sql_2,
path3 = @sql_3
WHERE t_id=@t_count
SET @t_count=@t_count+1
SELECT @sql=NULL
SELECT @sql_1=NULL
SELECT @sql_2=NULL
SELECT @sql_3=NULL
--SELECT @t_count
--SELECT * FROM @t_testd
END
SELECT * FROM @t_testd
--3)结果
t_id ProName proid path1 path2 path3
----------- -------------------- -------------------- -------------------- -------------------- --------------------
1 1 2/2/2 4/3/6 5/6/6 7/8/9
2 2 3/3/3 8/3/6 5/7/6 7/8/6