车辆营收每月每天数据统计问题！！！

Question

 组织表 v_dept deptid         name       pid
---------------------------------- 
1             公司        0   
2             车队        1 车辆档案表 v_car id         plate         deptid
---------------------------------- 
1          京00001        1   
2          京00002        1营收表 v_income id      deptid      plate        RMB         HK_Dollar      IncomeDate 
----------------------------------------------------------------------------------------------------------------------- 
101     2          京00001      5500         20             2009-11-01 
102     2          京00002      2000         300            2009-10-30 
103     2          京00002      1500         500            2009-10-29 
104     2          京00001      6500         1300           2009-10-28我现在要的查询结果是: 
31天的月份车号        1           2      ......     28           29        30        31      合    计 
-------------------------------------------------------------------------------------------- 
京00001     5500        2000   ......     1500        9000       1000      1000     20000
京00001     6500        2200   ......     1300        0          2000      5000     170002条总计     12000       4200   ......     2800        9000       3000      6000     47000
2条平均     6000        2100   ......     1400        4500       1500      3000     23500#####################################################################################################30天的月份
车号        1           2      ......     28           29        30        31      合    计 
-------------------------------------------------------------------------------------------- 
京00001     5500        2000   ......     1500        9000       1000               19000
京00001     6500        2200   ......     1300        0          2000               120002条总计     12000       4200   ......     2800        9000       3000               41000
2条平均     6000        2100   ......     1400        4500       1500               20500
#####################################################################################################
29天月份（2月）车号        1           2      ......     28           29        30        31      合    计 
-------------------------------------------------------------------------------------------- 
京00001     5500        2000   ......     1500        9000                          17000
京00001     6500        2200   ......     1300        0                             100002条总计     12000       4200   ......     2800        9000                          28000
2条平均     6000        2100   ......     1400        4500                          14000
#####################################################################################################
28天月份（2月）车号        1           2      ......     28           29        30        31      合    计 
-------------------------------------------------------------------------------------------- 
京00001     5500        2000   ......     1500                                      8000
京00001     6500        2200   ......     1300                                      100002条总计     12000       4200   ......     2800                                      19000
2条平均     6000        2100   ......     1400                                      9500请问sql语句应该怎么写效率比较高？？？

Answer 1

select plate,
sum(if(day(IncomeDate)=1,RMB,0)) as `1`,
sum(if(day(IncomeDate)=2,RMB,0)) as `2`,
sum(if(day(IncomeDate)=3,RMB,0)) as `3`,
...
sum(if(day(IncomeDate)=31,RMB,0)) as `31`
from v_income
where IncomeDate between '2009-10-01' and '2009-10-31'
group by plate

Answer 2

select plate_no,
    sum(if(day(on_duty_group)=1,(coin_one_yuan+coin_five_jiao+coin_one_jiao+bill_num+hk_dollar),0)) as  day1,
    sum(if(day(on_duty_group)=2,(coin_one_yuan+coin_five_jiao+coin_one_jiao+bill_num+hk_dollar),0)) as  day2,
    sum(if(day(on_duty_group)=3,(coin_one_yuan+coin_five_jiao+coin_one_jiao+bill_num+hk_dollar),0)) as  day3,
    sum(if(day(on_duty_group)=4,(coin_one_yuan+coin_five_jiao+coin_one_jiao+bill_num+hk_dollar),0)) as  day4,
    sum(if(day(on_duty_group)=5,(coin_one_yuan+coin_five_jiao+coin_one_jiao+bill_num+hk_dollar),0)) as  day5,
    sum(if(day(on_duty_group)=6,(coin_one_yuan+coin_five_jiao+coin_one_jiao+bill_num+hk_dollar),0)) as  day6,
    sum(if(day(on_duty_group)=7,(coin_one_yuan+coin_five_jiao+coin_one_jiao+bill_num+hk_dollar),0)) as  day7,
    sum(if(day(on_duty_group)=8,(coin_one_yuan+coin_five_jiao+coin_one_jiao+bill_num+hk_dollar),0)) as  day8,
    sum(if(day(on_duty_group)=9,(coin_one_yuan+coin_five_jiao+coin_one_jiao+bill_num+hk_dollar),0)) as  day9,
    sum(if(day(on_duty_group)=10,(coin_one_yuan+coin_five_jiao+coin_one_jiao+bill_num+hk_dollar),0)) as  day10,
    sum(if(day(on_duty_group)=11,(coin_one_yuan+coin_five_jiao+coin_one_jiao+bill_num+hk_dollar),0)) as  day11,
    sum(if(day(on_duty_group)=12,(coin_one_yuan+coin_five_jiao+coin_one_jiao+bill_num+hk_dollar),0)) as  day12,
    sum(if(day(on_duty_group)=13,(coin_one_yuan+coin_five_jiao+coin_one_jiao+bill_num+hk_dollar),0)) as  day13,
    sum(if(day(on_duty_group)=14,(coin_one_yuan+coin_five_jiao+coin_one_jiao+bill_num+hk_dollar),0)) as  day14,
    sum(if(day(on_duty_group)=15,(coin_one_yuan+coin_five_jiao+coin_one_jiao+bill_num+hk_dollar),0)) as  day15,
    sum(if(day(on_duty_group)=16,(coin_one_yuan+coin_five_jiao+coin_one_jiao+bill_num+hk_dollar),0)) as  day16,
    sum(if(day(on_duty_group)=17,(coin_one_yuan+coin_five_jiao+coin_one_jiao+bill_num+hk_dollar),0)) as  day17,
    sum(if(day(on_duty_group)=18,(coin_one_yuan+coin_five_jiao+coin_one_jiao+bill_num+hk_dollar),0)) as  day18,
    sum(if(day(on_duty_group)=19,(coin_one_yuan+coin_five_jiao+coin_one_jiao+bill_num+hk_dollar),0)) as  day19,
    sum(if(day(on_duty_group)=20,(coin_one_yuan+coin_five_jiao+coin_one_jiao+bill_num+hk_dollar),0)) as  day20,
    sum(if(day(on_duty_group)=21,(coin_one_yuan+coin_five_jiao+coin_one_jiao+bill_num+hk_dollar),0)) as  day21,
    sum(if(day(on_duty_group)=22,(coin_one_yuan+coin_five_jiao+coin_one_jiao+bill_num+hk_dollar),0)) as  day22,
    sum(if(day(on_duty_group)=23,(coin_one_yuan+coin_five_jiao+coin_one_jiao+bill_num+hk_dollar),0)) as  day23,
    sum(if(day(on_duty_group)=24,(coin_one_yuan+coin_five_jiao+coin_one_jiao+bill_num+hk_dollar),0)) as  day24,
    sum(if(day(on_duty_group)=25,(coin_one_yuan+coin_five_jiao+coin_one_jiao+bill_num+hk_dollar),0)) as  day25,
    sum(if(day(on_duty_group)=26,(coin_one_yuan+coin_five_jiao+coin_one_jiao+bill_num+hk_dollar),0)) as  day26,
    sum(if(day(on_duty_group)=27,(coin_one_yuan+coin_five_jiao+coin_one_jiao+bill_num+hk_dollar),0)) as  day27,
    sum(if(day(on_duty_group)=28,(coin_one_yuan+coin_five_jiao+coin_one_jiao+bill_num+hk_dollar),0)) as  day28,
    sum(if(day(on_duty_group)=29,(coin_one_yuan+coin_five_jiao+coin_one_jiao+bill_num+hk_dollar),0)) as  day29,
    sum(if(day(on_duty_group)=30,(coin_one_yuan+coin_five_jiao+coin_one_jiao+bill_num+hk_dollar),0)) as  day30,
    sum(if(day(on_duty_group)=31,(coin_one_yuan+coin_five_jiao+coin_one_jiao+bill_num+hk_dollar),0)) as  day31
from che_check_records
where on_duty_group between '2009-11-01' and '2009-11-30'
group by plate_no

Answer 3

单月统计总计，比如10月份：
select plate,
    sum(if(day(IncomeDate)=1,RMB,0)) as `1`,
    sum(if(day(IncomeDate)=2,RMB,0)) as `2`,
    sum(if(day(IncomeDate)=3,RMB,0)) as `3`,
    ...
    sum(if(day(IncomeDate)=31,RMB,0)) as `31`
from v_income
where IncomeDate between '2009-10-01' and '2009-10-31'
group by plate单月统计平均值，比如10月份：
select plate,
    avg(if(day(IncomeDate)=1,RMB,0)) as `1`,
    avg(if(day(IncomeDate)=2,RMB,0)) as `2`,
    avg(if(day(IncomeDate)=3,RMB,0)) as `3`,
    ...
    avg(if(day(IncomeDate)=31,RMB,0)) as `31`
from v_income
where IncomeDate between '2009-10-01' and '2009-10-31'
group by plate

Answer 4

加上最后一列，合计列单月统计总计，比如10月份：
select plate,
    sum(if(day(IncomeDate)=1,RMB,0)) as `1`,
    sum(if(day(IncomeDate)=2,RMB,0)) as `2`,
    sum(if(day(IncomeDate)=3,RMB,0)) as `3`,
    ...
    sum(if(day(IncomeDate)=31,RMB,0)) as `31`,
    sum(RMB) as '合计'
from v_income
where IncomeDate between '2009-10-01' and '2009-10-31'
group by plate;单月统计平均值，比如10月份：
select plate,
    avg(if(day(IncomeDate)=1,RMB,0)) as `1`,
    avg(if(day(IncomeDate)=2,RMB,0)) as `2`,
    avg(if(day(IncomeDate)=3,RMB,0)) as `3`,
    ...
    avg(if(day(IncomeDate)=31,RMB,0)) as `31`,
    sum(avg(if(day(IncomeDate)=1,RMB,0)) 
        + avg(if(day(IncomeDate)=2,RMB,0))
        + avg(if(day(IncomeDate)=3,RMB,0))
        ...
        +avg(if(day(IncomeDate)=31,RMB,0))) as '合计'
from v_income
where IncomeDate between '2009-10-01' and '2009-10-31'
group by plate;