一个复杂的group by的写法

[taijh999]

CREATE TABLE t_jingfei (ketibianhao VARCHAR(50) NOT NULL,
ketilaiyuan VARCHAR(30) NOT NULL,
zhuchirendanwei VARCHAR(30) NOT NULL,
zhuchiren VARCHAR(80) NOT NULL, jingfei FLOAT,
boruriqi VARCHAR(10), leibie CHAR(2), PRIMARY KEY (ketibianhao));

INSERT INTO keyanguanli.t_jingfei (ketibianhao, ketilaiyuan, zhuchirendanwei, zhuchiren, jingfei, boruriqi, leibie) VALUES ('1111', '01', '01', '07008', 78.0, '2008-01-09', '01');
INSERT INTO keyanguanli.t_jingfei (ketibianhao, ketilaiyuan, zhuchirendanwei, zhuchiren, jingfei, boruriqi, leibie) VALUES ('222', '02', '02', '07005', 89.0, '2009-01-12', '02');
INSERT INTO keyanguanli.t_jingfei (ketibianhao, ketilaiyuan, zhuchirendanwei, zhuchiren, jingfei, boruriqi, leibie) VALUES ('333', '03', '01', '07005', 67.0, '2010-1-2', '01');
INSERT INTO keyanguanli.t_jingfei (ketibianhao, ketilaiyuan, zhuchirendanwei, zhuchiren, jingfei, boruriqi, leibie) VALUES ('444', '01', '07', '06007', 89.0, '2010-1-1', '02');
INSERT INTO keyanguanli.t_jingfei (ketibianhao, ketilaiyuan, zhuchirendanwei, zhuchiren, jingfei, boruriqi, leibie) VALUES ('666', '08', '07', '07008', 12.0, '2009-1-1', '01');
INSERT INTO keyanguanli.t_jingfei (ketibianhao, ketilaiyuan, zhuchirendanwei, zhuchiren, jingfei, boruriqi, leibie) VALUES ('555', '09', '06', '07010', 56.0, '2009-1-1', '02');
INSERT INTO keyanguanli.t_jingfei (ketibianhao, ketilaiyuan, zhuchirendanwei, zhuchiren, jingfei, boruriqi, leibie) VALUES ('777', '04', '07', '07020', 66.0, '2008-1-1', '02');
INSERT INTO keyanguanli.t_jingfei (ketibianhao, ketilaiyuan, zhuchirendanwei, zhuchiren, jingfei, boruriqi, leibie) VALUES ('88', '07', '04', '09001', 23.0, '2009-1-1', '02');
INSERT INTO keyanguanli.t_jingfei (ketibianhao, ketilaiyuan, zhuchirendanwei, zhuchiren, jingfei, boruriqi, leibie) VALUES ('99', '09', '04', '05004', 67.0, '2008-9-9', '02');

CREATE TABLE source_table (sourceid CHAR(2) NOT NULL, `sourceName` VARCHAR(20), PRIMARY KEY (sourceid));

INSERT INTO keyanguanli.source_table (sourceid, `sourceName`) VALUES ('01', '科技部');
INSERT INTO keyanguanli.source_table (sourceid, `sourceName`) VALUES ('02', '教育部');
INSERT INTO keyanguanli.source_table (sourceid, `sourceName`) VALUES ('03', '农业部');
INSERT INTO keyanguanli.source_table (sourceid, `sourceName`) VALUES ('04', '国家自然基金委');
INSERT INTO keyanguanli.source_table (sourceid, `sourceName`) VALUES ('05', '国家社科基金');
INSERT INTO keyanguanli.source_table (sourceid, `sourceName`) VALUES ('06', '省科技厅');
INSERT INTO keyanguanli.source_table (sourceid, `sourceName`) VALUES ('07', '省哲学社科规划办');
INSERT INTO keyanguanli.source_table (sourceid, `sourceName`) VALUES ('08', '省教育厅');
INSERT INTO keyanguanli.source_table (sourceid, `sourceName`) VALUES ('09', '农垦总局');
INSERT INTO keyanguanli.source_table (sourceid, `sourceName`) VALUES ('10', '大庆市科技局');
INSERT INTO keyanguanli.source_table (sourceid, `sourceName`) VALUES ('11', '开发区经济科技局');


select sourceName,count(*) as 课题个数 ,
sum(jingfei) as 经费,SUBSTRING(boruriqi,1,4)
as year from t_jingfei k,source_table s where s.sourceid=k.ketilaiyuan

group by s.sourceid,year
having year>=2008 and year<=2010



如下所示：

1、如何加上 没有显示的类别 如大庆市科技局 并把经费为0

2、在科技部 2008-2010加上2009 并经费为0
农垦总局加上2010并把经费设为0
比较紧急 谢谢各位了

+------------------+----------+------+------+
| sourceName | 课题个数 | 经费 | year |
+------------------+----------+------+------+
| 科技部 | 1 | 78 | 2008 |
| 科技部 | 1 | 89 | 2010 |
| 教育部 | 1 | 89 | 2009 |
| 农业部 | 1 | 67 | 2010 |
| 国家自然基金委 | 1 | 66 | 2008 |
| 省哲学社科规划办 | 1 | 23 | 2009 |
| 省教育厅 | 1 | 12 | 2009 |
| 农垦总局 | 1 | 67 | 2008 |
| 农垦总局 | 1 | 56 | 2009 |
+------------------+----------+------+------+

[ACMAIN_CHM]

select a.sourceid,a.sourceName,
	count(ketibianhao) as `课题个数` ,
	IfNull(sum(jingfei),0) as `经费`,
	yy as year
from (source_table a ,(
select 2008 as yy
Union
select 2009 as yy
Union
select 2010 as yy
) b) left join t_jingfei c on a.sourceid=c.ketilaiyuan and yy=SUBSTRING(boruriqi,1,4) 
group by a.sourceid,Year

mysql> select a.sourceid,a.sourceName,
    ->  count(ketibianhao) as `课题个数` ,
    ->  IfNull(sum(jingfei),0) as `经费`,
    ->  yy as year
    -> from (source_table a ,(
    -> select 2008 as yy
    -> Union
    -> select 2009 as yy
    -> Union
    -> select 2010 as yy
    -> ) b) left join t_jingfei c on a.sourceid=c.ketilaiyuan and yy=SUBSTRING(b
oruriqi,1,4)
    -> group by a.sourceid,year;
+----------+------------------+----------+------+------+
| sourceid | sourceName       | 课题个数 | 经费 | year |
+----------+------------------+----------+------+------+
| 01       | 科技部           |        1 |   78 | 2008 |
| 01       | 科技部           |        0 |    0 | 2009 |
| 01       | 科技部           |        1 |   89 | 2010 |
| 02       | 教育部           |        0 |    0 | 2008 |
| 02       | 教育部           |        1 |   89 | 2009 |
| 02       | 教育部           |        0 |    0 | 2010 |
| 03       | 农业部           |        0 |    0 | 2008 |
| 03       | 农业部           |        0 |    0 | 2009 |
| 03       | 农业部           |        1 |   67 | 2010 |
| 04       | 国家自然基金委   |        1 |   66 | 2008 |
| 04       | 国家自然基金委   |        0 |    0 | 2009 |
| 04       | 国家自然基金委   |        0 |    0 | 2010 |
| 05       | 国家社科基金     |        0 |    0 | 2008 |
| 05       | 国家社科基金     |        0 |    0 | 2009 |
| 05       | 国家社科基金     |        0 |    0 | 2010 |
| 06       | 省科技厅         |        0 |    0 | 2008 |
| 06       | 省科技厅         |        0 |    0 | 2009 |
| 06       | 省科技厅         |        0 |    0 | 2010 |
| 07       | 省哲学社科规划办 |        0 |    0 | 2008 |
| 07       | 省哲学社科规划办 |        1 |   23 | 2009 |
| 07       | 省哲学社科规划办 |        0 |    0 | 2010 |
| 08       | 省教育厅         |        0 |    0 | 2008 |
| 08       | 省教育厅         |        1 |   12 | 2009 |
| 08       | 省教育厅         |        0 |    0 | 2010 |
| 09       | 农垦总局         |        1 |   67 | 2008 |
| 09       | 农垦总局         |        1 |   56 | 2009 |
| 09       | 农垦总局         |        0 |    0 | 2010 |
| 10       | 大庆市科技局     |        0 |    0 | 2008 |
| 10       | 大庆市科技局     |        0 |    0 | 2009 |
| 10       | 大庆市科技局     |        0 |    0 | 2010 |
| 11       | 开发区经济科技局 |        0 |    0 | 2008 |
| 11       | 开发区经济科技局 |        0 |    0 | 2009 |
| 11       | 开发区经济科技局 |        0 |    0 | 2010 |
+----------+------------------+----------+------+------+
33 rows in set (0.05 sec)

mysql>

[taijh999]

1楼太帅了，解决了一个大难题，非常感谢