如果没有记录，如何使MySQL SUM查询返回零而不是null? [英] How can I make a MySQL SUM query return zero instead of null if there are no records?

问题描述

这是我的选择查询:

SELECT SUM(rating) AS this_week 
FROM table_name 
WHERE UNIX_TIMESTAMP(created_at) >= UNIX_TIMESTAMP() - 604800)

基本上计算了上周的商品评分(604800是1周内的秒数).

Which basically counts the rating of an item for the last week (604800 is a number of seconds in 1 week).

问题在于，当表中没有行时，this_week将作为NULL返回.如果表中没有行，我希望查询返回0.怎么做?

The problem is that when there are no rows in the table, the this_week will be returned as NULL. I would like the query to return 0 in case there are no rows in the table. How to do it?

推荐答案

这应该可以解决问题:

SELECT COALESCE(SUM(rating),0) AS this_week FROM table_name 
  WHERE UNIX_TIMESTAMP(created_at) >= UNIX_TIMESTAMP() - 604800)

COALESCE是一个函数，它将从列表中返回第一个非NULL值.

COALESCE is a function that will return the first non NULL value from the list.

这篇关于如果没有记录，如何使MySQL SUM查询返回零而不是null?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！