【网学网提醒】：网学会员鉴于大家对十分关注，论文会员在此为大家搜集整理了“SQL统计月”一文，供大家参考学习
SELECT
BMAS收费单位编码,
MCAS收费单位名称,
xmbmAS项目编码,
xmmcAS项目名称,
hjAS合计,
m1AS一月,
m2AS二月,
m3AS三月,
m4AS四月,
m5AS五月,
m6AS六月,
m7AS七月,
m8AS八月,
m9AS九月,
m10AS十月,
m11AS十一月,
m12AS十二月
FROM
(
SELECT
x3.agencycodeASBM,
x3.agencynameASMC,
x5.nontaxcodeASxmbm,
x5.nontaxnameASxmmc,
sum(amt)AShj,
CASEWHENstrftime('%m',X1.tradedate)='01'THENsum(X2.AMT)ENDASm1,
CASEWHENstrftime('%m',X1.tradedate)='02'THENsum(X2.AMT)ENDASm2,
CASEWHENstrftime('%m',X1.tradedate)='03'THENsum(X2.AMT)ENDASm3,
CASEWHENstrftime('%m',X1.tradedate)='04'THENsum(X2.AMT)ENDASm4,
CASEWHENstrftime('%m',X1.tradedate)='05'THENsum(X2.AMT)ENDASm5,
CASEWHENstrftime('%m',X1.tradedate)='06'THENsum(X2.AMT)ENDASm6,
CASEWHENstrftime('%m',X1.tradedate)='07'THENsum(X2.AMT)ENDASm7,
CASEWHENstrftime('%m',X1.tradedate)='08'THENsum(X2.AMT)ENDASm8,
CASEWHENstrftime('%m',X1.tradedate)='09'THENsum(X2.AMT)ENDASm9,
CASEWHENstrftime('%m',X1.tradedate)='10'THENsum(X2.AMT)ENDASm10,
CASEWHENstrftime('%m',X1.tradedate)='11'THENsum(X2.AMT)ENDASm11,
CASEWHENstrftime('%m',X1.tradedate)='12'THENsum(X2.AMT)ENDASm12
FROM
COL_TRADEX1
LEFTJOINCOL_TRADE_DETAILX2ONx1.guid=x2.tradeid
INNERJOINBAS_ChargeAgencyX3ONX1.agencyid=X3.guid
INNERJOINBAS_ChargeProjectX5ONx2.proid=x5.guid
INNERJOINBAS_RemittedBankX6ONx1.bankid=x6.guid
INNERJOINBAS_CHGAGENCY2CHGPROT11ONT11.CHARGEAGENCYID=x1.agencyid
ANDT11.CHARGEPROJECTID=x2.proid
INNERJOINBAS_RemittedBankT13ONx1.bankid=T13.Guid
LEFTJOINbas_fundtypefeONx5.fundtypeguid=fe.Guid
LEFTJOINbas_chargetypectONx5.chargetypeguid=ct.guid
WHERE
1=1
ANDx1.charged='1'
ANDx1.audited='1'
ANDx3.agencycodeLIKE'%'
ANDx5.nontaxcodeLIKE'%'
ANDx1.CHARGEDATE>='2012-02-1700:00:00'
ANDx1.CHARGEDATE<='2012-02-1723:59:59'
ANDX2.NUMLIKE'%'
ANDX2.AmtLIKE'%'
ANDCASE
WHENx3.agencycodeISNULLTHEN
''
ELSE
x3.agencycode
ENDLIKE'%'
ANDCASE
WHENfe.FUNDTYPECODEISNULLTHEN
''
ELSE
fe.FUNDTYPECODE
ENDLIKE'%'
ANDCASE
WHENct.chargetypecodeISNULLTHEN
''
ELSE
ct.chargetypecode
ENDLIKE'%'
GROUPBY
x3.agencycode,
x3.agencyname,
x5.nontaxcode,
x5.nontaxname,
x6.bankcode,
x6.bankname
UNIONALL
SELECT
x3.agencycodeASBM,
x3.agencynameASMC,
''ASxmbm,
'单位小计'ASxmmc,
sum(amt)AShj,
CASEWHENstrftime('%m',X1.tradedate)='01'THENsum(X2.AMT)ENDASm1,
CASEWHENstrftime('%m',X1.tradedate)='02'THENsum(X2.AMT)ENDASm2,
CASEWHENstrftime('%m',X1.tradedate)='03'THENsum(X2.
AMT)ENDASm3,
CASEWHENstrftime('%m',X1.tradedate)='04'THENsum(X2.AMT)ENDASm4,
CASEWHENstrftime('%m',X1.tradedate)='05'THENsum(X2.AMT)ENDASm5,
CASEWHENstrftime('%m',X1.tradedate)='06'THENsum(X2.AMT)ENDASm6,
CASEWHENstrftime('%m',X1.tradedate)='07'THENsum(X2.AMT)ENDASm7,
CASEWHENstrftime('%m',X1.tradedate)='08'THENsum(X2.AMT)ENDASm8,
CASEWHENstrftime('%m',X1.tradedate)='09'THENsum(X2.AMT)ENDASm9,
CASEWHENstrftime('%m',X1.tradedate)='10'THENsum(X2.AMT)ENDASm10,
CASEWHENstrftime('%m',X1.tradedate)='11'THENsum(X2.AMT)ENDASm11,
CASEWHENstrftime('%m',X1.tradedate)='12'THENsum(X2.AMT)ENDASm12
FROM
COL_TRADEX1
LEFTJOINCOL_TRADE_DETAILX2ONx1.guid=x2.tradeid
INNERJOINBAS_ChargeAgencyX3ONX1.agencyid=X3.guid
INNERJOINBAS_ChargeProjectX5ONx2.proid=x5.guid
INNERJOINBAS_RemittedBankX6ONx1.bankid=x6.guid
INNERJOINBAS_CHGAGENCY2CHGPROT11ONT11.CHARGEAGENCYID=x1.agencyid
ANDT11.CHARGEPROJECTID=x2.proid
INNERJOINBAS_RemittedBankT13ONx1.bankid=T13.Guid
LEFTJOINbas_fundtypefeONx5.fundtypeguid=fe.Guid
LEFTJOINbas_chargetypectONx5.chargetypeguid=ct.guid
WHERE
1=1
ANDx1.charged='1'
ANDx1.audited='1'
ANDx3.agencycodeLIKE'%'
ANDx5.nontaxcodeLIKE'%'
ANDx1.CHARGEDATE>='2012-02-1700:00:00'
ANDx1.CHARGEDATE<='2012-02-1723:59:59'
ANDX2.NUMLIKE'%'
ANDX2.AmtLIKE'%'
ANDCASE
WHENx3.agencycodeISNULLTHEN
''
ELSE
x3.agencycode
ENDLIKE'%'
ANDCASE
WHENfe.FUNDTYPECODEISNULLTHEN
''
ELSE
fe.FUNDTYPECODE
ENDLIKE'%'
ANDCASE
WHENct.chargetypecodeISNULLTHEN
''
ELSE
ct.chargetypecode
ENDLIKE'%'
GROUPBY
x3.agencycode,
x3.agencyname,
x5.nontaxcode,
x5.nontaxname,
x6.bankcode,
x6.bankname
)
WHERE
1=1
ORDERBY
BM,
xmbm
