例如:表a
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我想得到这样的结果的SQL语句是什么?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
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我想得到这样的结果的SQL语句是什么?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
path1= STUFF
(
(SELECT DISTINCT '/' + path1
from a b where a.ProName=b.ProName and a.proid= b.proid for xml path('')) , 1 , 1 , ''
),
path2= STUFF
(
(SELECT DISTINCT '/' + path2
from a c where a.ProName=c.ProName and a.proid= c.proid for xml path('')) , 1 , 1 , ''
),
path3= STUFF
(
(SELECT DISTINCT '/' + path3
from a d where a.ProName=d.ProName and a.proid= d.proid for xml path('')) , 1 , 1 , ''
)
FROM a a
GROUP BY ProName, proid
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str = ''
select @str = @str + '/' + cast(path1 as varchar) from tb where proid = @a
set @str = right(@str , len(@str) - 1)
return(@str)
End
go
select distinct ProName ,proid, f_hb(proid)path1 from tbpath2,path3类似通过函数处理
--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_countSELECT @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_countSET @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
ENDSELECT * 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
--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)<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
表:shugao
字段:productid(int),shugaopath(varchar),shugaopath2(varchar),shugaopath3(varchar)productid shugaopath shugaopath2 shugaopath3
2 c m t
2 d n a
2 n n b
3 a m t
3 d t a
3 n n n
我想得到这样的结果的SQL语句是什么?productid shugaopath shugaopath2 shugaopath3
2 c/d/n m/n/n t/a/b
3 a/d/n m/t/n t/a/n
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 productidproductid shugaopath shugaopath2 shugaopath3
2 c/d/n m/n/n t/a/b
3 a/d/n m/t/n t/a/n
缺少 FROM 子句。
“=” 附近的 WHERE 子句错误。
“FROM” 附近的 WHERE 子句错误。
无法分析查询文本。
--1)数据SELECT * FROM for_csdnProName 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