mysql - ROLLUP 返回错误值

Question

今天在这个问题上停留了一段时间，想知道有人能提出问题出在哪里吗？这是我的代码:

select g.game_name, r.rental_id, sum(if(datediff(r.return_date, r.due_date) > 0, datediff(r.return_date, r.due_date) * g.overdue_charge_per_day, 0)) Overdue_Charges
from rental as r
left join game as g
on r.game_id = g.game_id
where month(r.return_date)=month(now())
group by rental_id
with rollup;

这将显示以下内容:

侠盗猎车手 V 12 4.00

托尼霍克 13 15.00

托尼·霍克 null 19.00

所以总共 4 和 15。但是我不希望它显示“Tony Hawk”两次。我可以用总计替换它吗？也许这是一个加入问题，第二个托尼·霍克也应该为空。谢谢

Answer 1

此行为在"GROUP BY Modifiers"中进行了解释。文档部分:

MySQL permits a column that does not appear in the GROUP BY list to be named in the select list. In this case, the server is free to choose any value from this nonaggregated column in summary rows, and this includes the extra rows added by WITH ROLLUP. For example, in the following query, country is a nonaggregated column that does not appear in the GROUP BY list and values chosen for this column are indeterminate:

mysql> SELECT year, country, SUM(profit)
    -> FROM sales GROUP BY year WITH ROLLUP;
+------+---------+-------------+
| year | country | SUM(profit) |
+------+---------+-------------+
| 2000 | India   |        4525 |
| 2001 | USA     |        3010 |
| NULL | USA     |        7535 |
+------+---------+-------------+

This behavior occurs if the ONLY_FULL_GROUP_BY SQL mode is not enabled. If that mode is enabled, the server rejects the query as illegal because country is not listed in the GROUP BY clause.

现在，要显示另一个值“Grand Total”，您可以使用带有 if 的包装查询，如下所示:

select    if(rental_id is null, 'Grand Total', game_name) game_name,
          rental_id,
          Overdue_Charges
from      ( 
        select    game_name 
                  r.rental_id, 
                  sum(if(datediff(r.return_date, r.due_date) > 0, 
                         datediff(r.return_date, r.due_date) * g.overdue_charge_per_day,
                         0)) Overdue_Charges
        from      rental as r
        left join game as g
               on r.game_id = g.game_id
        where     month(r.return_date)=month(now())
        group by  rental_id
        with rollup) sub;