mysql - 嵌套查询和联合分组 (SQL)

Question

我需要帮助编写查询以获取一些信息，但我在编写查询时遇到了问题。

[table_People]
int id
var name

[table_Tools]
int id
var name

[table_Activity1]
int person_id
int tool_id
date delivery_date

[table_Activity2]
int person_id
int tool_id
date installation_date

查询需要返回所有人员的列表以及他们在事件 1 或 2(两者之间发生的最近事件)中使用的最近工具的名称。

SELECT
    people.id   AS personId,
    people.name AS personName,
    (
        SELECT
            tools.name AS toolName
        FROM
            activity1
        JOIN
            tools ON tools.id=activity1.tool_id
        WHERE
            activity1.id=people.id
        UNION ALL
        SELECT
            tools.name AS toolName
        FROM
            activity2
        JOIN
            tools ON tools.id=activity2.tool_id
        WHERE
            activity2.id=people.id
        ORDER BY
            installationDate,deliveryDate
    ) AS toolName
FROM
    people
ORDER BY
    people.name
ASC

我遇到的问题是我无法按日期(交付或安装)排序，因为它们是不同的列名，因此出现错误。

Answer 1

在子查询中使用 UNION 创建派生的临时表。未选择的列不在结果集中，因此您不能对不在 SELECT 子句中的列进行排序。

使用 UNION 时，SELECT 子句中使用的第一个列名将用于结果集中(类似于别名，但您也可以使用别名)。

只需确保在 SELECT 子句中为列命名。

您还需要一个 LIMIT 子句来将子查询限制为单行:

SELECT
    people.id   AS personId,
    people.name AS personName,
    (
        SELECT
            tools.name AS toolName, delivery_date
        FROM
            activity1
        JOIN
            tools ON tools.id=activity1.tool_id
        WHERE
            activity1.id=people.id
        UNION ALL
        SELECT
            tools.name AS toolName, installation_date
        FROM
            activity2
        JOIN
            tools ON tools.id=activity2.tool_id
        WHERE
            activity2.id=people.id
        ORDER BY
            deliveryDate
        LIMIT 1
    ) AS toolName
FROM
    people
ORDER BY
    people.name
ASC

这里有一个更简单的例子来说明这个问题:

SELECT fish FROM sea
UNION
SELECT dog FROM land
ORDER BY fish

等同于:

SELECT fish AS animal FROM sea
UNION
SELECT dog AS animal FROM land
ORDER BY animal

结果被放入一个派生的临时表中，您可以随意命名列，但您使用的名字是固定的。