有关如何在SQL表中查询数字1-69的所有可能5个数字组合的逻辑问题 [英] Trouble with logic on how to query for all possible 5 number combinations of numbers 1 - 69 into SQL table

问题描述

编辑：为了明确起见，结果的顺序仅在1,2,3,4,5和5,4,3,2,1应该是同一记录时才重要。因此，我正在寻找不同的组合。

Just to clarify, the order of the results matters only in that 1,2,3,4,5 and 5,4,3,2,1 should be the same record. So I'm looking for distinct combinations.

我正在构建一个SQL数据库，其中包含数字1-69的所有可能5个数字组合，而同一数字不能是在一个5位数组合中使用两次。结果将包含11,238,513条记录。我在编写代码时遇到麻烦，无法使所有逻辑正常工作。我曾尝试在Excel VBA中使用疯狂的宏，但我觉得SQL会变得更简单，更快捷。我已经问过类似的问题，但它也无法使我到达那里（）。如何基于基于1-69的数字列表创建5个数字排列的列表？

I'm looking to build a SQL database with all possible 5 number combinations of numbers 1 - 69 where the same number cannot be used twice in a single 5 number combination. The result will contain 11,238,513 records. I'm having trouble writing the code to get this to work correctly with all logic at play. I've attempted a crazy macro in Excel VBA but I feel like SQL would have something simpler and quicker. I've asked something similar but it also just doesn't get me there (How do I create a list of 5 number permutations based on a list of numbers ranging from 1-69?).

我尝试了以下变化，但是我似乎无法概念化如何应对这一问题。有帮助吗？

I've tried variations of below, but I just can't seem to conceptualize how I should be attacking this. Any help?

WITH range
AS (
SELECT num1 AS c FROM TEST1
  UNION  
  SELECT num2 FROM TEST1
  UNION  
  SELECT num3 FROM TEST1
  UNION  
  SELECT num4 FROM TEST1
  UNION  
  SELECT num5 FROM TEST1)
SELECT *
FROM range r1,range r2,range r3,range r4,range r5
where r1.c <> r2.c and r1.c<>r3.c and r1.c<>r4.c and r1.c<>r5.c
and r2.c <> r3.c and r2.c<>r4.c and r2.c<>r5.c
and r3.c<>r4.c and r3.c<>r5.c
and r4.c <> r5.c
order by r1.c, r2.c, r3.c, r4.c, r5.c

排序

推荐答案

对表进行笛卡尔乘积5次，并仅选择数字按升序排列的行（以确保同一数字不会被多次选择） ，并且没有相同的5个数字的排列）：

Cartesian product the table to itself 5 times, and choose only rows where the numbers are in increasing order (this ensures the same number isn't selected more than once, and that there are no permutations of the same 5 numbers):

SELECT r1.c, r2.c, r3.c, r4.c, r5.c
FROM range r1, range r2, range r3, range r4, range r5
WHERE r1.c<r2.c AND r2.c<r3.c AND r3.c<r4.c AND r4.c<r5.c

这篇关于有关如何在SQL表中查询数字1-69的所有可能5个数字组合的逻辑问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！