嗨,大家好,我在这里有一个疑问,我有我的网站,我正在尝试从数据库中回显php的最高温度,最低温度,进入数据库的最新温度,我环顾四周,看来我需要使用通过使用来自Mysql的UNION的2个查询,我使用了它,但是现在它仅显示了当天数据库中的最高温度,这是我的代码:
$connect = mysqli_connect(".....", ".....", "....", ".....");
$sql ="SELECT MAX(temperature) as max_temperature , MIN(temperature) as min_temperature
FROM sensor
WHERE DATE(data) = CURDATE()
UNION
SELECT temperature, data
FROM sensor
ORDER BY data DESC
LIMIT 1";
$result = mysqli_query($connect, $sql);
while($row = mysqli_fetch_array($result)) {
$max_temperature = number_format($row['max_temperature'], 1, '.', ',');/*armazena dados vindo da B.D */
$temperature = number_format($row['temperature'], 1, '.', ',');
$min_temperature = number_format($row['min_temperature'], 1, '.', ',');
}
echo "<h4>".$max_temperature."</h4>";
echo "<h3>".$temperature."</h3>";
echo "<h4>".$min_temperature."</h4>";
非常感谢所有帮助人员!
UNION
在这里似乎不必要,并增加了客户端的复杂性。您可以UNION
使用以下查询在一行上获得所需的三个信息(因此不带):
SELECT
MAX(temperature) max_temperature,
MIN(temperature) min_temperature,
(SELECT temperature FROM sensor ORDER BY data DESC LIMIT 1) current_temperature
FROM sensor
WHERE data >= current_date AND data < current_date + 1 day
我不确定窗口功能是否会更好(尽管这需要MySQL 8.0):
SELECT DISTINCT
MAX(temperature) OVER() max_temperature,
MIN(temperature) OVER() min_temperature,
FIRST_VALUE(temperature) OVER(ORDER BY date DESC) current_temperature
FROM sensor
WHERE data >= current_date AND data < current_date + interval 1 day
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句