关于sql 的统计,一个字段,不同类型

Mr_K 发布于 2016/06/06 12:18
阅读 697
收藏 0
SQL

开源软件供应链点亮计划,等你来!>>>

如图下面这张表:

这张表,我需要按照年或者月统计All_Amount字段。但是需要根据AR_OpType字段来,因为 type=14为收入,15为支出,需要用收入-支出来得到结果。条件为Year,Month, HS_ID,HS_Name,HS_Num必须相等。

比如:按照年来统计:2016:天上人间01的收入为90.

按照月来,2016-01:天上人间01的收入为0,2月为90

这个怎么实现?

数据库:sql server

加载中
0
永远在一起

首先计算收入:

SELECT HS_Name
     , SUM(All_Amount) as out_amount
FROM TableName
WHERE AR_OpType == 14
GROUP BY HS_Name
其次,计算支出
SELECT HS_Name
     , SUM(All_Amount) as out_amount
FROM TableName
WHERE AR_OpType == 15
GROUP BY HS_Name
收入和支出合并:
SELECT HS_Name
     , in_amount
     , out_amount
     , NVL(in_amount) - NVL(out_amount) as result;
FORM (
   SELECT HS_Name
     , SUM(All_Amount) as in_amount
   FROM TableName
   WHERE AR_OpType == 14
   GROUP BY HS_Name
) A FULL JOIN (
   SELECT HS_Name
     , SUM(All_Amount) as out_amount
   FROM TableName
   WHERE AR_OpType == 15
   GROUP BY HS_Name
) B 
  ON ( A.HS_Name == B.HS_Name )



result列就是结果,请将HS_Name替换成你需要的三个列,NVL函数替换为SQL Server中对应的函数体。
0
A
Alps1004

select Year,Month,HS_ID,HS_Name,HS_Num,
sum(all_amount*(29-ar_optype*2)) amt 
from tableName 
where condition 
--如果ar_optype 包含其他类型, 那么这里加(ar_optype=14 or ar_optype=15)
group by Year,Month,HS_ID,HS_Name,HS_Num



0
jolphin
jolphin
SELECT YEAR,MONTH,HS_ID,HS_NAME,HS_NUM,
       SUM(CASE WHEN AR_OPTYPE = 14 THEN ISNULL(ALL_AMOUNT,0)
		        WHEN AR_OPTYPE = 15 THEN ISNULL(ALL_AMOUNT,0)*-1
		        ELSE 0 
	        END) TOTAL_AMOUNT
FROM TAB
WHERE AR_OPTYPE IN (14,15)
GROUP BY YEAR,MONTH,HS_ID,HS_NAME,HS_NUM;



0
beyondforever68
beyondforever68
少年,你是不是去了很多次天上人间
OSCHINA
登录后可查看更多优质内容
返回顶部
顶部