首先,需要给原表加一个分类(TYPE),相同分类的的数据,统计到一个合计里。 然后单独写一个查询,用来返回所有的合计。 最后用查询合计的语句UNION ALL原表。 SELECT * FROM ( SELECT T1.*,1 Y FROM TABLE_NAME T1 UNION ALL SELECT T2.*,2 Y FROM TABLE_NAME T2 GOURP BY T2.TYPE ) T ORDER BY T.TYPE,T.Y ; 最后的ORDER BY是为了保证相同分类(TYPE)的数据在一起,Y是为了保证基础数据在前,统计数据在后。
光用查询是达不到你这么完美的,就算用Group By With Rollup也达不到你想要的效果。 要想写跟你一样的,需要写一个函数,返回一张表, 然后函数里面把汇率数据一个一个写到表里面去。
思路就是有两个查询,分别统计按月的,和按年的,小计的话给一个最大的月的天数,比如2019-03-31,这样他一定排在每个月的最后面;总计就给一个最大的月份数,比如2019-13-01;然后把详细,小计,总计union all起来,在按日期正序排列,eg: select * from(select * from t1 UNION ALL select CONCAT(DATE_FORMAT(date,'yyyy-MM'),'-32') date,'小计' goods,'' sale,'' remain,sum(profit) profit from t1 GROUP BY DATE_FORMAT(date,'yyyy-MM') UNION ALL select CONCAT(DATE_FORMAT(date,'yyyy'),'13-01') date,'总计' goods,'' sale,'' remain,sum(profit) profit from t1 ) order by date
以上,不需要修改原表,添加的分类(TYPE)只是在查询语句中添加。直接查询即可,其实并不复杂。
with tmp as (select date '2019-03-05' as p_date, '电脑' as p_good, 4 as p_salnum, 6 as p_stock, 100 as p_money from dual union all select date '2019-03-09', '手机', 3, 5, 200 from dual union all select date '2019-04-10', '打印机', 3, 4, 360 from dual union all select date '2019-04-15', '冰箱', 2, 5, 250 from dual), tmp1 as (select tmp.*, trunc(tmp.p_date, 'mm') p_month, trunc(tmp.p_date, 'yyyy') as p_year from tmp) select P_DATE, P_GOOD, P_SALNUM, P_STOCK, P_MONEY from tmp1 union all select add_months(tmp1.p_month, 1) as P_DATE, null as P_GOOD, null as P_SALNUM, null as P_STOCK, sum(P_MONEY) from tmp1 group by tmp1.p_month union all select add_months(tmp1.p_year, 12) as P_DATE, null as P_GOOD, null as P_SALNUM, null as P_STOCK, sum(P_MONEY) from tmp1 group by tmp1.p_year order by p_date 这个是oracle版本的一种思路,供参考。就是构造出你需要汇总的数据即可。
然后单独写一个查询,用来返回所有的合计。
最后用查询合计的语句UNION ALL原表。
SELECT * FROM (
SELECT T1.*,1 Y FROM TABLE_NAME T1
UNION ALL
SELECT T2.*,2 Y FROM TABLE_NAME T2 GOURP BY T2.TYPE
) T ORDER BY T.TYPE,T.Y ;
最后的ORDER BY是为了保证相同分类(TYPE)的数据在一起,Y是为了保证基础数据在前,统计数据在后。
要想写跟你一样的,需要写一个函数,返回一张表,
然后函数里面把汇率数据一个一个写到表里面去。
select * from(select * from t1
UNION ALL
select CONCAT(DATE_FORMAT(date,'yyyy-MM'),'-32') date,'小计' goods,'' sale,'' remain,sum(profit) profit from t1 GROUP BY DATE_FORMAT(date,'yyyy-MM')
UNION ALL
select CONCAT(DATE_FORMAT(date,'yyyy'),'13-01') date,'总计' goods,'' sale,'' remain,sum(profit) profit from t1 ) order by date
(select date '2019-03-05' as p_date,
'电脑' as p_good,
4 as p_salnum,
6 as p_stock,
100 as p_money
from dual
union all
select date '2019-03-09', '手机', 3, 5, 200
from dual
union all
select date '2019-04-10', '打印机', 3, 4, 360
from dual
union all
select date '2019-04-15', '冰箱', 2, 5, 250
from dual),
tmp1 as
(select tmp.*,
trunc(tmp.p_date, 'mm') p_month,
trunc(tmp.p_date, 'yyyy') as p_year
from tmp)
select P_DATE, P_GOOD, P_SALNUM, P_STOCK, P_MONEY
from tmp1
union all
select add_months(tmp1.p_month, 1) as P_DATE,
null as P_GOOD,
null as P_SALNUM,
null as P_STOCK,
sum(P_MONEY)
from tmp1
group by tmp1.p_month
union all
select add_months(tmp1.p_year, 12) as P_DATE,
null as P_GOOD,
null as P_SALNUM,
null as P_STOCK,
sum(P_MONEY)
from tmp1
group by tmp1.p_year
order by p_date
这个是oracle版本的一种思路,供参考。就是构造出你需要汇总的数据即可。