mysql - 从 SELECT 创建的临时表中的多行中选择数据

Question

我有以下 SQL 查询，但不知何故损坏了:

SELECT *
FROM (
    SELECT ID, TEST, CHR, NUMBER
    FROM Test_Table 
    JOIN ...
    WHERE ...
) TEMP_TABLE
FROM TEMP_TABLE a
LEFT
JOIN TEMP_TABLE b
  ON b.test = a.test 
 AND b.chr = 'x'
WHERE a.number IN (5,6)
 AND b.id IS NULL
GROUP
  BY a.test
HAVING COUNT(*) = 2;

从第一个 FROM 语句我得到以下临时表:

 ID    , TEST,  CHR   ,  NUMBER
 ------------------------------
( 1    ,    7 , 'C'   ,       5),
( 2    ,    7 , 'T'   ,       6),
( 3    ,    8 , 'C'   ,       4),
( 4    ,    8 , 'T'   ,       5),
( 5    ,    9 , 'A'   ,       4),
( 6    ,    9 , 'G'   ,       5),
( 7    ,   10 , 'T'   ,       4),
( 8    ,   10 , 'A'   ,       5),
( 9    ,   10 , 'X'   ,       6),
(10    ,   14 , 'T'   ,       4),
(11    ,   14 , 'G'   ,       5);

从 FROM TEMP_TABLE ... 我尝试实现以下条件:

1. 例如 test 第 7 列包含两行，如果 number 列包含值 5 和 6，并且该值不是 X 在 chr 列中，我想选择在 test 列中选择带有 7 的行。

2. 例如 test 第 10 列包含三行，如果 number 列包含值 5 和 6，并且值 X存在于 chr 列中，我想排除 test 列中包含 10 的行。

结果它应该只是带有 7 的 test 列，因为 test 列 7 在 number 列中有 5 和 6 而不是 X .

结果示例:

ID | TEST | CHR  |  NUMBER               
1  |  7   |  C   |    5        
2  |  7   |  T   |    6   

上面的 SQL 查询出了什么问题？

Answer 1

您的查询有两个 FROM 子句删除一个并像下面这样尝试

SELECT *
FROM (
    SELECT ID, TEST, CHR, NUMBER
    FROM Test_Table 
    JOIN ...
    WHERE ...
)  a
LEFT JOIN 
 (
    SELECT ID, TEST, CHR, NUMBER
    FROM Test_Table 
    JOIN ...
    WHERE ...
)  b
  ON b.test = a.test 
 AND b.chr = 'x'
WHERE a.number IN (5,6)
 AND b.id IS NULL
GROUP
  BY a.test
HAVING COUNT(*) = 2;

一个更简单的方法是使用 TEMPORARY 关键字创建一个 TEMPORARY 表，如提到的 here

CREATE TEMPORARY TABLE TEMP_TABLE (ID int, TEST varchar(100), CHR char, NUMBER int ); 

INSERT INTO TEMP_TABLE
 (SELECT ID, TEST, CHR, NUMBER
FROM Test_Table 
JOIN ...
WHERE ...);


SELECT *
FROM TEMP_TABLE a
LEFT
JOIN TEMP_TABLE b
  ON b.test = a.test 
 AND b.chr = 'x'
WHERE a.number IN (5,6)
 AND b.id IS NULL
GROUP
  BY a.test
HAVING COUNT(*) = 2;