一个脚本实现单表多公司按年月统计报表sql写报表统计 by qiweb 201810

本文主要是介绍一个脚本实现单表多公司按年月统计报表sql写报表统计 by qiweb 201810，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

1、根据公司和日期分组获得发票数据量

SELECT
   PAY_TAXES_ORGNAME,
   --BILLING_NO,
   LEFT(INVOICE_OPEN_DATE,7) AS MM,
COUNT(1) AS NUM
--,INVOICE_STATE,
--sum(case when INVOICE_STATE=1 then 1 else 0 end) AS 蓝票,
--sum(case when INVOICE_STATE=2 then 1 else 0 end) as 红票,
--sum(case when INVOICE_STATE=4 then 1 else 0 end) as 作废成功
   --INVOICE_NUMBER,
   --INVOICE_CODE
FROM
   TBL_TXN_INVOICE
WHERE
   INVOICE_OPEN_STATE = 3
and INVOICE_STATE in(1,2,4)
AND INVOICE_CODE IS NOT NULL
GROUP BY LEFT(INVOICE_OPEN_DATE,7),PAY_TAXES_ORGNAME --,INVOICE_STATE

2、根据公司和日期分组获得不同发票状态下的发票数据量

SELECT
   PAY_TAXES_ORGNAME 公司,
   --BILLING_NO,
   LEFT(INVOICE_OPEN_DATE,7) 年月,
   --INVOICE_NUMBER,
   --INVOICE_CODE,
count(1) 数量,
INVOICE_STATE 发票状态
FROM
   TBL_TXN_INVOICE
WHERE
   INVOICE_OPEN_STATE = 3
and invoice_open_date like '2018-10%'
AND INVOICE_CODE IS NOT NULL
group by PAY_TAXES_ORGNAME,LEFT(INVOICE_OPEN_DATE,7),INVOICE_STATE
;

效果图：

3、在脚本一的基础上细分发票状态

SELECT
   PAY_TAXES_ORGNAME,
   --BILLING_NO,
   LEFT(INVOICE_OPEN_DATE,7) AS MM,
COUNT(1) AS NUM,INVOICE_STATE,
sum(case when INVOICE_STATE=1 then 1 else 0 end) AS 蓝票,
sum(case when INVOICE_STATE=2 then 1 else 0 end) as 红票,
sum(case when INVOICE_STATE=4 then 1 else 0 end) as 作废成功
   --INVOICE_NUMBER,
   --INVOICE_CODE
FROM
   TBL_TXN_INVOICE
WHERE
   INVOICE_OPEN_STATE = 3
and INVOICE_STATE in(1,2,4)
AND INVOICE_CODE IS NOT NULL
GROUP BY PAY_TAXES_ORGNAME,LEFT(INVOICE_OPEN_DATE,7) ,INVOICE_STATE

效果图：

细分后数量变多

4、在3的基础上修改，把分类列出来，同公司同日期合并在包装子查询，分组再排序得到如下结果

SELECT AA.PAY_TAXES_ORGNAME,AA.MM,
SUM(AA.NUM) AS 合计,
SUM(AA.蓝票) AS 蓝票,
SUM(AA.红票) AS 红票,
SUM(AA.作废成功) AS 作废成功
--CASE AA.INVOICE_STATE WHEN 1 THEN    1) 蓝票
--case when AA.INVOICE_STATE=1 then 1 else 0 end
FROM (
SELECT
   PAY_TAXES_ORGNAME,
   --BILLING_NO,
   LEFT(INVOICE_OPEN_DATE,7) AS MM,
COUNT(1) AS NUM,INVOICE_STATE,
sum(case when INVOICE_STATE=1 then 1 else 0 end) AS 蓝票,
sum(case when INVOICE_STATE=2 then 1 else 0 end) as 红票,
sum(case when INVOICE_STATE=4 then 1 else 0 end) as 作废成功
   --INVOICE_NUMBER,
   --INVOICE_CODE
FROM
   TBL_TXN_INVOICE
WHERE
   INVOICE_OPEN_STATE = 3
and INVOICE_STATE in(1,2,4)
AND INVOICE_CODE IS NOT NULL
GROUP BY PAY_TAXES_ORGNAME,LEFT(INVOICE_OPEN_DATE,7) ,INVOICE_STATE
) AA
GROUP BY AA.PAY_TAXES_ORGNAME,AA.MM
ORDER BY AA.PAY_TAXES_ORGNAME
;

效果图：

5、再扩展

SELECT AA.PAY_TAXES_ORGNAME as 公司,AA.MM 开票年月,
SUM(AA.NUM) AS 合计,
SUM(AA.蓝票) AS 蓝票,
SUM(AA.红票) AS 红票,
SUM(AA.作废成功) AS 作废成功
,SUM(AA.专票) AS 专票,
SUM(AA.普票) AS 普票,
SUM(AA.电子票) AS 电子票
--CASE AA.INVOICE_STATE WHEN 1 THEN    1) 蓝票
--case when AA.INVOICE_STATE=1 then 1 else 0 end
FROM (
SELECT
   PAY_TAXES_ORGNAME,
   --BILLING_NO,
   LEFT(INVOICE_OPEN_DATE,7) AS MM,
COUNT(1) AS NUM,INVOICE_STATE ,
sum(case when INVOICE_STATE=1 then 1 else 0 end) AS 蓝票,
sum(case when INVOICE_STATE=2 then 1 else 0 end) as 红票,
sum(case when INVOICE_STATE=4 then 1 else 0 end) as 作废成功
,INVOICE_TYPE ,
sum(case when INVOICE_TYPE=0 then 1 else 0 end) AS 专票,
sum(case when INVOICE_TYPE=1 then 1 else 0 end) as 普票,
sum(case when INVOICE_TYPE=3 OR INVOICE_TYPE=2 then 1 else 0 end) as 电子票
   --INVOICE_NUMBER,
   --INVOICE_CODE
FROM
   TBL_TXN_INVOICE
WHERE
   INVOICE_OPEN_STATE = 3
and INVOICE_STATE in(1,2,4)
AND INVOICE_CODE IS NOT NULL
GROUP BY PAY_TAXES_ORGNAME,LEFT(INVOICE_OPEN_DATE,7) ,INVOICE_STATE,INVOICE_TYPE
) AA
GROUP BY AA.PAY_TAXES_ORGNAME,AA.MM
ORDER BY AA.PAY_TAXES_ORGNAME
;