时间戳

首页 > 解决方案 > is it possible to use more than 1 query in mysql?

php - is it possible to use more than 1 query in mysql?

问题描述

hi guys i have a doubt here, i have my website and im trying to echo in php from my database the max temperature, minimum temperature, most recent temperature that entered the DB and i´ve looked around and it seems that i need to use 2 queries by using the UNION from Mysql and i used it but now it only shows the max temperature in DB from the current day here is my code:

$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>";

much apreciated all the help guys!

标签： phpmysqldatabasedategroup-by

解决方案

UNION seems unessary here, and adds complexity client-side. You can get the three information that you want on a single row (so without UNION) with the following query:

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

I am unsure whether window functions would perform better (this requires MySQL 8.0 though):

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

推荐阅读