问？SQL语句求和问题

[nbxkele]

有一张表
ID
1
2
3
4
5
6
7
8

取出所有ID的和=10的记录情况，记录数不定，例如 8+2=10，取出8和2，，1+2+7=10，取出1,2,7。。

[xxccy]

顶2楼
嵌套用得妙
学习了

[xxccy]

--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 行受影响)*/

[nbxkele]

想要4楼的结果

为什么我得到的是


10 1+2+7
10 1+3+6
10 2+3+5
10 1+4+5
10 2+4+4
10 3+4+3
10 1+5+4
10 2+5+3
10 3+5+2
10 4+5+1
10 1+6+3
10 2+6+2
10 3+6+1
10 4+6
10 1+7+2
10 2+7+1
10 3+7
10 1+8+1
10 2+8

[duanzhi1984]

--四个相加（最多四个相加)
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 

[duanzhi1984]

用这个吧！！！保证是正确的。。。
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) 

[duanzhi1984]

结果如下：

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 行受影响)

[RobinBest]

我一直在找5+5=10呢？

其实，这是一个经典的问题！

[guguda2008]

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
*/

[guguda2008]

去掉一个不用的条件

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
*/

[nbxkele]

在SQL 2000 中提是错误啊

[xman_78tom]

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
*/

[duanzhi1984]

[Quote=引用 12 楼 robinbest 的回复:]
我一直在找5+5=10呢？

其实，这是一个经典的问题！
[/Quote]

这就 要看你的求和的条件中的两个数字是否可以相等了。。。

根据你需要的更改程序中的条件，若要5+5=10，更改如下：

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) 

[nbxkele]

请问像4楼的 这种结果可以实现的吗？

/*
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
*/

[duanzhi1984]

你这是什么规律呢？

不明白你的规律

四楼不是已经实现你的规律 了吗

[人鱼传说]

学习了，楼主的问题像是数学问题，有点意思