DB-MYSQL查询帮助，用于选择在何处

debugcn 发表于 Dev

彼得·H

我创建了一个名为郊区_温度的表，该表基本上包含如下的郊区名称，日期和温度。

CREATE TABLE `test`.`suburb_temperature` (
  `idsuburb_temperature` INT NOT NULL AUTO_INCREMENT,
  `suburb_name` VARCHAR(100) NOT NULL,
  `time_value` DATETIME NOT NULL,
  `degrees` DOUBLE NOT NULL,
  PRIMARY KEY (`idsuburb_temperature`),
  INDEX `suburb_temperature_idx1` (`suburb_name` ASC, `time_value` ASC, `degrees` ASC));

然后，我向该表中插入了一些数据。

INSERT INTO `test`.`suburb_temperature` (`suburb_name`, `time_value`, `degrees`) VALUES ('SYDNEY', '2015-06-16', '11.5');
INSERT INTO `test`.`suburb_temperature` (`suburb_name`, `time_value`, `degrees`) VALUES ('SYDNEY', '2015-06-17', '12.5');
INSERT INTO `test`.`suburb_temperature` (`suburb_name`, `time_value`, `degrees`) VALUES ('ULTIMO', '2015-06-16', '11');
INSERT INTO `test`.`suburb_temperature` (`suburb_name`, `time_value`, `degrees`) VALUES ('ULTIMO', '2015-06-17', '11.9');
INSERT INTO `test`.`suburb_temperature` (`suburb_name`, `time_value`, `degrees`) VALUES ('PYRMONT', '2015-06-16', '12.4');
INSERT INTO `test`.`suburb_temperature` (`suburb_name`, `time_value`, `degrees`) VALUES ('PYRMONT', '2015-06-17', '12.8');
INSERT INTO `test`.`suburb_temperature` (`suburb_name`, `time_value`, `degrees`) VALUES ('RIVERWOOD', '2015-06-16', '13.1');
INSERT INTO `test`.`suburb_temperature` (`suburb_name`, `time_value`, `degrees`) VALUES ('RIVERWOOD', '2015-06-17', '12.7');

我想找到的是郊区的平均温度，最低温度和最高温度。

我创建了一个在下面运行的查询，尽管问题是表中不存在的郊区没有返回任何结果。EG在下面的查询中，我输入HAYMARKET作为不存在的郊区名称。没有返回带有郊区名称HAYMARKET的结果。我需要查看HAYMARKET在返回结果中的最小值，最大值和平均值为零。有谁知道如何有效地做到这一点？

SELECT suburb_name,
IFNULL(ROUND(avg(suburb_temperature.degrees),2),0) as 'SUBURB_AVG_TEMP_DEGREES_CELCIUS',
IFNULL(ROUND(min(suburb_temperature.degrees),2),0) as 'SUBURB_MIN_TEMP_DEGREES_CELCIUS',
IFNULL(ROUND(max(suburb_temperature.degrees),2),0) as 'SUBURB_MAX_TEMP_DEGREES_CELCIUS'
FROM suburb_temperature
WHERE suburb_name IN('HAYMARET','PYRMONT','RIVERWOOD','SYDNEY','ULTIMO')
GROUP BY suburb_name
ORDER BY suburb_name asc

以上查询的结果是：

潘卡巴卡（Venkatesh Panabaka）

我根据您的要求修改了您的SQL。

 SELECT b.suburb_name,
IFNULL(ROUND(AVG(a.degrees),2),0) AS 'SUBURB_AVG_TEMP_DEGREES_CELCIUS',
IFNULL(ROUND(MIN(a.degrees),2),0) AS 'SUBURB_MIN_TEMP_DEGREES_CELCIUS',
IFNULL(ROUND(MAX(a.degrees),2),0) AS 'SUBURB_MAX_TEMP_DEGREES_CELCIUS'
FROM suburb_temperature AS a RIGHT JOIN  (SELECT 'HAYMARET' AS 'suburb_name' UNION SELECT 'PYRMONT' AS 'suburb_name' UNION SELECT 'RIVERWOOD' AS 'suburb_name' 
UNION SELECT 'SYDNEY' AS 'suburb_name' UNION SELECT 'ULTIMO' AS 'suburb_name') AS b 
ON a.suburb_name = b.suburb_name
GROUP BY suburb_name
ORDER BY suburb_name ASC

我认为您的问题会解决。

谢谢你。

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。