22,207
社区成员
发帖
与我相关
我的任务
分享
select 10,cast(a.id as varchar(10))+'+'+cast(b.id as varchar(10)) from #tb a join #tb b on a.id>=b.id
where (a.id+b.id=10)
if OBJECT_ID('tb') is not null
drop table tb;
go
create table tb(id int);
go
insert into tb
select 1 union all select 2 union all select 3 union all select 4 union all
select 5 union all select 6 union all select 7 union all select 8;
go
declare @res table([sum] int, [exp] varchar(100));
insert into @res
select t1.id+t2.id,LTRIM(t1.id)+'+'+LTRIM(t2.id)
from tb t1, tb t2 where t1.id+t2.id<=10 and t1.id<t2.id;
while @@ROWCOUNT>0
begin
update t1
set t1.[sum]=t1.[sum]+t2.id, t1.[exp]=t1.[exp]+'+'+LTRIM(t2.id)
from @res t1, tb t2 where t1.[sum]+t2.id<=10;
end
select * from @res where [sum]=10;
go
/*
10 1+2+1+1+1+1+1+1+1
10 1+3+1+1+1+1+1+1
10 2+3+1+1+1+1+1
10 1+4+1+1+1+1+1
10 2+4+1+1+1+1
10 3+4+1+1+1
10 1+5+1+1+1+1
10 2+5+1+1+1
10 3+5+1+1
10 4+5+1
10 1+6+1+1+1
10 2+6+1+1
10 3+6+1
10 4+6
10 1+7+1+1
10 2+7+1
10 3+7
10 1+8+1
10 2+8
*/
IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB
GO
CREATE TABLE TB(ID INT)
INSERT INTO TB
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8
;WITH MU AS (
SELECT ID,CAST(ID AS VARCHAR(MAX)) AS VAL FROM TB
UNION ALL
SELECT MU.ID+TB.ID,VAL+'+'+LTRIM(TB.ID)
FROM MU
INNER JOIN TB ON MU.ID+TB.ID<=10 AND TB.ID>MU.ID
)
SELECT VAL FROM MU WHERE ID=10
/*
4+6
3+7
2+8
1+3+6
1+2+7
*/
IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB
GO
CREATE TABLE TB(ID INT)
INSERT INTO TB
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8
;WITH MU AS (
SELECT ID,CAST(ID AS VARCHAR(MAX)) AS VAL FROM TB
UNION ALL
SELECT MU.ID+TB.ID,VAL+'+'+LTRIM(TB.ID)
FROM MU
INNER JOIN TB ON MU.ID+TB.ID<=10 AND CHARINDEX('+'+LTRIM(TB.ID)+'+','+'+MU.VAL+'+')=0
AND TB.ID>MU.ID
)
SELECT VAL FROM MU WHERE ID=10
/*
4+6
3+7
2+8
1+3+6
1+2+7
*/
10 记录
----------- -------------------------------------------
10 4+3+2+1
10 5+4+1
10 5+3+2
10 6+3+1
10 7+2+1
10 6+4
10 7+3
10 8+2
10 9+1
(9 行受影响)
用这个吧!!!保证是正确的。。。
select 10, cast(a.id as varchar(10))+'+'+cast(b.id as varchar(10))+'+'+cast(c.id as varchar(10))+'+'+cast(d.id as varchar(10))
from #tb a join #tb b on a.id>b.id
join #tb c on a.id>c.id and b.id>c.id
join #tb d on a.id>c.id and b.id>c.id and c.id>d.id
where (a.id+b.id+c.id+d.id=10)
union all
select 10,cast(a.id as varchar(10))+'+'+cast(b.id as varchar(10))+'+'+cast(c.id as varchar(10))from #tb a join #tb b on a.id>b.id
join #tb c on a.id>c.id and b.id>c.id
where (a.id+b.id+c.id=10)
union all
select 10,cast(a.id as varchar(10))+'+'+cast(b.id as varchar(10)) from #tb a join #tb b on a.id>b.id
where (a.id+b.id=10)
--四个相加(最多四个相加)
select*from #tb a join #tb b on 1=1
join #tb c on 1=1 join #tb d on 1=1
where (a.id+b.id+c.id+d.id=10)
and a.id<>b.id and a.id<>c.id and a.id<>d.id
and b.id<>c.id and b.id<>d.id and c.id<>d.id
--三个相加
select*from #tb a join #tb b on 1=1
join #tb c on 1=1
where (a.id+b.id+c.id=10) and a.id<>b.id and a.id<>c.id and b.id<>c.id
--两个相加
select*from #tb a join #tb b on 1=1
where (a.id+b.id=10) and a.id<>b.id
--SQL2000可以在4楼的基础上加个条件判断
IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB
GO
CREATE TABLE TB(ID INT)
INSERT INTO TB
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 UNION ALL
SELECT 8
declare @res table([sum] int, [exp] varchar(100));
insert into @res
select t1.id+t2.id,LTRIM(t1.id)+'+'+LTRIM(t2.id)
from tb t1, tb t2 where t1.id+t2.id<=10 and t1.id<t2.id;
while @@ROWCOUNT>0
begin
update t1
set t1.[sum]=t1.[sum]+t2.id, t1.[exp]=t1.[exp]+'+'+LTRIM(t2.id)
from @res t1, tb t2 where t1.[sum]+t2.id<=10;
end
select * from @res a
where [sum]=10 and LEN(exp)=len(replace(exp,cast(1 as varchar(1))+'+',''))
go
/* 结果
sum exp
----------- ----------------------------------------------------------------------------------------------------
10 4+5+1
10 3+6+1
10 4+6
10 2+7+1
10 3+7
10 2+8
(6 行受影响)*/