mysql - 选择匹配子查询的两列

Question

背景:

给定表 t1，其中包含字段 A、B(和其他字段):

DROP TEMPORARY TABLE IF EXISTS t1; 
CREATE TEMPORARY TABLE t1 (ID INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, A varchar(255), B int, C varchar(40));

INSERT INTO t1 (A, B, C)
SELECT 'AA', 11, 100
UNION ALL
SELECT 'BB', 12, 200
UNION ALL
SELECT 'BB', 12, 201
UNION ALL
SELECT 'AA', 12, 300
UNION ALL
SELECT 'AA', 11, 101;

-- ID  A  B  C
--  1  AA 11 100
--  2  BB 12 200
--  3  BB 12 201
--  4  AA 12 300
--  5  AA 11 101

<小时/>

目标:对于给定的 A 和 B 组合，检查 t1 中有多少行，然后列出所有这些行(以了解这些行之间的相同和不同之处) .

(最后，但超出了这个问题的范围，将编写查询来处理一些旧行，这些旧行被确定为“过时”(由给定的最新行替换) A 和 B)。目前，对于 A 和 B 的所有组合这样做并不安全。我无法获得关于“A 和 B 的哪些组合可以安全删除旧版本”的明确答案 - 这是一个旧表，它与许多 GB 的外部文件关联，其中大部分不再与任何人相关。所有这些文件都已备份；我需要就要删除哪些文件以及如何确定提出一个保守的建议这些文件。)

我用 A 和 B 的所有不同组合制作了临时表 t2(加上 ID，以及每个组合的行数):

DROP TEMPORARY TABLE IF EXISTS t2; 
CREATE TEMPORARY TABLE t2 (ID INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY, A varchar(255), B int)
SELECT COUNT(1) As Cnt, A, B FROM t1
GROUP BY A, B
ORDER BY Cnt DESC;

从 t2 中选择*

-- ID  Cnt  A  B
--  1    2  AA 11
--  2    2  BB 12
--  3    1  AA 12

<小时/>

我在编写时遇到问题的查询:

在实际数据中，某些组合有数百行。我对计数较高的组合最感兴趣，因此我尝试根据 t2 的第一行转储 t1 的行:

SELECT * FROM t1
WHERE A=
    (SELECT A from t2 LIMIT 1 OFFSET 0) AND
    B=
    (SELECT B from t2 LIMIT 1 OFFSET 0);

这会产生错误:

Error Code: 1137. Can't reopen table: 't2'

我认为我应该引用 t2 中我想要的行:

(SELECT A, B from t2 LIMIT 1 OFFSET 0)

然后创建一个嵌套查询，在使用列 A 和 B 的两个地方两次使用该行。我不知道如何编写这个查询。我脑子里的基本想法是:

SELECT * FROM t1
    WHERE A=t3.A AND B=t3.B IN
        (SELECT A, B from t2 LIMIT 1 OFFSET 0) AS t3;

(这不是有效的 SQL)

注意:“OFFSET 0”之所以存在，是因为我将更改为其他偏移值，以检查其他 A-B 组合。

目标是看到响应:

-- ID  A  B  C
--  1  AA 11 100
--  5  AA 11 101

<小时/>

或者也许这可以通过 JOIN 来完成，但我不确定如何仅使用一行 t2 来完成 JOIN。

Answer 1

你可以这样做:

  SELECT t1.*
    FROM t1
    JOIN ( SELECT t2.A, t2.B
             FROM t2
            ORDER BY t2.A, t2.B
            LIMIT 1 OFFSET 0
         ) t3
   WHERE t3.A = t1.A
     AND t3.B = t1.B

如果没有ORDER BY子句，MySQL可以自由返回任何行。我们需要添加 ORDER BY 以使结果具有确定性。