mysql - 我需要查询以从一组数字中提供不同数量的选择

Question

我的表中有八个数字。我需要返回 28 个不同的组合。我以为我的代码是正确的，但它返回了无限多的组合。

这是代码。

SELECT t1.ID AS Number_1,
       t2.ID AS Number_2,
       t3.ID AS Number_3,
       t4.ID AS Number_4,
       t5.ID AS Number_5,
       t6.ID AS Number_6
  FROM cTop8 AS t1,
       cTop8 as t2,
       cTop8 as t3,
       cTop8 as t4,
       cTop8 as t5,
       cTop8 as t6
 where t2.id <> t1.id
   and t3.id <> t2.id
   AND t3.id <> t1.id
   and t4.id <> t3.id
   AND t4.id <> t2.id
   AND t4.id <> t1.id
   and t5.id <> t4.id
   AND t5.id <> t3.id
   AND t5.id <> t2.id
   AND t5.id <> t1.id
   and t6.id <> t5.id
   AND t6.id <> t4.id
   AND t6.id <> t3.id
   AND t6.id <> t2.id
   AND t6.id <> t1.id
 ORDER BY rand()

Answer 1

您可以使用 LIMIT 将结果限制为 28 种组合，并使用 NOT IN() 使代码更具可读性:

SELECT t1.ID AS Number_1,
       t2.ID AS Number_2,
       t3.ID AS Number_3,
       t4.ID AS Number_4,
       t5.ID AS Number_5,
       t6.ID AS Number_6
  FROM cTop8 AS t1,
       cTop8 as t2,
       cTop8 as t3,
       cTop8 as t4,
       cTop8 as t5,
       cTop8 as t6
where t1.id NOT IN(t2.id,t3.id,t4.id,t5.id,t6.id)
   AND t2.id NOT IN(t3.id,t4.id,t5.id,t6.id)
   AND t3.id NOT IN(t4.id,t5.id,t6.id)
   AND t4.id NOT IN(t5.id,t6.id)
   AND t5.id <> t6.id
ORDER BY rand() LIMIT 28;