求一个难写的SQL

zedan 2009-05-22 02:26:36
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
.......

需求应该描述清楚了吧
...全文
268 20 打赏 收藏 转发到动态 举报
写回复
用AI写文章
20 条回复
切换为时间正序
请发表友善的回复…
发表回复
zedan 2009-05-25
  • 打赏
  • 举报
 回复
谢谢大家了，有没有更好的解决方案，
字符串操作的在数据量大点时比较慢，要7，8秒。
用row_number() over(order by id_path)的没试，因为要兼容2000的数据库。

好象大家都没注意到还有这个表可用？
“假设有一个表A知道所有的子层关系，(是否利用这个表都可以) ”
gjg0423 2009-05-23
  • 打赏
  • 举报
 回复
虽然我不会，但感觉高人就是高人
jiangshun 2009-05-23
  • 打赏
  • 举报
 回复
[Quote=引用 12 楼 jiangshun 的回复:]
SQL code
--姑且在数据库中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 , …
[/Quote]




---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')
亥亥 2009-05-23
  • 打赏
  • 举报
 回复
帮顶
lg3605119 2009-05-23
  • 打赏
  • 举报
 回复
上面的有点小问题，更改后的～～


if object_id('T') is not null

  drop table T

GO



create 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'            ,  1



GO

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

go



select * 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
lg3605119 2009-05-23
  • 打赏
  • 举报
 回复
封装成标值函数


if object_id('T') is not null

  drop table T

GO



create 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'            ,  1



GO

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

go



select * 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
lg3605119 2009-05-23
  • 打赏
  • 举报
 回复 


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'            ,  1



declare @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

*/
devilidea 2009-05-23
  • 打赏
  • 举报
 回复
看的头晕
Jamin_Liu 2009-05-23
  • 打赏
  • 举报
 回复
--測試數據
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


jiangshun 2009-05-23
  • 打赏
  • 举报
 回复 


--姑且在数据库中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


lvenqi 2009-05-22
  • 打赏
  • 举报
 回复
高人。。。
qiaoliqing_2006 2009-05-22
  • 打赏
  • 举报
 回复
呵呵 帮顶了 。。。
zedan 2009-05-22
  • 打赏
  • 举报
 回复
[Quote=引用 7 楼 SQL77 的回复:]
SQL codeCREATETABLETBTEST(colINT, id_pathVARCHAR(800),countINT)INSERTTBTESTSELECT1,'2,201',2UNIONSELECT2,'2,203,2031',1UNIONSELECT3,'2,203,2032,20321',2UNIONSELECT4,'2,204,2041,20411',3UNIONSELECT5,'2,205,2051',4UNIONSELECT6,'3,301',1SELECT*FROMTBTESTCREATEPROCID_PATH(@id_pathVARCHAR(50))ASBEGINIFEXISTS(SELECT*FROMTBTESTWHERELEFT(LTRIM(id_path),1)=@id_path)BEGINSELECTCOL=IDENTITY(INT,1,1),*IN…
[/Quote]

先谢谢了，再汗一下，用两个IF啊？ 传入的ID是多变的。
SQL77 2009-05-22
  • 打赏
  • 举报
 回复 
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 TBTEST



CREATE 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

END



EXEC 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 行）
meheartfly 2009-05-22
  • 打赏
  • 举报
 回复
盼望小梁来解决！
meheartfly 2009-05-22
  • 打赏
  • 举报
 回复
设计这样的表结构的人真是高手！
JonasFeng 2009-05-22
  • 打赏
  • 举报
 回复
这是谁设计的表，有些晕菜。

帮顶下。
--小F-- 2009-05-22
  • 打赏
  • 举报
 回复
等强人 帮顶
utopia54 2009-05-22
  • 打赏
  • 举报
 回复
UP
zedan 2009-05-22
  • 打赏
  • 举报
 回复
对齐点


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

.......
SQL 嵌套 N 层太长太难写怎么办？
我们工作中写SQL处理数据是家常便饭，不管是应用内数据处理还是临时查询分析都可以用SQL完成，相对其他技术（如Java等高级语言）也更简单。不过，SQL的简单只限于简单需求，有些复杂计算场景SQL写起来却很难，嵌套N...
写一条sql语句的过程
今天碰到了一个需求，要求写一份报表，但是难在了sql语句上面。本人sql水平，仅限于select * from 这种简单的sql语句，看到老长的sql语句头就疼。下面分享我写这条语句的过程。（当然少不了百度，和向公司的学长请教...
一些比较难的SQL语句查询
查询每个部门薪水最高的人。 有两张表employees、departments; employees +------+------+---------------+--------+ | id | name | department_id | salary | +------+------+---------------+--------+ | 2
SQL Server数据库基础知识——数据库存储过程怎么写
存储过程是一个预编译的SQL语句，优点是允许模块化的设计，就是说只需创建一次，以后在程序中就可以调用多次。如果某次操作需要执行多次SQL，使用存储过程比单纯SQL语句执行要快。可以用一个“execute 存储过程名 ...
业务逻辑写在SQL中还是代码中问题
作为一名程序员，逻辑思维应该是基于代码实现逻辑，既然不是专业DBA，不应将过多的经历用在如何通过复杂SQL去实现功能，而是尽量保证SQL的简单，易于迁移与理解，通过代码层面去实现功能。要知道的是需求是多变的，...
MS-SQL Server

34,587

社区成员

254,588

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章