一个脚本实现单表多公司按年月统计报表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
;