我想得到的是每月来自generate_series的统计数据以及每个月中计算的id的总和.这个sql适用于Postgresql 9.1：
SELECT (to_char(serie,'yyyy-mm')) AS year,sum(amount)::int AS eintraege FROM (
SELECT
COUNT(mytable.id) as amount,generate_series::date as serie
FROM mytable
RIGHT JOIN generate_series(
(SELECT min(date_from) FROM mytable)::date,(SELECT max(date_from) FROM mytable)::date,interval '1 day') ON generate_series = date(date_from)
WHERE version = 1
GROUP BY generate_series
) AS foo
GROUP BY Year
ORDER BY Year ASC;
这是我的输出
"2006-12" | 4 "2007-02" | 1 "2007-03" | 1
但我想得到的是这个输出(1月份的“0”值)：
"2006-12" | 4 "2007-01" | 0 "2007-02" | 1 "2007-03" | 1
因此,如果有一个没有id的月份,它应该被列出.
任何想法如何解决这个问题？
以下是一些示例数据：
drop table if exists mytable;
create table mytable(id bigint,version smallint,date_from timestamp without time zone);
insert into mytable(id,version,date_from) values
('4084036','1','2006-12-22 22:46:35'),('4084938','2006-12-23 16:19:13'),'2','2006-12-23 16:20:23'),('4084939','2006-12-23 16:29:14'),('4084954','2006-12-23 16:28:28'),('4250653','2007-02-12 21:58:53'),('4250657','2007-03-12 21:58:53')
;
解决方法
解开,简化和修复,它可能看起来像这样：
SELECT to_char(s.tag,'yyyy-mm') AS monat,count(t.id) AS eintraege
FROM (
SELECT generate_series(min(date_from)::date,max(date_from)::date,interval '1 day'
)::date AS tag
FROM mytable t
) s
LEFT JOIN mytable t ON t.date_from::date = s.tag AND t.version = 1
GROUP BY 1
ORDER BY 1;
db<>小提琴here
在所有噪音,误导性标识符和非常规格式中,实际问题隐藏在此处：
WHERE version = 1
当你正确使用RIGHT [OUTER] JOIN时,你通过添加一个WHERE子句来取消这一努力,该子句需要mytable中的一个不同值 – 有效地将RIGHT JOIN转换为JOIN.
将该子句拉入JOIN条件以使其工作.
我简化了其他一些事情.
有关：
