• 全部
  • 基础类
  • 应用实例
  • 新技术前沿

求一个难写的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
.......

需求应该描述清楚了吧
...全文
236 点赞 收藏 20
写回复
20 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
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
.......

回复
相关推荐
发帖
MS-SQL Server
创建于2007-09-28

3.3w+

社区成员

MS-SQL Server相关内容讨论专区
申请成为版主
帖子事件
创建了帖子
2009-05-22 02:26
社区公告
暂无公告