select 月份,销售量 from (
select rtrim(month(insert_time)) as 月份,sum(销售量) as 销售量,cast(month(insert_time) as decimal(10,2)) as flag from tab where year(insert_time)=2002 group by month(insert_time)
union all
select '小计',sum(销售量) as 销售量,6.5 from tab where year(insert_time)=2002 and month(insert_time) between 1 and 6
union all
select '小计',sum(销售量) as 销售量,13 from tab where year(insert_time)=2002 and month(insert_time) between 7 and 12
union all
select '合计',sum(销售量) as 销售量,14 from tab where year(insert_time)=2002
) A
order by flag
select rtrim(month(insert_time)) as 月份,sum(销售量) as 销售量,cast(month(insert_time) as decimal(10,2)) as flag from tab where year(insert_time)=2002 group by month(insert_time)
union all
select '小计',sum(销售量) as 销售量,6.5 from tab where year(insert_time)=2002 and month(insert_time) between 1 and 6
union all
select '小计',sum(销售量) as 销售量,13 from tab where year(insert_time)=2002 and month(insert_time) between 7 and 12
union all
select '合计',sum(销售量) as 销售量,14 from tab where year(insert_time)=2002
) A
order by flag
select rtrim(month(insert_time)) as 月份,sum(销售量) as 销售量,cast(month(insert_time) as decimal(10,2)) as flag from tab where year(insert_time)=2002 group by month(insert_time)
显示“列 'tab.insert_time' 在选择列表中无效,因为该列既不包含在聚合函数中,也不包含在 GROUP BY 子句中。”错误!!!
from (
select 1 as id,1 as yue,'2003-01-01' start,'2003-02-1' enddate union
select 2 as id,2 as yue,'2003-02-01' start,'2003-03-1' enddate union
select 3 as id,3 as yue,'2003-03-01' start,'2003-03-1' enddate union
select 4 as id,4 as yue,'2003-04-01' start,'2003-05-1' enddate union
select 5 as id,5 as yue,'2003-05-01' start,'2003-06-1' enddate union
select 6 as id,6 as yue,'2003-06-01' start,'2003-07-1' enddate union
select 7 as id,'小计' as yue,'2003-01-01' start,'2003-07-1' enddate union
select 8 as id,7 as yue,'2003-7-01' start,'2003-08-1' enddate union
select 9 as id,8 as yue,'2003-08-01' start,'2003-09-1' enddate union
select 10 as id,9 as yue,'2003-09-01' start,'2003-10-1' enddate union
select 11 as id,10 as yue,'2003-10-01' start,'2003-11-1' enddate union
select 12 as id,11 as yue,'2003-11-01' start,'2003-12-1' enddate union
select 13 as id,12 as yue,'2003-12-01' start,'2004-01-1' enddate union
select 14 as id,'小计' as yue,'2003-07-01' start,'2004-01-1' enddate union
select 15 as id,'合计' as yue,'2003-01-01' start,'2004-01-1' enddate ) a
left join table
on insert_time >=start and insert_time <=enddate and 商品名称=''
group by id,yue
order by id
select rtrim(month(insert_time)) as 月份,sum(销售量) as 销售量,rtrim(month(insert_time)) as flag from tab where year(insert_time)=2002 group by rtrim(month(insert_time))
union all
select '小计',sum(销售量) as 销售量,6.5 from tab where year(insert_time)=2002 and month(insert_time) between 1 and 6
union all
select '小计',sum(销售量) as 销售量,13 from tab where year(insert_time)=2002 and month(insert_time) between 7 and 12
union all
select '合计',sum(销售量) as 销售量,14 from tab where year(insert_time)=2002
) A
order by cast(flag as decimal(10,2))
caiyunxia(monkey) :SUM会自动转null为0,除非整用没数据。
from (
select 1 as id,'1' as yue,'2003-01-01' start,'2003-02-1' enddate union
select 2 as id,'2' as yue,'2003-02-01' start,'2003-03-1' enddate union
select 3 as id,'3' as yue,'2003-03-01' start,'2003-03-1' enddate union
select 4 as id,'4' as yue,'2003-04-01' start,'2003-05-1' enddate union
select 5 as id,'5' as yue,'2003-05-01' start,'2003-06-1' enddate union
select 6 as id,'6' as yue,'2003-06-01' start,'2003-07-1' enddate union
select 7 as id,'小计' as yue,'2003-01-01' start,'2003-07-1' enddate union
select 8 as id,'7' as yue,'2003-7-01' start,'2003-08-1' enddate union
select 9 as id,'8' as yue,'2003-08-01' start,'2003-09-1' enddate union
select 10 as id,'9' as yue,'2003-09-01' start,'2003-10-1' enddate union
select 11 as id,'10' as yue,'2003-10-01' start,'2003-11-1' enddate union
select 12 as id,'11' as yue,'2003-11-01' start,'2003-12-1' enddate union
select 13 as id,'12' as yue,'2003-12-01' start,'2004-01-1' enddate union
select 14 as id,'小计' as yue,'2003-07-01' start,'2004-01-1' enddate union
select 15 as id,'合计' as yue,'2003-01-01' start,'2004-01-1' enddate ) a
left join table
on insert_time >=start and insert_time <=enddate and 商品名称=''
group by id,yue
order by id
现在没问题了,我测试了的
---- ----------------------------------------
1 50964.0000
2 28468.9000
3 .0000
4 .0000
5 .0000
6 .0000
小计 79432.9000
7 .0000
8 .0000
9 .0000
10 .0000
11 .0000
12 .0000
小计 .0000
合计 79432.9000(所影响的行数为 15 行)