日期:2014-05-19  浏览次数:20559 次

分类汇总再相减如何写?
t1:
dt     decimal(8,0),
tp     char(1),
tp1   char(2),
at     decimal(15,2)

原数据:
20070101     A     A1       10
20070101     A     A2       10
20070101     B     B1       1
20070101     B     B2       1
20070102     A     A1       20
20070102     A     A2       20
20070102     B     B1       2
20070102     B     B2       2

希望结果:
20070101   A-B     18
20070102   A-B     36

------解决方案--------------------

select
dt,
'A-B ' as 公式,
sum(case when tp= 'A ' then [at] when tp= 'B ' then -1*[at] end) as 差额
from 表名
group by dt
------解决方案--------------------
Select
dt,
'A-B ' As [A-B],
SUM(Case tp When 'A ' Then [at] When 'B ' Then - [at] Else 0 End) As [at]
From
t1
Group By
dt
------解决方案--------------------
select dt, 'A-B ',sum(case when tp= 'A ' then [at] else -[at] end)
from t1 group by dt
------解决方案--------------------
select dt, 'A-B ',sum(case when tp= 'A ' then [at] else -[at] end)
from t1 group by dt

------解决方案--------------------
Select dt, 'A-B ' As [A-B],
SUM(Case tp When 'A ' Then [at] When 'B ' Then - [at] Else 0 End) As [at]
From t1 Group By dt

------解决方案--------------------
Create Table t1
(dt decimal(8,0),
tp char(1),
tp1 char(2),
[at] decimal(15,2))
Insert t1 Select 20070101, 'A ', 'A1 ', 10
Union All Select 20070101, 'A ', 'A2 ', 10
Union All Select 20070101, 'B ', 'B1 ', 1
Union All Select 20070101, 'B ', 'B2 ', 1
Union All Select 20070102, 'A ', 'A1 ', 20
Union All Select 20070102, 'A ', 'A2 ', 20
Union All Select 20070102, 'B ', 'B1 ', 2
Union All Select 20070102, 'B ', 'B2 ', 2
select dt, 'A-B ' as '公式 ',sum(case when tp= 'A ' then [at] end ) - sum(case when tp= 'B ' then [at] end ) as num
FROM t1 group by dt