检索行数，如果没有行则返回0 [英] Retrieving row count and returning 0 when no rows

问题描述

我写了一个查询来检索我每天有多少网站注册：

 已创建SELECT，COUNT个（id）
来自注册人
 GROUP BY创建了
 ORDER BY创建了desc 
  

但是，这只会检索人们实际注册的日期的行。如果一天没有人签约，那么我想当天返回0。有没有办法使用SQL来执行此操作，还是我必须使用PHP解析结果？

解决方案

假设由于缺乏信息而创建了 date 类型的。

Postgres提供了很棒的 generate_series（） 来简化此操作：

  SELECT d。创建，COUNT个.id）AS ct 
 FROM（
选择generate_series（min（created）
，max（created），interval'1 day'）:: date AS created 
 FROM signups 
）d 
左联接注册s的使用（创建）
 GROUP BY 1 
 ORDER BY 1 DESC； 
  

这会自动从您的表格中检索最小和最大日期，并且每天之间提供一行。 p>

I've written a query to retrieve how many website signups I've had every day:

SELECT created, COUNT(id)
FROM signups
GROUP BY created
ORDER BY created desc

However, this only retrieves rows for days where people have actually signed up. If nobody has signed up in a day, I would like to return 0 for that day. Is there a way to do this using SQL or will I have to parse through the results using PHP?

解决方案

Assuming created to be of type date for lack of information.

Postgres provides the wonderful generate_series() to make this easy:

SELECT d.created, COUNT(s.id) AS ct
FROM  (
   SELECT generate_series(min(created)
                        , max(created), interval '1 day')::date AS created
   FROM   signups
   ) d
LEFT   JOIN signups s USING (created)
GROUP  BY 1
ORDER  BY 1 DESC;

This retrieves minimum and maximum day from your table automatically and provides one row per day in between.

这篇关于检索行数，如果没有行则返回0的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！