合并3个SELECT语句以输出1个表

小编典典

合并3个SELECT语句以输出1个表

sql

我有三个查询结果。

查询1：

SELECT DISTINCT employeeid, work.clientid, ROUND ((CAST (AVG(current_lawn_price) AS numeric) / CAST (AVG((((EXTRACT(HOUR FROM job_finish)*60) + EXTRACT(MIN FROM job_finish))) - ((EXTRACT(HOUR FROM job_start)*60) + EXTRACT(MIN FROM job_start))) AS numeric)) / 1, 2) AS under_over_1
FROM work
JOIN timesheet USING (date_linkid)
JOIN client USING (clientid)
WHERE employeeid = 1 AND workid < 557 AND workid > 188
GROUP BY employeeid, clientid ORDER BY clientid ASC;

employeeid | clientid | under_over_1
------------+----------+--------------
          1 |        3 |         0.54
          1 |        4 |         0.47
          1 |        6 |         0.45
          1 |        7 |         0.59
          . |        . |           .
          . |        . |           .

查询2：

SELECT DISTINCT employeeid, work.clientid, ROUND ((CAST (AVG(current_lawn_price) AS numeric) / CAST (AVG((((EXTRACT(HOUR FROM job_finish)*60) + EXTRACT(MIN FROM job_finish))) - ((EXTRACT(HOUR FROM job_start)*60) + EXTRACT(MIN FROM job_start))) AS numeric)) / 1.31666666666667, 2) AS under_over_1
FROM work
JOIN timesheet USING (date_linkid)
JOIN client USING (clientid)
WHERE employeeid = 2
GROUP BY employeeid, clientid ORDER BY clientid ASC;

 employeeid | clientid | under_over_1
------------+----------+--------------
          2 |        2 |         1.01
          2 |        3 |         0.21
          2 |        4 |         0.71
          2 |        6 |         0.68
          . |        . |           .
          . |        . |           .

查询：3

SELECT DISTINCT employeeid, work.clientid, ROUND ((CAST (AVG(current_lawn_price) AS numeric) / CAST (AVG((((EXTRACT(HOUR FROM job_finish)*60) + EXTRACT(MIN FROM job_finish))) - ((EXTRACT(HOUR FROM job_start)*60) + EXTRACT(MIN FROM job_start))) AS numeric)) / 1.31666666666667, 2) AS under_over_1
FROM work
JOIN timesheet USING (date_linkid)
JOIN client USING (clientid)
WHERE employeeid = 3
GROUP BY employeeid, clientid ORDER BY clientid ASC;

 employeeid | clientid | under_over_1
------------+----------+--------------
          3 |        4 |         0.70
          3 |        6 |         0.54
          3 |        7 |         1.03
          3 |       11 |         0.74
          . |        . |           .
          . |        . |           .

我想输出一个表，其中包含所有三个查询的结果，例如（很抱歉，但我必须在这里写更多内容，以便我可以提交此帖子。我希望这足够;-)）：

employeeid | clientid | under_over_1
------------+----------+--------------
          1 |        3 |         0.54
          1 |        4 |         0.47
          1 |        6 |         0.45
          1 |        7 |         0.59
          . |        . |           .
          . |        . |           .
          2 |        2 |         1.01
          2 |        3 |         0.21
          2 |        4 |         0.71
          2 |        6 |         0.68
          . |        . |           .
          . |        . |           .
          3 |        4 |         0.70
          3 |        6 |         0.54
          3 |        7 |         1.03
          3 |       11 |         0.74
          . |        . |           .
          . |        . |           .

我尝试UNION ALL了

SELECT DISTINCT employeeid, work.clientid, ROUND ((CAST (AVG(current_lawn_price) AS numeric) / CAST (AVG((((EXTRACT(HOUR FROM job_finish)*60) + EXTRACT(MIN FROM job_finish))) - ((EXTRACT(HOUR FROM job_start)*60) + EXTRACT(MIN FROM job_start))) AS numeric)) / 1, 2) AS under_over_1
FROM work
JOIN timesheet USING (date_linkid)
JOIN client USING (clientid)
WHERE employeeid = 1 AND workid < 557 AND workid > 188
GROUP BY employeeid, clientid ORDER BY clientid ASC

UNION ALL

SELECT DISTINCT employeeid, work.clientid, ROUND ((CAST (AVG(current_lawn_price) AS numeric) / CAST (AVG((((EXTRACT(HOUR FROM job_finish)*60) + EXTRACT(MIN FROM job_finish))) - ((EXTRACT(HOUR FROM job_start)*60) + EXTRACT(MIN FROM job_start))) AS numeric)) / 1.31666666666667, 2) AS under_over_1
FROM work
JOIN timesheet USING (date_linkid)
JOIN client USING (clientid)
WHERE employeeid = 3
GROUP BY employeeid, clientid ORDER BY clientid ASC

UNION ALL

SELECT DISTINCT employeeid, work.clientid, ROUND ((CAST (AVG(current_lawn_price) AS numeric) / CAST (AVG((((EXTRACT(HOUR FROM job_finish)*60) + EXTRACT(MIN FROM job_finish))) - ((EXTRACT(HOUR FROM job_start)*60) + EXTRACT(MIN FROM job_start))) AS numeric)) / 1.31666666666667, 2) AS under_over_1
FROM work
JOIN timesheet USING (date_linkid)
JOIN client USING (clientid)
WHERE employeeid = 2
GROUP BY employeeid, clientid ORDER BY clientid ASC;

但是，出现以下错误：

ERROR:  syntax error at or near "UNION"
LINE 7: UNION ALL

我不确定为什么这是错误的，或者UNION ALL在这里是否正确的方法。有人知道吗？

阅读 299

2021-04-14

共1个答案

小编典典

导致错误的直接原因是引用手册：

（ORDER BY并且LIMIT可以连接到如果一个子表达式 括在括号中
。如果没有括号，这些条款将被视为适用于的结果UNION，而不是它的右手输入表达式。）

大胆强调我的。

因此，按照@wilx的建议，将每个SELECT括号括起来即可解决 该问题 。

但是还有更多。

合并为单个查询

SELECT employeeid, work.clientid    -- no DISTINCT
     , ROUND ((AVG(current_lawn_price)
             / AVG(extract(epoch FROM job_finish - job_start)))::numeric / 79, 2) AS under_over_1
FROM   work
JOIN   timesheet USING (date_linkid)
JOIN   client    USING (clientid)
WHERE (employeeid IN (1, 2) OR
       employeeid = 3
   AND workid < 557
   AND workid > 188)
GROUP  BY employeeid, clientid 
ORDER  BY CASE employeeid
            WHEN 1 THEN 1
            WHEN 2 THEN 3
            WHEN 3 THEN 2
          END
        , clientid;

合并3个SELECT查询。

删除冗余的DISTINCT。之后那是昂贵的无人值守GROUP BY。

与其从开始时间戳和结束时间戳等中提取小时和分钟，不如interval通过简单的减法（使用timestamp或time值类似）来计算并从中提取epoch。给您秒数。除以60，您得到的分钟数要快得多。
79 是 _ 60 * 1.31666666666667_ 的结果。

有关提取epoch的手册：

对于timestamp with time zone值，是自1970-01-01 00:00:00
UTC以来的秒数（可以为负）；对于date和timestamp 值，自当地时间1970-01-01 00:00:00以来的秒数；
对于interval值，间隔中的总秒数

大胆强调我的。

由于此删除UNION ALL，不再需要在顶部提到的括号。

该CASE表达式弥补了混合顺序employeeid，如提供的粘滞位。

如果查询无法合并

如果由于某种原因您不能或不会合并三个原始SELECT查询，请执行以下操作：

(  -- parentheses required
SELECT employeeid, work.clientid    -- no DISTINCT !
     , ROUND ((AVG(current_lawn_price)
             / AVG(extract(epoch FROM job_finish - job_start)))::numeric / 79, 2) AS under_over_1
FROM   work
JOIN   timesheet USING (date_linkid)
JOIN   client    USING (clientid)
WHERE  employeeid = 1
AND    workid < 557 
AND    workid > 188
GROUP  BY clientid  -- no need to GROUP BY employeeid while filtering single value
ORDER  BY clientid
)

UNION ALL
(
SELECT employeeid, work.clientid
     , ROUND ((AVG(current_lawn_price)
             / AVG(extract(epoch FROM job_finish - job_start)))::numeric / 79, 2) AS under_over_1
FROM   work
JOIN   timesheet USING (date_linkid)
JOIN   client    USING (clientid)
WHERE  employeeid = 3
GROUP  BY clientid
ORDER  BY clientid
)

UNION ALL
(
SELECT employeeid, work.clientid
     , ROUND ((AVG(current_lawn_price)
             / AVG(extract(epoch FROM job_finish - job_start)))::numeric / 79, 2) AS under_over_1
FROM   work
JOIN   timesheet USING (date_linkid)
JOIN   client    USING (clientid)
WHERE  employeeid = 2
GROUP  BY clientid
ORDER  BY clientid
);
-- no outer ORDER BY required

保持ORDER BY每个SELECT并添加括号来解决语法。UNION ALL（与相对UNION）只是附加结果，以保持单个SELECTs的顺序。这应该是 _ 便宜 比订购整套 _之后 UNION ALL。而且您确实想继续SELECT按原样使用单个查询…

上面的大多数其他建议也适用。

撇开： 养成使用表别名和对连接多个表的查询中的所有列进行表限定的习惯。对以后的更改更强大，并且更易于阅读/调试。

2021-04-14