在所有组合sql中用6个数字查询6个不同的列

Question

我有一个包含6列的数据库，n1-n6。每行都有一个数字，所以每行有6个不同的数字。当我查询时，我会对照数据库中的每一行检查我必须提交的6个数字。我需要查询来更改组合，因此理论上可以完成36个查询。

因此，一个示例数据集可能是

N1  N2  N3  N4  N5  N6
1   12  54  36  17  23

我要查询的号码是

1 54 36 17 23 12

所以它们是相同的，但因为它们的顺序不同，所以不会返回结果。所有6个必须匹配才能返回正。

示例查询：

$sql = "SELECT * FROM numbers WHERE n1 = :n1 AND n2 = :n2 AND n3 = :n3 AND n4 = :n4 AND n5 = :n5 AND n6 = :n6";
$stmt = $conn->prepare($sql);
$stmt->bindParam(":n1", $n1);
$stmt->bindParam(":n2", $n2);
$stmt->bindParam(":n3", $n3);
$stmt->bindParam(":n4", $n4);
$stmt->bindParam(":n5", $n5);
$stmt->bindParam(":n6", $n6);
$stmt->execute();

我可以用36个不同的查询来完成这项工作，但能够在一个查询中完成这项任务会更有用。

Answer 1

嗯。一行存储六个数字听起来像是一种糟糕的数据格式。如果这些是行而不是列，那么会更容易。

然而，假设没有重复，你可以做：

SELECT *
FROM numbers
WHERE n1 IN (:n1, :n2, :n3, :n4, :n5, :n6) AND
      n2 IN (:n1, :n2, :n3, :n4, :n5, :n6) AND
      n3 IN (:n1, :n2, :n3, :n4, :n5, :n6) AND
      n4 IN (:n1, :n2, :n3, :n4, :n5, :n6) AND
      n5 IN (:n1, :n2, :n3, :n4, :n5, :n6) AND
      n6 IN (:n1, :n2, :n3, :n4, :n5, :n6);

Answer 2

CREATE TABLE YourTable (n1 INT, n2 INT, n3 INT, n4 INT, n5 INT, n6 INT);
INSERT INTO YourTable (n1,n2,n3,n4,n5,n6)
VALUES (1,12,54,36,17,23);
CREATE TABLE ValuesTable (Id INT NOT NULL AUTO_INCREMENT,Value INT, primary key(id));
INSERT INTO ValuesTable (Value) VALUES (1),(12),(54),(36),(17),(23);
SELECT t.*
FROM
    YourTable t
    INNER JOIN (
       SELECT
          v1.Value as n1
          ,v2.Value as n2
          ,v3.Value as n3
          ,v4.Value as n4
          ,v5.Value as n5
          ,v6.Value as n6
       FROm
          ValuesTable v1
          INNER JOIN ValuesTable v2
          ON v1.Id <> v2.Id
          INNER JOIN ValuesTable v3
          ON v1.Id <> v3.Id
          AND v2.Id <> v3.Id
          INNER JOIN ValuesTable v4
          ON v1.Id <> v4.Id
          AND v2.Id <> v4.Id
          AND v3.Id <> v4.Id
          INNER JOIN ValuesTable v5
          ON v1.Id <> v5.Id
          AND v2.Id <> v5.Id
          AND v3.Id <> v5.Id
          AND v4.Id <> v5.Id
          INNER JOIN ValuesTable v6
          ON v1.Id <> v6.Id
          AND v2.Id <> v6.Id
          AND v3.Id <> v6.Id
          AND v4.Id <> v6.Id
          AND v5.Id <> v6.Id
    ) v
    ON t.n1 = v.n1
    AND t.n2 = v.n2
    AND t.n3 = v.n3
    AND t.n4 = v.n4
    AND t.n5 = v.n5
    AND t.n6 = v.n6
;

通过将6个数字添加到表/临时表中，并使用6个内部联接自动递增，您可以生成所有排列(6个阶乘或720个组合(，然后内部联接到表中，获得所需结果。通过使用Id字段，它将允许在列表中重复数字，例如1、12、12。。。。

另一种选择是通过将所有列合并为一列来重新排序表，然后您可以加入一个包含6个值和计数的临时表，以确保6个值已加入，或者您可以对列进行重新排序，并使用条件聚合再次生成列，并测试与列表中相同的顺序。

Answer 3

假设您可能有重复项，并且您有一个主键(以下示例中的id(：

select a.* from numbers a
inner join 
(select id, group_concat(n order by n asc separator ' ') as n from (
  select id, n1 as n from numbers
  union all
  select id, n2 as n from numbers
  union all
  select id, n3 as n from numbers
  union all
  select id, n4 as n from numbers
  union all
  select id, n5 as n from numbers
  union all
  select id, n6 as n from numbers
) b group by id) c on a.id = c.id 
where c.n = '1 12 17 23 36 54';

唯一的要求是您正在查询的数字以升序连接到一个字符串中。

SQL Fiddle 中的示例

Answer 4

这里有一个简单的逻辑，使用带有count检查的UNION ALL运算符。

考虑下表包含数据

IF OBJECT_ID('dbo.Table1', 'U') IS NOT NULL 
DROP TABLE dbo.Table1;
CREATE TABLE dbo.Table1 (n1 INT, n2 INT, n3 INT, n4 INT, n5 INT, n6 INT);
INSERT INTO Table1 (n1,n2,n3,n4,n5,n6)
VALUES (13,12,54,34,17,23);
INSERT INTO Table1 (n1,n2,n3,n4,n5,n6)
VALUES (1,12,54,36,17,23);

这是您的输入，我们将在表中检查的数据。

IF OBJECT_ID('tempdb.dbo.#MyInput', 'U') IS NOT NULL
DROP TABLE #MyInput; 
CREATE TABLE dbo.#MyInput (Id INT NOT NULL identity(1,1),Value INT, primary key(id));
INSERT INTO #MyInput (Value) VALUES (12),(13),(54),(36),(17),(23);

关键的一步来了。您必须对基表中的所有列应用UNIONALL运算符，即具有行号的Table1。

IF OBJECT_ID('tempdb.dbo.#NumberedTable1','U') IS NOT NULL
DROP TABLE #NumberedTable1
GO
SELECT ROW_NUMBER()OVER(ORDER BY n1) Rno, n1 value
 INTO #NumberedTable1
FROM Table1
UNION ALL
SELECT ROW_NUMBER()OVER(ORDER BY n1) Rno, n2 
FROM Table1
UNION ALL
SELECT ROW_NUMBER()OVER(ORDER BY n1) Rno, n3
FROM Table1
UNION ALL
SELECT ROW_NUMBER()OVER(ORDER BY n1) Rno, n4
FROM Table1
UNION ALL
SELECT ROW_NUMBER()OVER(ORDER BY n1) Rno, n5
FROM Table1
UNION ALL
SELECT ROW_NUMBER()OVER(ORDER BY n1) Rno, n6
FROM Table1

下面的循环将检查输入记录是否与表中存储的数字匹配。如果与任何存储的数据匹配，您将获得打印结果。您可以根据需要更改输出模式..：(

DECLARE @MaxLoop int
SELECT @MaxLoop=MAX(Rno)
FROM #NumberedTable1
WHILE (@MaxLoop>=1)
BEGIN
IF (SELECT COUNT(1)
        FROM #NumberedTable1 t
            JOIN #MyInput b ON t.Rno=@MaxLoop and t.value=b.Value)=6 -- SPECIFY THE COLUMN COUNT 
    BEGIN
    Print'Combination exists';
    BREAK;
    END
 ELSE
  BEGIN
   SET @MaxLoop=@MaxLoop-1
    IF @MaxLoop=-0
    Print'Combination does not exists'
   END
 END

快乐编码..：(