56,940
社区成员




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>