使用条件列表的任何子集查询数据库

发布于 2024-11-07 00:11:44 字数 324 浏览 1 评论 0原文

我有一个包含许多列的 MySQL 数据库（例如，column0 ..column19）。

我想选择数据库中满足以下形式条件的所有行：

column0 < value0 AND column1 < value1 AND ... AND column19 < value19.

问题是我想选择满足上述任意 15 个条件的行。

我知道我可以对 15 个条件的所有可能组合执行多个查询，但我正在寻找仅包含 1 个查询的解决方案。

用MySQL可以吗？是否有另一种数据库架构允许此类查询？

谢谢

原文

I have a MySQL database with a number of columns (say column0 .. column19).

I want to select all rows of the database which satisfy a condition of the form:

column0 < value0 AND column1 < value1 AND ... AND column19 < value19.

The catch is that I want to select rows which satisfy ANY 15 conditions out of the above.

I know I can do multiple queries will all possible combinations of 15 conditions, but I'm looking for a solution with only 1 query.

Is it possible with MySQL? Is there another database architecture which allows such queries?

Thanks

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

傲鸠 2024-11-14 00:11:44

您可以使用以下内容，但不要指望它会表现得特别好！

WHERE (CASE WHEN column0 < value0 THEN 1 ELSE 0 END + 
      CASE WHEN column1 < value1 THEN 1 ELSE 0 END + 
      ... + 
      CASE WHEN column19 < value19 THEN 1 ELSE 0 END) >= 15

You could use the below, but don't expect it'll perform particularly well!

WHERE (CASE WHEN column0 < value0 THEN 1 ELSE 0 END + 
      CASE WHEN column1 < value1 THEN 1 ELSE 0 END + 
      ... + 
      CASE WHEN column19 < value19 THEN 1 ELSE 0 END) >= 15

回复收藏 0 原文

白鸥掠海 2024-11-14 00:11:44

Will的答案也可以写成：

WHERE (  SUM( column0 < value0 ) 
       + SUM( column1 < value1 ) 
       ... + 
       + SUM( column19 < value19 ) 
      ) >= 15

上面的内容仅在MYSQL中运行，不适用于其他数据库。因此，我认为使用标准 CASE WHEN 比这更好。

处理此类查询的另一种方法如下（id 是表的主键）：

SELECT * 
FROM yourTable
    JOIN
      ( SELECT id
        FROM yourTable
        WHERE column0 < value0
      UNION ALL
        SELECT id
        FROM yourTable
        WHERE column1 < value1
      UNION ALL
        ...
      UNION ALL
        SELECT id
        FROM yourTable
        WHERE column19 < value19

      GROUP BY id
      HAVING COUNT(*) >=15
      ) AS chk  
    ON chk.id = yourTable.id

如果有 20 个索引（每个 column0 一个，column1, ..., column19)，可能会更快。

Will's answer can also be written as:

WHERE (  SUM( column0 < value0 ) 
       + SUM( column1 < value1 ) 
       ... + 
       + SUM( column19 < value19 ) 
      ) >= 15

The above will work only in MYSQL, not other databases. So, I suppose it's better to use the standard CASE WHEN than this.

Another way to handle such a query would be like this (id is the primary key of the table):

SELECT * 
FROM yourTable
    JOIN
      ( SELECT id
        FROM yourTable
        WHERE column0 < value0
      UNION ALL
        SELECT id
        FROM yourTable
        WHERE column1 < value1
      UNION ALL
        ...
      UNION ALL
        SELECT id
        FROM yourTable
        WHERE column19 < value19

      GROUP BY id
      HAVING COUNT(*) >=15
      ) AS chk  
    ON chk.id = yourTable.id

If there are 20 indexes (one on every column0, column1, ..., column19), it might, might be quicker.

回复收藏 0 原文

~没有更多了~