大家帮小妹一把,关于表内的统计,谢谢大家,多帮忙呀!
有一张表
客户编号           月份         消费额
    1                   200504             20
    1                   200505             30
    2                   200504             30
    2                   200505             70
    3                   200504             30
    3                   200505             10

想要求2005年4月比2005月5月消费额增长的记录
返回的结果是:
1       10
2       40


------解决方案--------------------
select
客户编号,
sum(case when 月份=200505 then 消费额 when 月份=200504 then -1*消费额 end ) as 差额
from T
group by 客户编号
having sum(case when 月份=200505 then 消费额 when 月份=200504 then -1*消费额 end )> 0

------解决方案--------------------
create table T(客户编号 int,月份 int,消费额 int)

insert into T select 1,200504,20
insert into T select 1,200505,30
insert into T select 2,200504,30
insert into T select 2,200505,70
insert into T select 3,200504,30
insert into T select 3,200505,10


select
客户编号,
sum(case when 月份=200505 then 消费额 when 月份=200504 then -1*消费额 end ) as 差额
from T
group by 客户编号
having sum(case when 月份=200505 then 消费额 when 月份=200504 then -1*消费额 end )> 0

drop table T
------解决方案--------------------
借用下楼上的数据.
=================================================
create table T(客户编号 int,月份 int,消费额 int)

insert into T select 1,200504,20
insert into T select 1,200505,30
insert into T select 2,200504,30
insert into T select 2,200505,70
insert into T select 3,200504,30
insert into T select 3,200505,10

select a.月份,a.客户编号,sum(a.消费额) - sum(t.消费额) as '消费额增长 '
from t,t a
where t.客户编号 = a.客户编号 and
t.月份 = 200504 and
a.月份 = 200505
group by a.客户编号,a.月份
having sum(a.消费额) - sum(t.消费额) > 0

drop table T

如果数据量大的话,最好不要用case,数据少就无所谓了.
------解决方案--------------------
CREATE TABLE #T(客户编号 int,月份 nchar(6),消费额 int)

INSERT INTO #T
SELECT 1, '200504 ',20 UNION ALL
SELECT 1, '200505 ',30 UNION ALL
SELECT 2, '200504 ',30 UNION ALL
SELECT 2, '200505 ',70 UNION ALL
SELECT 3, '200504 ',30 UNION ALL
SELECT 3, '200505 ',10

DECLARE @YearMonth1 nchar(6)
DECLARE @YearMonth2 nchar(6)
SELECT @YearMonth1= '200504 ',@YearMonth2= '200505 '

SELECT A.[客户编号],B.[消费额]-A.[消费额] AS [消费额] FROM
(SELECT [客户编号],SUM([消费额]) AS [消费额]
FROM #T WHERE [月份]=@YearMonth1 GROUP BY [客户编号]) AS A
INNER JOIN
(SELECT [客户编号],SUM([消费额]) AS [消费额]
FROM #T WHERE [月份]=@YearMonth2 GROUP BY [客户编号]) AS B
ON A.[客户编号]=B.[客户编号] AND B.[消费额]> A.[消费额]
/*
1 10
2 40
*/

DROP TABLE #T
------解决方案--------------------