27,579
社区成员
发帖
与我相关
我的任务
分享SQL题目：从一列数字中随机找出几个使得和等于10 ，要求存储过程能返回被选取出来的数据的序列号
从一列数字中随机找出几个使得和等于10 ，要求存储过程能返回被选取出来的数据的序列号，找出的序列数不能超过4条。
比如从下表中的number(数量)字段之和等于10，算法举例：2+3+5=10 返回序列号：1，3，4
| id | number |
+----+----+
| 1 | 2 |
| 2 | 2 |
| 3 | 3 |
| 4 | 5 |
| 5 | 2 |
| 6 | 8 |
| 7 | 1 |
| 8 | 2 |
| 9 | 3 |
| 10 | 3 |
比如从下表中的number(数量)字段之和等于10，算法举例：2+3+5=10 返回序列号：1，3，4
| id | number |
+----+----+
| 1 | 2 |
| 2 | 2 |
| 3 | 3 |
| 4 | 5 |
| 5 | 2 |
| 6 | 8 |
| 7 | 1 |
| 8 | 2 |
| 9 | 3 |
| 10 | 3 |
...全文
请发表友善的回复…
发表回复
Ginnnnnnnn 2015-01-28
- 打赏
- 举报
那你这样的话可以先算出有有什么组合能满足相加等于10，再从表里面去筛选，这样的话性能也不会很低
beiguaxiezi 2015-01-27
- 打赏
- 举报
多谢，我明早看下效果如何
还在加载中灬 2015-01-27
- 打赏
- 举报
;WITH CTE AS(
SELECT CAST(id AS VARCHAR(8000))ids,id
,number
,1 Times
FROM TB
WHERE number<=10
UNION ALL
SELECT T2.ids+','+CAST(T1.id AS VARCHAR(8000)),T1.id,T1.number+T2.number
,T2.Times+1
FROM TB T1
JOIN CTE T2 ON T2.id<T1.id AND T1.number+T2.number<=10 AND T2.Times<=3
)
SELECT ids[序列号] FROM CTE
WHERE number=10beiguaxiezi 2015-01-27
- 打赏
- 举报
数据量大的话效率会很低，还有没有其它办法呢？
Ginnnnnnnn 2015-01-27
- 打赏
- 举报
;WITH CTE(ID,Nr) AS
(
SELECT 1,2
UNION ALL SELECT 2,2
UNION ALL SELECT 3,3
UNION ALL SELECT 4,5
UNION ALL SELECT 5,2
UNION ALL SELECT 6,8
UNION ALL SELECT 7,1
UNION ALL SELECT 8,2
UNION ALL SELECT 9,3
UNION ALL SELECT 10,3
)
SELECT a.ID ,b.ID,C.ID
FROM CTE a,CTE b,CTE c
WHERE a.Nr + b.Nr + c.Nr = 10
AND a.ID <= b.ID
AND b.ID <= c.ID
shiyiwan 2015-01-26
- 打赏
- 举报
家里机器上没SQL server,写了个Oracle版的，你稍微改改就可以了。
Connected to Oracle Database 11g Express Edition Release 11.2.0.2.0
Connected as shiyiwan@XE
SQL>
SQL> WITH T AS
2 (SELECT 1 AS ID, 2 AS NUM
3 FROM DUAL
4 UNION ALL
5 SELECT 2, 2
6 FROM DUAL
7 UNION ALL
8 SELECT 3, 3
9 FROM DUAL
10 UNION ALL
11 SELECT 4, 5
12 FROM DUAL
13 UNION ALL
14 SELECT 5, 2
15 FROM DUAL
16 UNION ALL
17 SELECT 6, 8
18 FROM DUAL
19 UNION ALL
20 SELECT 7, 1
21 FROM DUAL
22 UNION ALL
23 SELECT 8, 2
24 FROM DUAL
25 UNION ALL
26 SELECT 9, 3
27 FROM DUAL
28 UNION ALL
29 SELECT 10, 3
30 FROM DUAL)
31 SELECT T1.ID, T2.ID, NULL, NULL
32 FROM T T1, T T2
33 WHERE T1.NUM + T2.NUM = 10
34 AND T1.NUM <> T2.NUM
35 UNION ALL
36 SELECT T1.ID, T2.ID, T3.ID, NULL
37 FROM T T1, T T2, T T3
38 WHERE T1.NUM + T2.NUM + T3.NUM = 10 AND T1.NUM <> T2.NUM AND
39 T1.NUM <> T3.NUM AND T2.NUM <> T3.NUM
40 UNION ALL
41 SELECT T1.ID, T2.ID, T3.ID, T4.ID
42 FROM T T1, T T2, T T3, T T4
43 WHERE T1.NUM + T2.NUM + T3.NUM + T4.NUM = 10 AND T1.NUM <> T2.NUM AND
44 T1.NUM <> T3.NUM AND T1.NUM <> T4.NUM AND T2.NUM <> T3.NUM AND
45 T2.NUM <> T4.NUM AND T3.NUM <> T4.NUM;
ID ID NULL NULL
---------- ---------- ---------- ----------
1 6
2 6
5 6
6 1
6 2
6 5
6 8
8 6
1 3 4
1 4 3
1 4 9
1 4 10
1 9 4
1 10 4
2 3 4
2 4 3
2 4 9
2 4 10
2 9 4
2 10 4
ID ID NULL NULL
---------- ---------- ---------- ----------
3 1 4
3 2 4
3 4 1
3 4 2
3 4 5
3 4 8
3 5 4
3 8 4
4 1 3
4 1 9
4 1 10
4 2 3
4 2 9
4 2 10
4 3 1
4 3 2
4 3 5
4 3 8
4 5 3
4 5 9
4 5 10
ID ID NULL NULL
---------- ---------- ---------- ----------
4 8 3
4 8 9
4 8 10
4 9 1
4 9 2
4 9 5
4 9 8
4 10 1
4 10 2
4 10 5
4 10 8
5 3 4
5 4 3
5 4 9
5 4 10
5 9 4
5 10 4
8 3 4
8 4 3
8 4 9
8 4 10
ID ID NULL NULL
---------- ---------- ---------- ----------
8 9 4
8 10 4
9 1 4
9 2 4
9 4 1
9 4 2
9 4 5
9 4 8
9 5 4
9 8 4
10 1 4
10 2 4
10 4 1
10 4 2
10 4 5
10 4 8
10 5 4
10 8 4
80 rows selected
SQL> 
