mysql - 具有连接子查询的列范围

Question

我必须选择一个计数列，按两个来源的日期分组。我将结果集作为子查询加入。然而，结果是假的。据我所知，问题与 JOIN .. ON 子句有关。这个查询工作正常:

SELECT id
FROM pu a 
LEFT JOIN (
    SELECT 
        COUNT(pd.id) AS c_id1, 
        NULL AS c_id2,
        LEFT(pd.start_date, 10) AS date,
        pd.pid 
    FROM 
        p_d pd
    **WHERE pd.pid = 111**
    GROUP BY date

    UNION 

    SELECT 
        NULL AS c_id1,
        COUNT(pd.id) AS c_id2,
        LEFT(pd.inactivation_date, 10) AS date, 
        pd.pid
    FROM 
        p_d pd
    **WHERE pd.pid = 111**
    GROUP BY date
) x
ON x.pid = a.id;

但是这个(没有 WHERE 子句)返回一个错误的结果集:

SELECT id
FROM pu a 
LEFT JOIN (
    SELECT 
        COUNT(pd.id) AS c_id1, 
        NULL AS c_id2,
        LEFT(pd.start_date, 10) AS date,
        pd.pid 
    FROM 
        p_d pd
    GROUP BY date

    UNION 

    SELECT 
        NULL AS c_id1,
        COUNT(pd.id) AS c_id2,
        LEFT(pd.inactivation_date, 10) AS date, 
        pd.pid
    FROM 
        p_d pd
    GROUP BY date
) x
ON x.pid = a.id;

是否可以在连接的子查询中使用a.id？现在是“未知列”。

Answer 1

在您的子查询中，您使用诸如 pd.pid 之类的列来表示 SELECT，这些列不属于 GROUP BY 的一部分，并且不会聚合。这些列被称为“隐藏”，在标准 SQL 中这会产生语法错误，但 mysql 允许这样做，尽管可以自由地从每个组中的任何行中选择值。

如果您使用 WHERE pd.pid = 111 限制您的集合，则该组中的所有 pd.pid 值都将相同，因此使用哪一行来获取它并不重要，但是如果没有WHERE，pd.pid 的值将是未定义的(mysql 可能会选择可以最快获取到的那个)。您还可以使用未定义的 pid 进行 JOIN，因此您一定会得到错误的结果。

http://dev.mysql.com/doc/refman/5.6/en/group-by-hidden-columns.html

但是很难说您应该如何重写查询，因为您没有提供有关表架构的足够信息，您想要实现什么以及表/列名称的含义是什么。