请教一个统计方面的sql写法

Question

表内容如下：
table A
id intime val
1 2000-1 100
1 2000-2 101
1 2002-1 110
1 2003-1 120
要按年统计2000-2004年val的和，即得到下面的结果：
intime id sum(val)
2000 1 201
2001 1 0
2002 1 110
2003 1 120
2004 1 0这个SQL语句应该怎么写？
我用SELECT year(intime),id sum(val) from A where intime between '2000-1-1' and '2004-12-31' group by year(intime);
得到的结果里没有2001和2004的行.我的目的是想让没有数据的行给一个特定值（比如0），这样只要我给定时间范围，统计出来的结果集无论对那个ID都会返回相同数量的行。

Answer 1

呵呵，新建临时表，字段
id intime val ID为1，INTIME 为2000-2004，VAL为0，两表UNION再GROUP BY

Answer 2

如果数据量不大的话：
SELECT year(intime),id sum(val) from (
select 1 as id ,cast('2000-1-1' as date) as intime,0 as val
union
select 1,cast('2001-1-1' as date),0
union
select 1,cast('2002-1-1' as date),0
union
select 1,cast('2003-1-1' as date),0
union
select 1,cast('2004-1-1' as date),0
union
select * from a )A1
 where intime between '2000-1-1' and '2004-12-31' group by year(intime); 

Answer 3

谢谢表数据很多，最多会上千万条记录。
drop table if exists ta;
create temporary table ta (id int,intime datetime,val int);
insert into ta values(610,'2000-1-1',0);
insert into ta values(610,'2001-1-1',0);
insert into ta values(610,'2002-1-1',0);
insert into ta values(610,'2003-1-1',0);
insert into ta values(610,'2004-1-1',0);select tmp.* from (
select id,year(intime) AS y,sum(val) AS sv from histry where id=610 and intime between '2000-1-1' and '2004-12-31' group by year(intime)
union select id,year(intime) AS y,val AS sv from ta 
group by y) AS tmp
group by tmp.y;
这样好像可以了。不过这个临时表构造起来很麻烦的，因为时间段是任意的。况且还可以按天，按月等来统计，如果都这样构造也太麻烦了。还有没有别的办法？

Answer 4

另建一个表，create table periods(
  pyear INTEGER PRIMARY KEY
);insert into periods values (2000);
insert into periods values (2001);
insert into periods values (2002);
insert into periods values (2003);
insert into periods values (2004);然后用left join即可select p.pyear,p.id,sum(a.val)
from ( select b.pyear,c.id
from periods b , (select distinct id from A) c) p 
left join A 
on p.pyear=left(a.intime,4) and p.id=a.id
group by p.pyear,p.id

Answer 5

刚试了一下，好像巨慢，等了以后没结束就中止了。估计left join很耗时。

Answer 6

select distinct id from A这句不会太快，然后再left join会花很多时间，建议把select b.pyear,c.id
from periods b , (select distinct id from A) c结果放到临时表，然后再做left join速度或许会快一些。

Answer 7

在这里，每次发出查询时，日期范围是固定的，分组间隔也是固定的，但要统计的字段和ID号是不一定的。比如一次查询可能是：
统计2000年到2004年按年统计id=1的val1的平均值、id=2的val2的总和
另一次则可能是：
统计2001年到2007年按年统计id=1的val2的最大值、id=2的val1的平均值等。然后对于各个ID来说，都可能存在在某一个分组段内没有记录的情况。查询到之后要把数据在一个图中画出来（或者填入一个表内），所以我需要保证不同的ID/参数在相同的时间段，相同的统计间隔下返回相同的行。现在有两个办法：
1  按照两位上面给出的方法，构造一个统计时间间隔的序列（临时表），直接让数据库返回符合要求的记录集；
2  直接group，多个ID用多个查询，返回之后在客户端重新整理成符合要求的数组。请问有没有更好的办法？

Answer 8

应该没有，生成临时表与工作表UNION，再GROUP ，应该是最佳选择。