一个复杂的group by的写法

Question

本帖最后由 taijh999 于 2010-10-12 07:13:44 编辑

Answer 1

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,Yearmysql> 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>