该表的最佳SQL语句? [英] Best SQL statement for this table?

问题描述

我有9个字段ID为F1..F8的表.

I have table with 9 fields ID, F1..F8.

一些数据的示例:

id f1 f2 f3 f4 f5 f6 f7 f8
1   1  2  3  0  0  0  0  0
2   0  1  0  3  2  0  0  0
3   4  0  5  2  1  0  0  0  
4   0  0  0  0  0  0  1  4
5   2  0  0  0  0  1  3  0
6   2  0  0  0  0  1  0  8
7   2  0  0  0  0  1  0  3
.
.
.

我怎么select * from table where F1...F8 in value (1,2,3)? 此查询的结果必须具有ID 1,2,5,7的记录.

How can I select * from table where F1...F8 in value (1,2,3)? The result of this query must have records with id 1,2,5,7.

推荐答案

对公共表表达式使用查询:

Use query with common table expression:

WITH t AS
  (
    SELECT id, f1 AS f from tbl
    UNION ALL
    SELECT id, f2 AS f from tbl
    UNION ALL
    SELECT id, f3 AS f from tbl
    UNION ALL
    SELECT id, f4 AS f from tbl
    UNION ALL
    SELECT id, f5 AS f from tbl
    UNION ALL
    SELECT id, f6 AS f from tbl
    UNION ALL
    SELECT id, f7 AS f from tbl
    UNION ALL
    SELECT id, f8 AS f from tbl
  )
SELECT
  t1.id
FROM
  t t1
  JOIN t t2 ON t2.id = t1.id
  JOIN t t3 ON t3.id = t2.id
WHERE
  t1.f IN (1, 2, 3)
  AND
  t2.f IN (1, 2, 3)
  AND
  t3.f IN (1, 2, 3)
  AND
  t1.f <> t2.f
  AND
  t2.f <> t3.f

这篇关于该表的最佳SQL语句?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！