在SQL中，如何生成5！56的每种可能的唯一组合?

问题描述

我有一个带有一个列"的表"元素，键入SMALLINT，它包含从1到56的数字.如何使用SQL语句生成从1到56的每个可能组合的5个数字的唯一集? /p>

在APL(编程语言)中，简单的二进位函数5！56可以解决问题！

在良好的MS-DOS QBASIC中，我是这样实现的:

10  OPEN "C:\5NUMBERS.OUT" FOR OUTPUT ACCESS READ WRITE AS #1
12  LET SER = 0

15  LET E = 56
30      FOR B5 = 5 TO E
40          FOR B4 = 4 TO E
50              FOR B3 = 3 TO E
60                  FOR B2 = 2 TO E
70                      FOR B1 = 1 TO E
80

88  IF B5 = B1 THEN 190
89  IF B5 = B2 THEN 190
90  IF B5 = B3 THEN 190
91  IF B5 = B4 THEN 190

92  IF B4 = B1 THEN 180
93  IF B4 = B2 THEN 180
94  IF B4 = B3 THEN 180

95  IF B3 = B1 THEN 170
96  IF B3 = B2 THEN 170

97  IF B2 = B1 THEN 160

98 LET SER = SER + 1

100 PRINT #1, SER; "|";
130 PRINT #1, B1; "|";
131 PRINT #1, B2; "|";
132 PRINT #1, B3; "|";
133 PRINT #1, B4; "|";
134 PRINT #1, B5; "|";
140 PRINT #1, B1 + B2 + B3 + B4 + B5; "|"

150                     NEXT B1
160                 NEXT B2
170             NEXT B3
180         NEXT B4
190     NEXT B5
205 CLOSE
210 END
220 SYSTEM 

顺便说一下，这将我的加载文件创建到了INFORMIX-SQL表中

TABLE combos
(
seq_id SERIAL,
ball_1 SMALLINT,
ball_2 SMALLINT,
ball_3 SMALLINT,
ball_4 SMALLINT,
ball_5 SMALLINT,
sum    SMALLINT
);

我用combos.sum生成了一个钟形曲线图，显示了每个元素具有相同总和的组合的数量.

解决方案

如果要包括成对的相同数字，例如(5,5):

SELECT e1.number AS number1
      ,e2.number AS number2
FROM   elements e1
      ,elements e2
WHERE  e1.number <= e2.number;

如果您希望每对中只有不同的数字:

SELECT e1.number AS number1
      ,e2.number AS number2
FROM   elements e1
      ,elements e2
WHERE  e1.number < e2.number;

I have a TABLE "elements" with one COLUMN "number", type SMALLINT that contains numbers 1 thru 56. How can I generate unique sets of 5 numbers of every possible combination from 1 to 56, using an SQL statement?

In APL (programming language) a simple dyadic function 5!56 does the trick!

EDIT: In good ole MS-DOS QBASIC, I accomplished it like this:

10  OPEN "C:\5NUMBERS.OUT" FOR OUTPUT ACCESS READ WRITE AS #1
12  LET SER = 0

15  LET E = 56
30      FOR B5 = 5 TO E
40          FOR B4 = 4 TO E
50              FOR B3 = 3 TO E
60                  FOR B2 = 2 TO E
70                      FOR B1 = 1 TO E
80

88  IF B5 = B1 THEN 190
89  IF B5 = B2 THEN 190
90  IF B5 = B3 THEN 190
91  IF B5 = B4 THEN 190

92  IF B4 = B1 THEN 180
93  IF B4 = B2 THEN 180
94  IF B4 = B3 THEN 180

95  IF B3 = B1 THEN 170
96  IF B3 = B2 THEN 170

97  IF B2 = B1 THEN 160

98 LET SER = SER + 1

100 PRINT #1, SER; "|";
130 PRINT #1, B1; "|";
131 PRINT #1, B2; "|";
132 PRINT #1, B3; "|";
133 PRINT #1, B4; "|";
134 PRINT #1, B5; "|";
140 PRINT #1, B1 + B2 + B3 + B4 + B5; "|"

150                     NEXT B1
160                 NEXT B2
170             NEXT B3
180         NEXT B4
190     NEXT B5
205 CLOSE
210 END
220 SYSTEM 

This, by the way, created my load file into an INFORMIX-SQL table

TABLE combos
(
seq_id SERIAL,
ball_1 SMALLINT,
ball_2 SMALLINT,
ball_3 SMALLINT,
ball_4 SMALLINT,
ball_5 SMALLINT,
sum    SMALLINT
);

I used combos.sum to generate a bell curve graph, showing the count of combinations having the same sum of each element.

解决方案

If you want to include pairs of identical numbers, e.g. (5,5):

SELECT e1.number AS number1
      ,e2.number AS number2
FROM   elements e1
      ,elements e2
WHERE  e1.number <= e2.number;

If you want to only have different numbers in each pair:

SELECT e1.number AS number1
      ,e2.number AS number2
FROM   elements e1
      ,elements e2
WHERE  e1.number < e2.number;

这篇关于在SQL中，如何生成5！56的每种可能的唯一组合?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！