当前位置: 首页 > 面试题库 >

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

轩辕天佑
2023-03-14
问题内容

我有三个查询结果。

查询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在这里是否正确的方法。有人知道吗?


问题答案:

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

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通过简单的减法(使用timestamptime值类似)来计算并从中提取epoch。给您秒数。除以60,您得到的分钟数要快得​​多。
79 是 _ 60 * 1.31666666666667_ 的结果。

有关提取epoch的手册:

对于timestamp with time zone值,是自1970-01-01 00:00:00
UTC以来的秒数(可以为负);对于datetimestamp 值,自当地时间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按原样使用单个查询…

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

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



 类似资料:
  • 问题内容: 我想将3个列表合并到一个列表中。例如,我有三个列表: 最后我想得到 出,, 有没有更快的方法来合并这三个列表?这是我的代码: 问题答案:

  • 我有以下表格: http://www.gulllakeschools.net/mysqltables.pdf 我需要以以下格式提取数据:学生姓、学生名、日期事件、日期事件(学生登录的每一天都有一个日期事件) 组件com_users是我需要从组件列中提取的组件。 我是mysql的菜鸟,不知道如何一次完成。我有这些单独运行良好的精选语句: 但我不能为我的生活让他们加入。我尝试过加入,加入,完全加入,合

  • 问题内容: 我正在研究别人的PHP代码,并且一遍又一遍地看到这种模式: (伪代码) 如果其他表中没有相关行,则代码需要分支,但是通过在单个SELECT语句中执行LEFT JOIN不能做到更好吗?我是否缺少一些性能优势?可移植性问题?还是我只是在挑剔? 问题答案: 没有足够的信息来真正回答问题。我曾在一些应用程序上工作过,这些应用程序出于一个原因减少查询数量而出于另一个原因增加查询数量 都 提高了性

  • 问题内容: 我要么变老,要么我需要写的查询变得越来越复杂。以下查询将获取与该用户的所有关联。 该表为(,,,); 现在,我还需要通过 独立查询如下所示: 但是,是否可以将这两个查询合并为一个查询?我的逻辑认为应该这样,尽管我不知道如何进行实际的JOIN。 问题答案: 我可能会使用UNION。

  • 到目前为止,我已经写了一份精选声明,获取球队的名称和他们在主场比赛中的平均得分,以及他们输给另一个球队的平均得分。 当他们客场时,我还有另一个,我选择了客队,以及他们客场时的平均得分和他们的平均失球数。 我如何从选择查询中连接这两个表,以便得到一个带有球队名称、主场平均得分、主场平均失球、客场平均失球和客场平均得分的表? 以下是我的代码: 任何帮助都将不胜感激。谢谢!

  • 有没有办法在2个Select语句之间应用? 我对的其他解决方案持开放态度。 示例 或 条件1/查询1 或 条件2/查询2-显示数据 注意-在子句中,两个Select具有不同的条件