php - 查询带数据昼夜sql

Question

我有以下查询，其中包含特定区域每分钟的温度和相对湿度信息:

SELECT plands.land AS Land,
   ROUND(AVG(meteorology.temperature),2) AS Temperatureday,
   ROUND(AVG(meteorology.humidity),2) AS Humidityday,
   WEEKOFYEAR(meteorology.date) AS Week
FROM meteorology
INNER JOIN plands ON plands.id = meteorology.id_land
WHERE DATE(meteorology.date) BETWEEN '2016-09-12' AND '2016-09-23'
  AND TIME(meteorology.date) >= '06:00:00'
  AND TIME(meteorology.date) <= '18:00:00'
GROUP BY meteorology.id_land,
         WEEKOFYEAR(meteorology.date),
         YEAR(meteorology.date)
UNION ALL
SELECT plands.land AS Land,
       ROUND(AVG(meteorology.temperature),2) AS TemperatureNight,
       ROUND(AVG(meteorology.humidity),2) AS HumidityNight,
       WEEKOFYEAR(meteorology.date) AS Week
FROM meteorology
INNER JOIN plands ON plands.id = meteorology.id_land
WHERE DATE(meteorology.date) BETWEEN '2016-09-12' AND '2016-09-23'
  AND (TIME(meteorology.date) < '06:00:00'
       OR TIME(meteorology.date) > '18:00:00')
GROUP BY meteorology.id_land,
         WEEKOFYEAR(meteorology.date),
         YEAR(meteorology.date)

显示如下:

----------------------------------------------------------
  Land    |    Temperatureday  |   Humidityday  |  Week  |
----------------------------------------------------------
 LAND1    |         16.13      |     92.03      |  37    |
----------------------------------------------------------
 LAND1    |         16.46      |     84.35      |  38    | 
----------------------------------------------------------
 LAND1    |         9.37       |     99.0       |  37    |
----------------------------------------------------------
 LAND1    |         9.95       |     99.0       |  38    | 
----------------------------------------------------------

但我要显示的数据如下:

------------------------------------------------------------------------------
  Land | Temperatureday  | Humidityday |TemperatureNight | HumidityNigh |Week|
------------------------------------------------------------------------------
 LAND1 |   16.13         |   92.03     |     9.37        |     99.0     | 37 |
------------------------------------------------------------------------------
 LAND1 |   16.46         |   84.35     |     9.95        |     99.0     | 38 | 
------------------------------------------------------------------------------

我知道我的查询是错误的，但我不知道我在做什么。

谢谢!

Answer 1

白天数据的子查询，夜间数据加入

连接数据的另一种方法涉及白天数据的子查询，您可以通过第二个外部查询JOIN 夜间数据作为附加详细信息。这适用于将子查询具体化为临时表的查询引擎，例如 MySQL。

SELECT wxday.Land, Temperatureday, Humidityday,
       ROUND(AVG(meteorology.temperature),2) AS TemperatureNight,
       ROUND(AVG(meteorology.humidity),2) AS HumidityNight,
       wxday.Week
FROM (
  SELECT plands.land AS Land,
     ROUND(AVG(meteorology.temperature),2) AS Temperatureday,
     ROUND(AVG(meteorology.humidity),2) AS Humidityday,
     WEEKOFYEAR(meteorology.date) AS Week,
     YEAR(meteorology.date) AS Year
  FROM meteorology
  INNER JOIN plands ON plands.id = meteorology.id_land
  WHERE DATE(meteorology.date) BETWEEN '2016-09-12' AND '2016-09-23'
    AND TIME(meteorology.date) >= '06:00:00'
    AND TIME(meteorology.date) <= '18:00:00'
  GROUP BY meteorology.id_land,
           WEEKOFYEAR(meteorology.date),
           YEAR(meteorology.date)
) AS wxday
INNER JOIN meteorology
ON (wxday.Land = meteorology.id_land
    AND WEEKOFYEAR(meteorology.date) = wxday.Week
    AND YEAR(meteorology.date) = wxday.Year)
WHERE DATE(meteorology.date) BETWEEN '2016-09-12' AND '2016-09-23'
  AND (TIME(meteorology.date) < '06:00:00'
       OR TIME(meteorology.date) > '18:00:00')
GROUP BY wxday.Land, wxday.Week, wxday.Year

具有不同非空列的两个表的联合

第二种方法保留 UNION，将日夜数据放在单独的输出列中，然后使用 MIN 将它们合并。这是有效的，因为具有一个非空值和一个 NULL 的组的 MIN 是一个非空值。在某些星期有夜间数据但没有白天数据或反之亦然的情况下，它可能会更好地工作:

SELECT Land,
       MIN(Temperatureday) AS Temperatureday,
       MIN(Humidityday) AS Humidityday,
       MIN(TemperatureNight) AS TemperatureNight,
       MIN(HumidityNight) AS HumidityNight,
       Week
FROM (
  SELECT plands.land AS Land,
     ROUND(AVG(meteorology.temperature),2) AS Temperatureday,
     ROUND(AVG(meteorology.humidity),2) AS Humidityday,
     NULL AS TemperatureNight,
     NULL AS HumidityNight,
     WEEKOFYEAR(meteorology.date) AS Week,
     YEAR(meteorology.date) AS Year
  FROM meteorology
  INNER JOIN plands ON plands.id = meteorology.id_land
  WHERE DATE(meteorology.date) BETWEEN '2016-09-12' AND '2016-09-23'
    AND TIME(meteorology.date) >= '06:00:00'
    AND TIME(meteorology.date) <= '18:00:00'
  GROUP BY meteorology.id_land,
           WEEKOFYEAR(meteorology.date),
           YEAR(meteorology.date)
  UNION ALL
  SELECT plands.land AS Land,
         NULL AS TemperatureNight,
         NULL AS HumidityNight,
         ROUND(AVG(meteorology.temperature),2) AS TemperatureNight,
         ROUND(AVG(meteorology.humidity),2) AS HumidityNight,
         WEEKOFYEAR(meteorology.date) AS Week,
         YEAR(meteorology.date) AS Year
  FROM meteorology
  INNER JOIN plands ON plands.id = meteorology.id_land
  WHERE DATE(meteorology.date) BETWEEN '2016-09-12' AND '2016-09-23'
    AND (TIME(meteorology.date) < '06:00:00'
         OR TIME(meteorology.date) > '18:00:00')
  GROUP BY meteorology.id_land,
           WEEKOFYEAR(meteorology.date),
           YEAR(meteorology.date)
) AS itsownalias
GROUP BY Land, Week, Year

使用条件 null 从平均值中排除

第三种方法通过数据传递并使用 AVG，依赖于其相同的 NULL 跳过行为。这也可以在没有子查询的情况下表达，但是 IsDay 使其更具可读性并且更符合 "Don't repeat yourself" principle .

SELECT Land,
       ROUND(AVG(CASE IsDay WHEN 0 THEN NULL ELSE temperature END),2) AS Temperatureday,
       ROUND(AVG(CASE IsDay WHEN 0 THEN NULL ELSE humidity END),2) AS Humidityday,
       ROUND(AVG(CASE IsDay WHEN 0 THEN temperature ELSE NULL END),2) AS TemperatureNight,
       ROUND(AVG(CASE IsDay WHEN 0 THEN humidity ELSE NULL END),2) AS HumidityNight,
       WEEKOFYEAR(meteorology.date) AS Week
FROM (
  SELECT plands.land AS Land,
     meteorology.temperature,
     meteorology.humidity,
     WEEKOFYEAR(meteorology.date) AS Week,
     YEAR(meteorology.date) AS Year,
     CASE WHEN TIME(meteorology.date) >= '06:00:00'
               AND TIME(meteorology.date) <= '18:00:00'
     THEN 1 ELSE 0 END AS IsDay
  FROM meteorology
  INNER JOIN plands ON plands.id = meteorology.id_land
  WHERE DATE(meteorology.date) BETWEEN '2016-09-12' AND '2016-09-23'
) AS itsownalias
GROUP BY Land,
         WEEKOFYEAR(meteorology.date),
         YEAR(meteorology.date)