我的语句如下: SELECT TaxPayerID, TaxPayerName, iYear, BankName, TaxDeptName,
(CASE WHEN imonth = '1' AND CancelOrNot = 20 THEN taxmoney ELSE 0 END)
AS Jan1, (CASE WHEN imonth = '1' THEN taxmoney ELSE 0 END) AS Jan0,
(CASE WHEN imonth = '2' AND CancelOrNot = 20 THEN taxmoney ELSE 0 END)
AS Feb1, (CASE WHEN imonth = '2' THEN taxmoney ELSE 0 END) AS Feb0,
(CASE WHEN imonth = '3' AND CancelOrNot = 20 THEN taxmoney ELSE 0 END)
AS Mar1, (CASE WHEN imonth = '3' THEN taxmoney ELSE 0 END) AS Mar0,
(CASE WHEN imonth = '4' AND CancelOrNot = 20 THEN taxmoney ELSE 0 END)
AS Apr1, (CASE WHEN imonth = '4' THEN taxmoney ELSE 0 END) AS Apr0,
(CASE WHEN imonth = '5' AND CancelOrNot = 20 THEN taxmoney ELSE 0 END)
AS May1, (CASE WHEN imonth = '5' THEN taxmoney ELSE 0 END) AS May0,
(CASE WHEN imonth = '6' AND CancelOrNot = 20 THEN taxmoney ELSE 0 END)
AS Jun1, (CASE WHEN imonth = '6' THEN taxmoney ELSE 0 END) AS Jun0,
(CASE WHEN imonth = '7' AND CancelOrNot = 20 THEN taxmoney ELSE 0 END)
AS Jul1, (CASE WHEN imonth = '7' THEN taxmoney ELSE 0 END) AS Jul0,
(CASE WHEN imonth = '8' AND CancelOrNot = 20 THEN taxmoney ELSE 0 END)
AS Aug1, (CASE WHEN imonth = '8' THEN taxmoney ELSE 0 END) AS Aug0,
(CASE WHEN imonth = '9' AND CancelOrNot = 20 THEN taxmoney ELSE 0 END)
AS Sep1, (CASE WHEN imonth = '9' THEN taxmoney ELSE 0 END) AS Sep0,
(CASE WHEN imonth = '10' AND CancelOrNot = 20 THEN taxmoney ELSE 0 END)
AS Oct1, (CASE WHEN imonth = '10' THEN taxmoney ELSE 0 END) AS Oct0,
(CASE WHEN imonth = '11' AND CancelOrNot = 20 THEN taxmoney ELSE 0 END)
AS Nov1, (CASE WHEN imonth = '11' THEN taxmoney ELSE 0 END) AS Nov0,
(CASE WHEN imonth = '12' AND CancelOrNot = 20 THEN taxmoney ELSE 0 END)
AS Dec1, (CASE WHEN imonth = '12' THEN taxmoney ELSE 0 END) AS Dec0,
FROM V_CancelRep1
GROUP BY iYear, TaxPayerID, TaxPayerName, BankName, TaxDeptName, TaxMoney,
iMonth, CancelOrNot 但是这样统计出来同一单位的信息有多条,如果12个月都有的话就有12条,如下,数值为7月份和8月份的,并非在一行统计,求教请问这样的语句怎么修改 340111849021476 安徽省电力公司合肥供电公司2008 中国工商银行合肥市四牌楼支行 合肥市包河区国家税务局 0 0 0 0 0 0 100 0 0 0 0 0
340111849021476 安徽省电力公司合肥供电公司2008 中国工商银行合肥市四牌楼支行 合肥市包河区国家税务局 0 0 0 0 0 0 0 100 0 0 0 0
(CASE WHEN imonth = '1' AND CancelOrNot = 20 THEN taxmoney ELSE 0 END)
AS Jan1, (CASE WHEN imonth = '1' THEN taxmoney ELSE 0 END) AS Jan0,
(CASE WHEN imonth = '2' AND CancelOrNot = 20 THEN taxmoney ELSE 0 END)
AS Feb1, (CASE WHEN imonth = '2' THEN taxmoney ELSE 0 END) AS Feb0,
(CASE WHEN imonth = '3' AND CancelOrNot = 20 THEN taxmoney ELSE 0 END)
AS Mar1, (CASE WHEN imonth = '3' THEN taxmoney ELSE 0 END) AS Mar0,
(CASE WHEN imonth = '4' AND CancelOrNot = 20 THEN taxmoney ELSE 0 END)
AS Apr1, (CASE WHEN imonth = '4' THEN taxmoney ELSE 0 END) AS Apr0,
(CASE WHEN imonth = '5' AND CancelOrNot = 20 THEN taxmoney ELSE 0 END)
AS May1, (CASE WHEN imonth = '5' THEN taxmoney ELSE 0 END) AS May0,
(CASE WHEN imonth = '6' AND CancelOrNot = 20 THEN taxmoney ELSE 0 END)
AS Jun1, (CASE WHEN imonth = '6' THEN taxmoney ELSE 0 END) AS Jun0,
(CASE WHEN imonth = '7' AND CancelOrNot = 20 THEN taxmoney ELSE 0 END)
AS Jul1, (CASE WHEN imonth = '7' THEN taxmoney ELSE 0 END) AS Jul0,
(CASE WHEN imonth = '8' AND CancelOrNot = 20 THEN taxmoney ELSE 0 END)
AS Aug1, (CASE WHEN imonth = '8' THEN taxmoney ELSE 0 END) AS Aug0,
(CASE WHEN imonth = '9' AND CancelOrNot = 20 THEN taxmoney ELSE 0 END)
AS Sep1, (CASE WHEN imonth = '9' THEN taxmoney ELSE 0 END) AS Sep0,
(CASE WHEN imonth = '10' AND CancelOrNot = 20 THEN taxmoney ELSE 0 END)
AS Oct1, (CASE WHEN imonth = '10' THEN taxmoney ELSE 0 END) AS Oct0,
(CASE WHEN imonth = '11' AND CancelOrNot = 20 THEN taxmoney ELSE 0 END)
AS Nov1, (CASE WHEN imonth = '11' THEN taxmoney ELSE 0 END) AS Nov0,
(CASE WHEN imonth = '12' AND CancelOrNot = 20 THEN taxmoney ELSE 0 END)
AS Dec1, (CASE WHEN imonth = '12' THEN taxmoney ELSE 0 END) AS Dec0,
FROM V_CancelRep1
GROUP BY iYear, TaxPayerID, TaxPayerName, BankName, TaxDeptName, TaxMoney,
iMonth, CancelOrNot 但是这样统计出来同一单位的信息有多条,如果12个月都有的话就有12条,如下,数值为7月份和8月份的,并非在一行统计,求教请问这样的语句怎么修改 340111849021476 安徽省电力公司合肥供电公司2008 中国工商银行合肥市四牌楼支行 合肥市包河区国家税务局 0 0 0 0 0 0 100 0 0 0 0 0
340111849021476 安徽省电力公司合肥供电公司2008 中国工商银行合肥市四牌楼支行 合肥市包河区国家税务局 0 0 0 0 0 0 0 100 0 0 0 0
解决方案 »
- delphi 5 安装控件报错:can't load package XXX.bpl 执行页内操作时的错误
- 关于TForm透明属性的效率问题
- excel表中的显示问题,请多多指教!!!
- 执行存储过程错:List index out of bounds(0)
- access我在从另一个表里取数据出问题了,救我啊!
- 怎样才能判断出一个变量中的字符串是否是汉字?
- 程序使用Access数据库发布时需要附加哪些文件???
- 如何通过UDP实现两个通过代理服务器连接的机器的连接,像QQ那样!!
- win32和win16中的32和16是指CPU的字长吗?
- 不容易,终于将在ListBox中改变行的颜色搞清楚。附源代码!
- 怎样在Label或Edit组件中显示上、下箭头?
- 简单的翻译题(1题10分,谢谢)
GROUP BY iYear, TaxPayerID, TaxMoney, iMonth在和 TaxPayerID的数据库关联
SELECT iYear, TaxPayerID, TaxPayerName, BankName, TaxDeptName, TaxMoney,CancelOrNot,
SUM(JAN1)JAN1,SUM(JAN0)JAN0......(后面的类似)
FROM
(SELECT TaxPayerID, TaxPayerName, iYear, BankName, TaxDeptName,
(CASE WHEN imonth = '1' AND CancelOrNot = 20 THEN taxmoney ELSE 0 END)
AS Jan1, (CASE WHEN imonth = '1' THEN taxmoney ELSE 0 END) AS Jan0,
(CASE WHEN imonth = '2' AND CancelOrNot = 20 THEN taxmoney ELSE 0 END)
AS Feb1, (CASE WHEN imonth = '2' THEN taxmoney ELSE 0 END) AS Feb0,
(CASE WHEN imonth = '3' AND CancelOrNot = 20 THEN taxmoney ELSE 0 END)
AS Mar1, (CASE WHEN imonth = '3' THEN taxmoney ELSE 0 END) AS Mar0,
(CASE WHEN imonth = '4' AND CancelOrNot = 20 THEN taxmoney ELSE 0 END)
AS Apr1, (CASE WHEN imonth = '4' THEN taxmoney ELSE 0 END) AS Apr0,
(CASE WHEN imonth = '5' AND CancelOrNot = 20 THEN taxmoney ELSE 0 END)
AS May1, (CASE WHEN imonth = '5' THEN taxmoney ELSE 0 END) AS May0,
(CASE WHEN imonth = '6' AND CancelOrNot = 20 THEN taxmoney ELSE 0 END)
AS Jun1, (CASE WHEN imonth = '6' THEN taxmoney ELSE 0 END) AS Jun0,
(CASE WHEN imonth = '7' AND CancelOrNot = 20 THEN taxmoney ELSE 0 END)
AS Jul1, (CASE WHEN imonth = '7' THEN taxmoney ELSE 0 END) AS Jul0,
(CASE WHEN imonth = '8' AND CancelOrNot = 20 THEN taxmoney ELSE 0 END)
AS Aug1, (CASE WHEN imonth = '8' THEN taxmoney ELSE 0 END) AS Aug0,
(CASE WHEN imonth = '9' AND CancelOrNot = 20 THEN taxmoney ELSE 0 END)
AS Sep1, (CASE WHEN imonth = '9' THEN taxmoney ELSE 0 END) AS Sep0,
(CASE WHEN imonth = '10' AND CancelOrNot = 20 THEN taxmoney ELSE 0 END)
AS Oct1, (CASE WHEN imonth = '10' THEN taxmoney ELSE 0 END) AS Oct0,
(CASE WHEN imonth = '11' AND CancelOrNot = 20 THEN taxmoney ELSE 0 END)
AS Nov1, (CASE WHEN imonth = '11' THEN taxmoney ELSE 0 END) AS Nov0,
(CASE WHEN imonth = '12' AND CancelOrNot = 20 THEN taxmoney ELSE 0 END)
AS Dec1, (CASE WHEN imonth = '12' THEN taxmoney ELSE 0 END) AS Dec0,
FROM V_CancelRep1 ) A
GROUP BY iYear, TaxPayerID, TaxPayerName, BankName, TaxDeptName, TaxMoney,CancelOrNot
group by Month