时间戳

首页 > 解决方案 > 我如何加入 2 个查询以显示在 1 个表中

sql-server - 我如何加入 2 个查询以显示在 1 个表中

问题描述

我有 2 个查询，它们几乎相同，它们返回相同数量的列。

我想创建一个存储过程来获取基于 2 个日期和 1 个参数的 1 个表。

select 
    Datepart(minute, created_at) as minuto,  
    avg(salida_1) as 'salida_1' 
from 
    salida_a 
where 
    created_at between '27/01/2020 04:00:00' and '27/01/2020 05:00:00' 
    and id_linea = 6 
group by 
    datepart(minute, created_at) 
order by 
    minuto       


select 
    Datepart(minute, created_at) as minuto, 
    avg(entrada_1) as 'entrada_1' 
from 
    entrada_a 
where 
    created_at between '27/01/2020 04:00:00' and'27/01/2020 05:00:00' 
    and id_linea = 6 
group by 
    datepart(minute, created_at) 
order by 
    minuto

每个查询返回

"minuto0","entrada_1"

其他查询

"minuto1","salida_1"

我想要一张这样的桌子

"minuto0","entrada_1","minuto1","salida_1"

或这个

"minuto0","entrada_1","salida_1"

帮助

标签： sql-server

解决方案

作为我的联合/条件 agg 的替代方案，您可以使用联接。这对您来说可能更容易理解：

获取您现有的查询并将它们转换为 cte：

WITH s as (
  select Datepart(minute, created_at) as minuto,  avg(salida_1) as 'salida_1' from salida_a where created_at between '27/01/2020 04:00:00' and '27/01/2020 05:00:00' and id_linea = 6 group by datepart(minute, created_at)
),      
E as (
  select Datepart(minute, created_at) as minuto, avg(entrada_1) as 'entrada_1' from entrada_a where created_at between '27/01/2020 04:00:00' and'27/01/2020 05:00:00' and id_linea = 6 group by datepart(minute, created_at)
)

SELECT 
  COALESCE(e.minuto, s.minuto) as minuto,
  S.salida_1,
  E.entrada_1
FROM
  s FULL OUTER JOIN e ON s.minuto = e.minuto
ORDER BY minuto

它接受您的两个查询并将它们连接起来，使它们在一行上，它使用 s 或 e 中的 minuto，然后使用每个表中的值。如果 s 中的 minuto 为 1，而表 e 的 minuto 不为 1，则 e 的 avg 将为空。如果您希望它改为 0，请再次使用 coalesce 来给出 0 而不是 null：

COALESCE(e.entrada_1, 0)

推荐阅读