求一个难写的SQL

Question

col    id_path           count
1 2,201 2
2 2,203,2031 1
3 2,203,2032,20321 2
4 2,204,2041,20411 3
5 2,205,2051 4
6 3,301 1求SQL/UDF， 根据传入的id, 取得它的直接子层的 id_path 和 count(不是直接子层的count要加在直接子层上)
比如 传入2结果为
col    id_path           count
1 2,201 2
2 2,203 3   (行2，行3并起来了)
3 2,204 3
4 2,205 4再如，传入 203
col    id_path           count
1 2,203,2031 1
2 2,203,2032 2假设有一个表A知道所有的子层关系，(是否利用这个表都可以)
A
col id childid deep (直接子层deep=1,否则每隔一层则加一)
1 2 201 1
2 2 2011 2
3 2 203 1
4 2 2031 2
5 2 2032 2
6 3 301 1
7       201 2011 1
.......需求应该描述清楚了吧

Answer 1

对齐点col    id_path          count
1      2,201              2
2      2,203,2031         1
3      2,203,2032,20321   2
4      2,204,2041,20411   3
5      2,205,2051         4
6      3,301              1求SQL/UDF， 根据传入的id, 取得它的直接子层的 id_path 和 count(不是直接子层的count要加在直接子层上)
比如 传入2结果为
col    id_path          count
1      2,201              2
2      2,203              3  (行2，行3并起来了)
3      2,204              3
4      2,205              4再如，传入 203
col    id_path          count
1      2,203,2031         1
2      2,203,2032         2假设有一个表A知道所有的子层关系，(是否利用这个表都可以)
A
col  id    childid   deep (直接子层deep=1,否则每隔一层则加一)
1    2       201      1
2    2       2011     2
3    2       203      1
4    2       2031     2
5    2       2032     2
6    3       301      1
7    201     2011     1
....... 

Answer 2

CREATE TABLE TBTEST(col INT,    id_path VARCHAR(800),count INT)
INSERT TBTEST
SELECT 1 ,     '2,201'            ,  2 UNION 
SELECT 2 ,     '2,203,2031'       ,  1 UNION
SELECT 3 ,     '2,203,2032,20321' ,  2 UNION
SELECT 4 ,     '2,204,2041,20411' ,  3 UNION
SELECT 5 ,     '2,205,2051'       ,  4 UNION
SELECT 6 ,     '3,301'            ,  1
SELECT * FROM TBTESTCREATE PROC ID_PATH(@id_path VARCHAR(50))
AS
BEGIN
    IF EXISTS(SELECT * FROM TBTEST WHERE LEFT(LTRIM(id_path),1)=@id_path)
 BEGIN
    SELECT COL=IDENTITY(INT,1,1),* INTO #TBTEST FROM 
    (SELECT LEFT(LTRIM(id_path),5)AS ID_PATH ,SUM(COUNT)COUNT 
    FROM TBTEST  WHERE LEFT(LTRIM(id_path),1)=@id_path GROUP BY  LEFT(LTRIM(id_path),5))AS T
    SELECT * FROM #TBTEST
  END 
 IF EXISTS(SELECT 1 FROM TBTEST WHERE SUBSTRING(LEFT(LTRIM(id_path),5),3,3)=@id_path)
  BEGIN
   SELECT COL=IDENTITY(INT,1,1),* INTO #TBTEST1 FROM
   (SELECT LEFT(LTRIM(id_path),10)AS ID_PATH,SUM(COUNT)COUNT 
    FROM TBTEST  WHERE SUBSTRING(LEFT(LTRIM(id_path),5),3,3)=@id_path GROUP BY LEFT(LTRIM(id_path),10))AS T1
   SELECT * FROM #TBTEST1
 END
ENDEXEC ID_PATH '203'（所影响的行数为 2 行）COL         ID_PATH              COUNT       
----------- -------------------- ----------- 
1           2,203,2031           1
2           2,203,2032           2（所影响的行数为 2 行）
EXEC ID_PATH '2'
（所影响的行数为 4 行）COL         ID_PATH    COUNT       
----------- ---------- ----------- 
1           2,201      2
2           2,203      3
3           2,204      3
4           2,205      4（所影响的行数为 4 行）  

Answer 3

先谢谢了，再汗一下，用两个IF啊？ 传入的ID是多变的。

Answer 4

--姑且在数据库中id_path中2为02，在查询的时候02替换掉就可以了
if object_id('tab') is not null
drop table tab
create table tab (col int, id_path VARCHAR(800),count int)
INSERT INTO tab
SELECT 1 ,     '02,201'            ,  2 UNION 
SELECT 2 ,     '02,203,2031'       ,  1 UNION
SELECT 3 ,     '02,203,2032,20321' ,  2 UNION
SELECT 4 ,     '02,204,2041,20411' ,  3 UNION
SELECT 5 ,     '02,205,2051'       ,  4 UNION
SELECT 6 ,     '03,301'            ,  1
declare @s varchar(20)
set @s = '2031'
select
       id_path= replace(substring(id_path,1,charindex(','+@s,','+id_path)+len(@s)*2+1),'02','2'),--在查询的时候02替换为2
       [count]=count([count])
from tab
where charindex(','+@s+',',','+id_path+',')>0
group by replace(substring(id_path,1,charindex(','+@s,','+id_path)+len(@s)*2+1),'02','2')
/*
（所影响的行数为 6 行）id_path              count       
-------------------- ----------- 
2,203,2031           1（所影响的行数为 1 行）
*/
declare @s varchar(20)
set @s = '02'
select
       id_path= replace(substring(id_path,1,charindex(','+@s,','+id_path)+len(@s)*2+1),'02','2'),
       [count]=count([count])
from tab
where charindex(','+@s+',',','+id_path+',')>0
group by replace(substring(id_path,1,charindex(','+@s,','+id_path)+len(@s)*2+1),'02','2')
/*
id_path                         count       
------------------------------- ----------- 
2,201                           1
2,203                           2
2,204                           1
2,205                                                                                                                                                                                                                                                            1（所影响的行数为 4 行）
*/drop table tab

Answer 5

declare @T table(col int, id_path VARCHAR(800),count int)
INSERT INTO @T
SELECT 1 ,     '2,201'            ,  2 UNION 
SELECT 2 ,     '2,203,2031'       ,  1 UNION
SELECT 3 ,     '2,203,2032,20321' ,  2 UNION
SELECT 4 ,     '2,204,2041,20411' ,  3 UNION
SELECT 5 ,     '2,205,2051'       ,  4 UNION
SELECT 6 ,     '3,301'            ,  1declare @s varchar(20)
set @s = '2'select col = row_number() over(order by id_path), 
       id_path ,
       [count]
from 
(
select id_path = substring(id_path,1,charindex(',', ','+id_path+',', charindex(',',','+id_path+',',charindex(','+@s+',',','+id_path+',')+1)+1)-2),
   [count] = sum([count])
from @T 
where charindex(','+@s+',',','+id_path+',')>0
group by substring(id_path,1,charindex(',', ','+id_path+',', charindex(',',','+id_path+',',charindex(','+@s+',',','+id_path+',')+1)+1)-2)
)T/*
1 2,201 2
2 2,203 3
3 2,204 3
4 2,205 4
*/

Answer 6

封装成标值函数if object_id('T') is not null
  drop table T
GOcreate table T(col int, id_path VARCHAR(800),count int)
INSERT INTO T
SELECT 1 ,     '2,201'            ,  2 UNION 
SELECT 2 ,     '2,203,2031'       ,  1 UNION
SELECT 3 ,     '2,203,2032,20321' ,  2 UNION
SELECT 4 ,     '2,204,2041,20411' ,  3 UNION
SELECT 5 ,     '2,205,2051'       ,  4 UNION
SELECT 6 ,     '3,301'            ,  1GO
create function f_getchild(@s varchar(100))
returns @t table(col int,id_path varchar(100),[count] int)
as 
begin
    insert into @t
select col = row_number() over(order by id_path), 
   id_path ,
   [count]
from 
(
select id_path = substring(id_path,1,charindex(',', ','+id_path+',', charindex(',',','+id_path+',',charindex(','+@s+',',','+id_path+',')+1)+1)-2),
   [count] = sum([count])
from T 
where charindex(','+@s+',',','+id_path+',')>0
group by substring(id_path,1,charindex(',', ','+id_path+',', charindex(',',','+id_path+',',charindex(','+@s+',',','+id_path+',')+1)+1)-2)
)A
    return 
end
goselect * from f_getchild('2')
/*
1 2,201 2
2 2,203 3
3 2,204 3
4 2,205 4
*/select * from f_getchild('203')
/*
1 2,203,2031 1
2 2,203,2032 2
*/drop function f_getchild
drop table T

Answer 7

上面的有点小问题，更改后的～～if object_id('T') is not null
  drop table T
GOcreate table T(col int, id_path VARCHAR(800),count int)
INSERT INTO T
SELECT 1 ,     '2,201'            ,  2 UNION 
SELECT 2 ,     '2,203,2031'       ,  1 UNION
SELECT 3 ,     '2,203,2032,20321' ,  2 UNION
SELECT 4 ,     '2,204,2041,20411' ,  3 UNION
SELECT 5 ,     '2,205,2051'       ,  4 UNION
SELECT 6 ,     '3,301'            ,  1GO
create function f_getchild(@s varchar(100))
returns @t table(col int,id_path varchar(100),[count] int)
as 
begin
    insert into @t
select col = row_number() over(order by id_path), 
   id_path ,
   [count]
from 
(
select id_path =  case when charindex(','+@s+',',','+id_path+',')-1 <> len(stuff(reverse(id_path),1,charindex(',',reverse(id_path))-1,''))
  then
  substring(id_path,1,charindex(',', ','+id_path+',', charindex(',',','+id_path+',',charindex(','+@s+',',','+id_path+',')+1)+1)-2)
  else
   id_path
  end,
   [count] = sum([count])
from T 
where charindex(','+@s+',',','+id_path+',')>0
group by
              case when charindex(','+@s+',',','+id_path+',')-1 <> len(stuff(reverse(id_path),1,charindex(',',reverse(id_path))-1,''))
  then
  substring(id_path,1,charindex(',', ','+id_path+',', charindex(',',','+id_path+',',charindex(','+@s+',',','+id_path+',')+1)+1)-2)
                  else
                   id_path
              end
)A
    return 
end
goselect * from f_getchild('2')
/*
1 2,201 2
2 2,203 3
3 2,204 3
4 2,205 4
*/select * from f_getchild('20321')
/*
1 2,203,2032,20321 2
*/drop function f_getchild
drop table T

Answer 8

---sum([count])
declare @s varchar(20)
set @s = '02'
select
       id_path= replace(substring(id_path,1,charindex(','+@s,','+id_path)+len(@s)*2+1),'02','2'),
       [count]=sum([count])
from tab
where charindex(','+@s+',',','+id_path+',')>0
group by replace(substring(id_path,1,charindex(','+@s,','+id_path)+len(@s)*2+1),'02','2')

Answer 9

--測試數據
declare @source table
(
col int,
id_path varchar(50),
[count] int
)insert into @source
values(1,'2,201',2),
(2,'2,203,2031',1),
(3,'2,203,2032,20321',2),
(4,'2,204,2041,20411',3),
(5,'2,205,2051',4),
(6,'3,301',1);--參數
declare @filter varchar(5)='2'--結果
select case when len(SUBSTRING(id_path,1,case when CHARINDEX(@filter+',',id_path)=1 then CHARINDEX(@filter,id_path)+LEN(@filter) else CHARINDEX(','+@filter,id_path)+LEN(@filter) end))=LEN(id_path)
   then id_path
   else 
case when CHARINDEX(',',id_path,len(SUBSTRING(id_path,1,case when CHARINDEX(@filter+',',id_path)=1 then CHARINDEX(@filter,id_path)+LEN(@filter) else CHARINDEX(','+@filter,id_path)+LEN(@filter) end))+2)=0
 then id_path
else SUBSTRING(id_path,1,charindex(',',id_path,len(SUBSTRING(id_path,1,case when CHARINDEX(@filter+',',id_path)=1 then CHARINDEX(@filter,id_path)+LEN(@filter) else CHARINDEX(','+@filter,id_path)+LEN(@filter) end))+2)-1)  end
   end as id_path
    ,SUM([count]) as [count]
    from @source
where id_path like @filter+'%' or id_path like '%,'+@filter+'%'
group by  case when len(SUBSTRING(id_path,1,case when CHARINDEX(@filter+',',id_path)=1 then CHARINDEX(@filter,id_path)+LEN(@filter) else CHARINDEX(','+@filter,id_path)+LEN(@filter) end))=LEN(id_path)
   then id_path
   else 
case when CHARINDEX(',',id_path,len(SUBSTRING(id_path,1,case when CHARINDEX(@filter+',',id_path)=1 then CHARINDEX(@filter,id_path)+LEN(@filter) else CHARINDEX(','+@filter,id_path)+LEN(@filter) end))+2)=0
 then id_path
else SUBSTRING(id_path,1,charindex(',',id_path,len(SUBSTRING(id_path,1,case when CHARINDEX(@filter+',',id_path)=1 then CHARINDEX(@filter,id_path)+LEN(@filter) else CHARINDEX(','+@filter,id_path)+LEN(@filter) end))+2)-1)  end
   end
   

Answer 10

谢谢大家了，有没有更好的解决方案，
字符串操作的在数据量大点时比较慢，要7，8秒。
用row_number() over(order by id_path)的没试，因为要兼容2000的数据库。好象大家都没注意到还有这个表可用？
“假设有一个表A知道所有的子层关系，(是否利用这个表都可以) ”