Mysql 查询问题

Question

   表格里有如下几个字段：
            date       business   province   income    newuser
       2012-01-02     game1       北京        200       20
       2012-01-03     game1       北京        100       20
       2012-01-02     game2       北京        300       20
       2012-01-03     game3       北京        200       20
       2012-01-02     game4       北京        200       20
        xxxxxxx       xxxxx       xxx       xxx       xx

   最初是要在表格里体现每一天，每个省的所有业务的总收入和新增用户数，所以我直接写select date,province,SUM(income),sum(newuser) from table group by date,province 就行了，现在又有新的两个字段要查询，要在原有的查询出来的一条记录的基础上加两列，查出特定的几个游戏的sum(income)和sum(newuser),也是按每一天每个省算，请问sql语句如何写呢？

Answer 1

[code="sql"]select date, province, sum(case when business = 'game1' then income else 0 end) as game1_income,
sum(case when business = 'game1' then newuser else 0 end) as game1_newuser,
sum(case when business = 'game2' then income else 0 end) as game2_income,
sum(case when business = 'game2' then newuser else 0 end) as game2_newuser
....
from 业务数据表 group by date, province[/code]