如何将两个SQL查询合并为一个

Question

我是一位有用的助手，可以为您翻译文本。

我有两个SQL查询语句，它们分别都可以正常工作，但当我试图将它们合并时，就会出现问题。

查询 1：

SELECT Day( funct_consumatori.timestamp ) AS numar_zi, ROUND( SUM( funct_consumatori.timp_functionare ) /3600 ) AS ore_functionare, ROUND( ROUND( SUM( funct_consumatori.timp_functionare ) /3600 ) * consumatori.kwh, 3 ) AS consum, ROUND( ROUND( ROUND( SUM( funct_consumatori.timp_functionare ) /3600 ) * consumatori.kwh, 3 ) * pret_energie.pret, 2 ) AS estimare
FROM funct_consumatori
INNER JOIN consumatori ON consumatori.id = funct_consumatori.consumator
INNER JOIN pret_energie ON pret_energie.id =1
WHERE Year( funct_consumatori.timestamp ) = Year(
CURRENT_TIMESTAMP )
AND Month( funct_consumatori.timestamp ) = Month(
CURRENT_TIMESTAMP )
GROUP BY numar_zi DESC

查询2：

SELECT Day( ambienta.timestamp ) AS numar_zi, TRUNCATE( AVG( temperatura ) , 1 ) AS temp_med, MIN( temperatura ) AS temp_min, MAX( temperatura ) AS temp_max, TRUNCATE( AVG( umiditate ) , 1 ) AS umid_medie
FROM ambienta
INNER JOIN consumatori AS c2 ON c2.id = '1'
INNER JOIN pret_energie AS pe ON pe.id = '1'
WHERE Year( ambienta.timestamp ) = Year(
CURRENT_TIMESTAMP )
AND Month( ambienta.timestamp ) = Month(
CURRENT_TIMESTAMP )
GROUP BY numar_zi DESC

我该如何根据 numar_zi（INNER one）将它们统一起来？

- Marcel Domuta

---

可能是重复的问题：https://dev59.com/ymkv5IYBdhLWcg3wghIi - Galz

Answer 1

我不知道你的表格和数据，但以下SQL可能有效。

select q1.numar_zi
        ,q1.ore_functionare
        ,q1.consum
        ,q1.estimare
        -- value of q2 start 
        ,q2.numar_zi
        ,q2.temp_med
        ,q2.temp_min
        ,q2.temp_max
        ,q2.umid_medie
from 
    (
    SELECT Day( funct_consumatori.timestamp ) AS numar_zi
    , ROUND( SUM( funct_consumatori.timp_functionare ) /3600 ) AS ore_functionare
    , ROUND( ROUND( SUM( funct_consumatori.timp_functionare ) /3600 ) * consumatori.kwh, 3 ) AS consum
    , ROUND( ROUND( ROUND( SUM( funct_consumatori.timp_functionare ) /3600 ) * consumatori.kwh, 3 ) * pret_energie.pret, 2 ) AS estimare
    FROM funct_consumatori
    INNER JOIN consumatori ON consumatori.id = funct_consumatori.consumator
    INNER JOIN pret_energie ON pret_energie.id =1
    WHERE Year( funct_consumatori.timestamp ) = Year(
    CURRENT_TIMESTAMP )
    AND Month( funct_consumatori.timestamp ) = Month(
    CURRENT_TIMESTAMP )
    GROUP BY numar_zi DESC
    ) q1

INNER JOIN

    (
    SELECT Day( ambienta.timestamp ) AS numar_zi
    , TRUNCATE( AVG( temperatura ) , 1 ) AS temp_med
    , MIN( temperatura ) AS temp_min
    , MAX( temperatura ) AS temp_max
    , TRUNCATE( AVG( umiditate ) , 1 ) AS umid_medie
    FROM ambienta
    INNER JOIN consumatori AS c2 ON c2.id = '1'
    INNER JOIN pret_energie AS pe ON pe.id = '1'
    WHERE Year( ambienta.timestamp ) = Year(
    CURRENT_TIMESTAMP )
    AND Month( ambienta.timestamp ) = Month(
    CURRENT_TIMESTAMP )
    GROUP BY numar_zi DESC
    ) q2
on
    q1.numar_zi = q2.numar_zi

请根据您的关系型数据库进行更改（如果需要），但请记住，如果您想要连接两个查询，请使用以下查询语句：

select t1.Id, t1.Col1, t1.Col2, t2.Id, t2.Col1, t2.Col2 
from 
    (SELECT Id, COUNT(*) AS Col1, Col2 FROM Table2 GROUP BY Id) t1
INNER JOIN -- or, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN .... 
    (SELECT Id, COUNT(*) AS Col1, Col2 FROM Table2 GROUP BY Id) t2
on
    t1.Id= t2.Id

- RU Ahmed

---

代码运行良好，但问题在于我只有2行数据。实际上，“ambienta”表中的“INNER”查询结果为2，而“funct_consumatori”查询结果为31（当前月份的最后几天），所以我该怎么做才能获取每天完整数据表中的所有数据，并且如果在另一个表中存在同一天的数据，则将其分配给当前表？ - Marcel Domuta

没问题，我只需要使用 RIGHT JOIN 和一些小修改，非常感谢 Raihan，你有我的赞！ - Marcel Domuta