create table getdata ( Id int primary key, data int,) select * from getdatainsert getdata select 1,100 union all select 2,900 union all select 3,400 union all select 4,300 union all select 5,200 union all select 6,200 union all select 7,700create table #vvc ( ID int, IDS nvarchar(1000) ) select distinct * from #vvc declare @ID int, @ID2 int, @ID3 int, @CS int, @round int, @numer1 int, @numer2 int, @numer3 int, @str nvarchar(1000)declare AEF cursor for select ID,data from getdataopen AEF fetch next From AEF into @ID,@numer1while @@FETCH_STATUS=0 begin select @round=count(1)-1 from getdata set @ID3=-1 while @round>0 begindeclare AEF2 cursor for select ID,data from getdata where ID <>@ID and ID>@ID3 open AEF2 fetch next From AEF2 into @ID2,@numer2 while @@FETCH_STATUS=0 begin if @CS=1 begin set @ID3=@ID2 end set @CS=@CS+1 set @numer3=isnull(@numer3,0)+@numer2 if @numer3<=@numer1 begin set @str=@str+','+cast(@ID2 AS nvarchar(10)) end else begin set @numer3=isnull(@numer3,0)-@numer2 end fetch next From AEF2 into @ID2,@numer2 end close AEF2 DEALLOCATE AEF2 if @str<>'' and @numer3=@numer1 begin insert #vvc select @ID,substring(@str,2,len(@str)) end set @str='' set @numer3=0 set @round=@round-1 set @CS=1end fetch next From AEF into @ID,@numer1 end close AEF DEALLOCATE AEF select distinct * from #vvcdrop #vvc
我写的语句里,最终有一步分是开始打算转换成交叉表用的,所以如果只呈现 2 1,3,5,6 2 3,4,5 ...这样的结果的话,我稍加改动,且省略一部分,效率可以有提升。不过不会太多,主要效率差异在cte这里。IF OBJECT_ID('tb') IS NOT NULL DROP TABLE tb GO CREATE TABLE tb([id] INT,[data] INT) INSERT tb SELECT 1,100 UNION ALL SELECT 2,900 UNION ALL SELECT 3,400 UNION ALL SELECT 4,300 UNION ALL SELECT 5,200 UNION ALL SELECT 6,200 UNION ALL SELECT 7,700 /************/ /*Test Data*/ /***fcuandy**/ /*2009-02-11*/ /************/ GO --SELECT * FROM tb GOCREATE FUNCTION f_tab (@id INT) RETURNS @t TABLE(sid INT,gid VARCHAR(100),id INT,data INT) AS BEGIN DECLARE @data INT SELECT @data = data FROM tb WHERE id=@id ; WITH fc AS ( SELECT *,data AS totData,CAST(id AS VARCHAR(100)) gid FROM tb WHERE id!=@id AND data <= @data UNION ALL SELECT a.*,a.data + b.totData,CAST(gid + ',' + RTRIM(a.id) AS VARCHAR(100)) FROM tb a,fc b WHERE a.data + b.totData <= @data AND a.id > b.id AND a.id!=@id
) INSERT @t SELECT @id,gid,id,totData FROM fc RETURNEND GO SELECT c.id,a.gid FROM ( SELECT * FROM tb ) c CROSS APPLY ( SELECT * FROM f_tab(c.id) ) a WHERE a.Data = c.data /* 2 6,7 2 5,7 2 3,4,5 2 3,4,6 2 1,3,5,6 3 5,6 3 1,4 4 1,5 4 1,6 5 6 6 5 7 4,5,6 7 3,4 7 1,3,5 7 1,3,6 */GO DROP FUNCTION f_tab GO
这样的小问题对你来说看来只算小菜了
create table getdata
(
Id int primary key,
data int,)
select * from getdatainsert getdata select 1,100
union all
select 2,900
union all
select 3,400
union all
select 4,300
union all
select 5,200
union all
select 6,200
union all
select 7,700create table #vvc
(
ID int,
IDS nvarchar(1000)
)
select distinct * from #vvc
declare
@ID int,
@ID2 int,
@ID3 int,
@CS int,
@round int,
@numer1 int,
@numer2 int,
@numer3 int,
@str nvarchar(1000)declare AEF cursor
for
select ID,data from getdataopen AEF
fetch next From AEF into @ID,@numer1while @@FETCH_STATUS=0
begin
select @round=count(1)-1 from getdata
set @ID3=-1
while @round>0
begindeclare AEF2 cursor
for
select ID,data from getdata where ID <>@ID and ID>@ID3
open AEF2
fetch next From AEF2 into @ID2,@numer2
while @@FETCH_STATUS=0
begin
if @CS=1
begin
set @ID3=@ID2
end
set @CS=@CS+1
set @numer3=isnull(@numer3,0)+@numer2
if @numer3<=@numer1
begin
set @str=@str+','+cast(@ID2 AS nvarchar(10))
end
else
begin
set @numer3=isnull(@numer3,0)-@numer2
end
fetch next From AEF2 into @ID2,@numer2
end
close AEF2
DEALLOCATE AEF2
if @str<>'' and @numer3=@numer1
begin
insert #vvc select @ID,substring(@str,2,len(@str))
end
set @str=''
set @numer3=0
set @round=@round-1
set @CS=1end
fetch next From AEF into @ID,@numer1
end
close AEF
DEALLOCATE AEF
select distinct * from #vvcdrop #vvc
如果数据像上边那样再大一些,会用N长时间N多CPU N多内存.
理论上可以的高效率,但实际上这样做的话,结果可能就不完整。比如一条记录data为 900
每多记录data 为100
控制深度的话,可能就组合不出900
7=id2:4+id3:3
7=id4:5+id5:2
7=id4:5+id8:2
2 1,3,5,6
2 3,4,5
...这样的结果的话,我稍加改动,且省略一部分,效率可以有提升。不过不会太多,主要效率差异在cte这里。IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb([id] INT,[data] INT)
INSERT tb SELECT 1,100
UNION ALL SELECT 2,900
UNION ALL SELECT 3,400
UNION ALL SELECT 4,300
UNION ALL SELECT 5,200
UNION ALL SELECT 6,200
UNION ALL SELECT 7,700
/************/
/*Test Data*/
/***fcuandy**/
/*2009-02-11*/
/************/
GO
--SELECT * FROM tb
GOCREATE FUNCTION f_tab
(@id INT)
RETURNS @t TABLE(sid INT,gid VARCHAR(100),id INT,data INT)
AS
BEGIN
DECLARE @data INT
SELECT @data = data FROM tb WHERE id=@id
;
WITH fc AS
(
SELECT *,data AS totData,CAST(id AS VARCHAR(100)) gid FROM tb WHERE id!=@id AND data <= @data
UNION ALL
SELECT a.*,a.data + b.totData,CAST(gid + ',' + RTRIM(a.id) AS VARCHAR(100)) FROM tb a,fc b
WHERE a.data + b.totData <= @data AND a.id > b.id AND a.id!=@id
)
INSERT @t SELECT @id,gid,id,totData FROM fc
RETURNEND
GO
SELECT c.id,a.gid FROM
(
SELECT * FROM tb
) c
CROSS APPLY
(
SELECT * FROM f_tab(c.id)
) a
WHERE a.Data = c.data
/*
2 6,7
2 5,7
2 3,4,5
2 3,4,6
2 1,3,5,6
3 5,6
3 1,4
4 1,5
4 1,6
5 6
6 5
7 4,5,6
7 3,4
7 1,3,5
7 1,3,6
*/GO
DROP FUNCTION f_tab
GO