结合3条SELECT语句输出1张表 [英] Combining 3 SELECT statements to output 1 table

查看：36 发布时间：2021/6/18 20:32:09 sql postgresql select union-all

本文介绍了结合3条SELECT语句输出1张表的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我有三个带有结果的查询.

查询 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))) 作为数字))/1, 2) AS under_over_1从工作JOIN 时间表使用 (date_linkid)JOIN client USING (clientid)WHERE employeeid = 1 AND workid <;第557话188GROUP BY employeeid, clientid ORDER BY clientid ASC;员工ID |客户编号 |under_over_1-------------+--------------+--------------1 |3 |0.541 |4 |0.471 |6 |0.451 |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))) 作为数字))/1.31666666666667, 2) AS under_over_1从工作JOIN 时间表使用 (date_linkid)JOIN client USING (clientid)员工 ID = 2GROUP BY employeeid, clientid ORDER BY clientid ASC;员工ID |客户ID |under_over_1-------------+--------------+--------------2 |2 |1.012 |3 |0.212 |4 |0.712 |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))) 作为数字))/1.31666666666667, 2) AS under_over_1从工作JOIN 时间表使用 (date_linkid)JOIN client USING (clientid)员工 ID = 3GROUP BY employeeid, clientid ORDER BY clientid ASC;员工ID |客户ID |under_over_1-------------+--------------+--------------3 |4 |0.703 |6 |0.543 |7 |1.033 |11 |0.74.|.|..|.|.

我想输出一个包含所有三个查询结果的表，例如(我很抱歉，但我必须在这里写更多，以便我可以提交这篇文章.我希望这足够了；-)):

employeeid |客户ID |under_over_1-------------+--------------+--------------1 |3 |0.541 |4 |0.471 |6 |0.451 |7 |0.59.|.|..|.|.2 |2 |1.012 |3 |0.212 |4 |0.712 |6 |0.68.|.|..|.|.3 |4 |0.703 |6 |0.543 |7 |1.033 |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))) 作为数字))/1, 2) AS under_over_1从工作JOIN 时间表使用 (date_linkid)JOIN client USING (clientid)WHERE employeeid = 1 AND workid <;第557话188GROUP BY employeeid, clientid ORDER BY clientid ASC联合所有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))) 作为数字))/1.31666666666667, 2) AS under_over_1从工作JOIN 时间表使用 (date_linkid)JOIN client USING (clientid)员工 ID = 3GROUP BY employeeid, clientid ORDER BY clientid ASC联合所有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 数字))/1.31666666666667, 2) AS under_over_1从工作JOIN 时间表使用 (date_linkid)JOIN client USING (clientid)员工 ID = 2GROUP BY employeeid, clientid ORDER BY clientid ASC;

但是，我收到以下错误:

<块引用>

错误:UNION"处或附近的语法错误第 7 行:联合所有

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

解决方案

错误的直接原因是，引用手册:

<块引用>

(ORDER BY 和 LIMIT 可以附加到子表达式，如果它是括在括号中.没有括号，这些子句将是应用于 UNION 的结果，而不是它的右侧输入表达.)

粗体强调我的.

因此，正如@wilx 所建议的，将每个 SELECT 括在括号中将解决那个.

但还有更多.

合并为单个查询

SELECT employeeid, work.clientid -- 没有 DISTINCT, 圆形 ((AVG(current_lawn_price)/AVG(extract(epoch FROM job_finish - job_start)))::numeric/79, 2) AS under_over_1从工作JOIN 时间表使用 (date_linkid)JOIN client USING (clientid)WHERE (employeeid IN (1, 2) 或员工 ID = 3AND 工作 ID <557AND 工作 ID >188)GROUP BY 员工 ID，客户 ID按情况排序员工 ID当 1 那么 1当 2 那么 3当 3 那么 2结尾，客户身份；

合并 3 个 SELECT 查询.

删除多余的DISTINCT.在 GROUP BY 之后，这是一个昂贵的空操作.

不是从开始和结束时间戳等中提取小时和分钟，而是通过简单的减法计算 interval(与 timestamp 或 time 值相同)并从中提取 epoch.给你秒数.除以 60，你得到的分钟数要快得多.79 是 60 * 1.316666666666667 的结果.

关于提取纪元的手册:

<块引用>

对于 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 查询，请改为执行此操作:

( -- 需要括号选择employeeid，work.clientid——没有DISTINCT！, 圆形 ((AVG(current_lawn_price)/AVG(extract(epoch FROM job_finish - job_start)))::numeric/79, 2) AS under_over_1从工作JOIN 时间表使用 (date_linkid)JOIN client USING (clientid)员工 ID = 1AND 工作 ID <557AND 工作 ID >188GROUP BY clientid -- 过滤单个值时无需 GROUP BY employeeid按客户编号订购)联合所有(选择雇员 ID，工作.clientid, 圆形 ((AVG(current_lawn_price)/AVG(extract(epoch FROM job_finish - job_start)))::numeric/79, 2) AS under_over_1从工作JOIN 时间表使用 (date_linkid)JOIN client USING (clientid)员工 ID = 3按客户 ID 分组按客户编号订购)联合所有(选择雇员 ID，工作.clientid, 圆形 ((AVG(current_lawn_price)/AVG(extract(epoch FROM job_finish - job_start)))::numeric/79, 2) AS under_over_1从工作JOIN 时间表使用 (date_linkid)JOIN client USING (clientid)员工 ID = 2按客户 ID 分组按客户编号订购);-- 不需要外部 ORDER BY

根据 SELECT 保留 ORDER BY 并添加括号以修复语法.UNION ALL(与 UNION 相反)只是附加结果，保留单个 SELECT 的顺序.这应该便宜比之后UNION ALL订购整套.并且您确实希望继续使用查询单个 SELECT 查询原样"；...

上面的大多数其他建议都相应地适用.

旁白:养成在连接多个表的查询中使用表别名和表限定所有列的习惯.对以后的更改更加健壮，更易于阅读/调试.

I have three queries with results.

Query 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
          . |        . |           .
          . |        . |           .

Query 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
          . |        . |           .
          . |        . |           .

Query: 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
          . |        . |           .
          . |        . |           .

I want to output one table with the result of all three queries such as (I am sorry but I have to write more here so I can submit this post. I hope this is enough;-)):

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
          . |        . |           .
          . |        . |           .

I tried UNION ALL as in

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;

However, I get the following error:

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

I am not sure why this is wrong or whether UNION ALL is the right approach here. Anyone have a clue?

解决方案

The immediate cause for the error is, quoting the manual:

(ORDER BY and LIMIT can be attached to a subexpression if it is enclosed in parentheses. Without parentheses, these clauses will be taken to apply to the result of the UNION, not to its right-hand input expression.)

Bold emphasis mine.

So, as suggested by @wilx, enclosing each SELECT in parentheses would fix that.

But there is more.

Merge into single query

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;

Merge the 3 SELECT queries.

Remove the redundant DISTINCT. That's an expensive no-op after GROUP BY.

Instead of extracting hours and minutes from both begin and end timestamps etc., compute the interval by plain subtraction (works with timestamp or time values alike) and extract the epoch from it. Gives you the number of seconds. Divide by 60 and you got the number of minutes much faster. 79 being the result of 60 * 1.31666666666667, accordingly.

The manual about extracting epoch:

For timestamp with time zone values, the number of seconds since 1970-01-01 00:00:00 UTC (can be negative); for date and timestamp values, the number of seconds since 1970-01-01 00:00:00 local time; for interval values, the total number of seconds in the interval

Bold emphasis mine.

Since this removes UNION ALL, the parentheses mentioned at the top are not needed any more.

The CASE expression makes up for the mixed order in employeeid, like sticky bit provided.

If queries cannot be merged

If, for some reason, you can't or won't merge the three original SELECT queries, do this instead:

(  -- 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

Keep ORDER BY per SELECT and add parentheses to fix the syntax. UNION ALL (as opposed to UNION) simply appends results preserving the order of individual SELECTs. This should be cheaper than ordering the whole set after UNION ALL. And you did want to keep using queries individual SELECT queries "as is" ...

Most of the other advice above applies accordingly.

Aside: make it a habit to use table aliases and table-qualify all columns in queries joining multiple tables. Much more robust against later changes and easier to read / debug.

这篇关于结合3条SELECT语句输出1张表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文

结合3条SELECT语句输出1张表 [英] Combining 3 SELECT statements to output 1 table

问题描述

合并为单个查询

如果查询不能合并

Merge into single query

If queries cannot be merged

相关文章

其他开发最新文章

热门教程

热门工具

登录关闭

结合3条SELECT语句输出1张表 [英] Combining 3 SELECT statements to output 1 table

问题描述

合并为单个查询

如果查询不能合并

Merge into single query

If queries cannot be merged

相关文章

其他开发最新文章

热门教程

热门工具

登录 关闭

登录关闭