组合来自同一组数据的两个基于时间的查询

Question

我有一个数据库，其中包含两个时区中多个站点的事件，并且都报告本地时间。我需要获取最后12个小时的数据，并将其放入CSV中。我放入了一个并集，当它输出不带ORDER BY的数据时，当我用INTO OUTFILE部分运行它时，我会得到一个别名错误。我错过了什么？

(SELECT
    comp.locationid,
    comp.name,
    event.status,
    event.starttime,
    event.endtime
FROM
    comp,event
WHERE
    event.endtime >= DATE_SUB(NOW(), INTERVAL 12 HOUR)
    AND event.endtime < NOW()
    AND event.compid = comp.id
    AND comp.timezone = "EST")

UNION

(SELECT
    comp.locationid,
    comp.name,
    event.status,
    event.starttime,
    event.endtime
FROM
    comp,event
WHERE
    event.endtime >= DATE_SUB(NOW(), INTERVAL 15 HOUR)
    AND event.endtime < DATE_SUB(NOW(), INTERVAL 3 HOUR)
    AND event.compid = comp.id
    AND comp.timezone = "PST")

ORDER BY comp.locationid
INTO OUTFILE
    "report.csv"
FIELDS TERMINATED BY ','
LINES TERMINATED BY 'rn'
;

Answer 1

更新：

1. 不能在UNION的ORDER BY子句中引用特定的表名。话虽如此，只是改变

   ORDER BY comp.locationid
            ^^^^^
   

   简称

   ORDER BY locationid
   

2. 此外，您似乎最想使用UNION ALL而不是UNION。所以也要改变这一点，因为UNION需要额外的处理步骤来消除重复。

3. 删除UNION 中两个SELECT周围的括号

4. 将所有双引号更改为单引号

Answer 2

为表提供别名将有助于

(SELECT
    comp.locationid,
    comp.name,
    event.status,
    event.starttime,
    event.endtime
FROM
    comp,event
WHERE
    event.endtime >= DATE_SUB(NOW(), INTERVAL 12 HOUR)
    AND event.endtime < NOW()
    AND event.compid = comp.id
    AND comp.timezone = "EST") AS T

UNION ALL

(SELECT
    comp.locationid,
    comp.name,
    event.status,
    event.starttime,
    event.endtime
FROM
    comp,event
WHERE
    event.endtime >= DATE_SUB(NOW(), INTERVAL 15 HOUR)
    AND event.endtime < DATE_SUB(NOW(), INTERVAL 3 HOUR)
    AND event.compid = comp.id
    AND comp.timezone = "PST") AS T2
ORDER BY comp.locationid
INTO OUTFILE
    "report.csv"
FIELDS TERMINATED BY ','
LINES TERMINATED BY 'rn'
;