mysql - 当在年(日期)和月(日期)上使用 MYSQL GROUP BY AND ROLLUP 时，我无法将 Null 更改为 'Total'

Question

我想使用 GROUP BY WITH ROLLUP 创建一个表并获取总行数而不是 null。

$sql ="SELECT 
            IF(YEAR(transaktioner.datum) is null or YEAR(transaktioner.datum) = '','Total',YEAR(transaktioner.datum)) as Year,
            IF(MONTH(transaktioner.datum) is null or MONTH(transaktioner.datum) = '','Total',MONTH(transaktioner.datum)) as Month,                      
            SUM(transaktioner.belopp)*0.01 as Belopp
        FROM transaktioner 
            GROUP BY 
                Year,
                Month
            WITH ROLLUP
   ";


$result = $conn->query($sql);

if ($result->num_rows > 0) {
    // output data of each row
    while($row = $result->fetch_assoc()) {
        echo "||Year:". $row["Year"]."|MONTH:" . $row["Month"]. "|Sum:" . $row["Belopp"]. " ";
    }
} else {
    echo "0 results";
}
$conn->close();

但是输出没有给我 True 或 false，而是给我:

|Year:2016|MONTH:1|Sum:408.1797 |
|Year:2016|MONTH:10|Sum:-70.6915 |
|Year:2016|MONTH:11|Sum:-189.0771 |
|Year:2016|MONTH:12|Sum:-51.7362 |
|Year:2016|MONTH:2|Sum:67.5001 |
|Year:2016|MONTH:3|Sum:132.6177 |
|Year:2016|MONTH:4|Sum:-36.4121 |
|Year:2016|MONTH:5|Sum:-121.8800 |
|Year:2016|MONTH:6|Sum:294.5811 |
|Year:2016|MONTH:7|Sum:76.3505 |
|Year:2016|MONTH:8|Sum:-201.3231 |
|Year:2016|MONTH:9|Sum:-62.8723 |
|Year:2016|MONTH: |Sum:245.2368 |
|Year:2017|MONTH:1|Sum:156.2617 |
|Year:2017|MONTH:2|Sum:-166.7156 |
|Year:2017|MONTH:3|Sum:-183.3601 |
|Year:2017|MONTH:4|Sum:-213.6732 |
|Year:2017|MONTH:5|Sum:-149.0897 |
|Year:2017|MONTH:6|Sum:-120.2097 |
|Year:2017|MONTH:7|Sum:-302.1064 |
|Year:2017|MONTH:8|Sum:-183.0638 |
|Year:2017|MONTH:9|Sum:-119.3371 |
|Year:2017|MONTH: |Sum:-1281.2939 |
|Year:    |MONTH: |Sum:347.6165 

它是空白的，而不是“总计”。我究竟做错了什么？我也尝试过 ifnull() 但同样的问题。

Answer 1

如果要在SQL中进行检查，则必须将分组查询放入子查询中，并在主查询中处理输出:

SELECT IFNULL(Year, 'Total') AS Year,
       IFNULL(Month, 'Total') AS Month,
       Belopp
FROM (
    SELECT YEAR(transaktioner.datum) AS Year,
           MONTH(transaktioner.datum) AS Month,
           SUM(transaktioner.belopp) * 0.01 AS Belopp
    FROM transaktioner
    GROUP BY Year, Month
    WITH ROLLUP
) AS x

您也可以只执行分组查询，并在 PHP 中检查 NULL:

while($row = $result->fetch_assoc()) {
    if ($row["Year"] == null) {
        $row["Year"] = "Total";
    }
    if ($row["Month"] == null) {
        $row["Month"] = "Total";
    }
    echo "||Year:". $row["Year"]."|MONTH:" . $row["Month"]. "|Sum:" . $row["Belopp"]. " ";
}