求一SQL
select 姓名，金额，月份 from TABLE where 月份=1
姓名 金额 月份
aa 100 1
bb 101 1
cc 102 1
select 姓名，金额，月份 from TABLE where 月份=2
姓名 金额 月份
aa 110 2
bb 120 2
cc 90 2
想要出下面的结果:2月分,和1月份比较
姓名 金额 月份 增减 排名
aa 110 2 增 2
bb 121 2 增 1
cc 90 2 减 3
问题点数：20、回复次数：5Top
1 楼rea1gz（冒牌realgz V0.4）回复于 2006-12-01 11:52:52 得分 7
select
t2.*
case when t2.金额>=t1.金额 then '增' else '减' end as 增减,
(select count(*) from [table] where 月份=t2.月份 and 金额 <=t2.金额) as 排名
from
[table] t1,
[table] t2
where
t1.姓名=t2.姓名 and
t1.月份=1 and
t2.月份=2
Top
2 楼rea1gz（冒牌realgz V0.4）回复于 2006-12-01 11:53:19 得分 0
PS:
姓名 金额 月份 增减 排名
aa 110 2 增 2
bb 120 2 增 1
cc 90 2 减 3
Top
3 楼hhhdyj（萤火虫）回复于 2006-12-01 11:56:08 得分 6
DECLARE @ta TABLE([姓名] varchar(10), [金额] int, [月份] int)
INSERT INTO @ta
SELECT 'aa', 100, 1
UNION ALL SELECT 'bb', 101, 1
UNION ALL SELECT 'cc', 102, 1
UNION ALL SELECT 'aa', 110, 2
UNION ALL SELECT 'bb', 120, 2
UNION ALL SELECT 'cc', 90, 2
DECLARE @MONTH INT
SET @MONTH = 2
SELECT A.姓名, A.金额, A.月份, CASE WHEN A.金额 > B.金额 THEN '增' WHEN A.金额 < B.金额 THEN '减' ELSE '不变' END, A.MC FROM
(SELECT *,(SELECT COUNT(1) FROM @ta WHERE 月份 = T.月份 AND 金额 >= T.金额) AS MC FROM @ta T WHERE 月份 = @MONTH)A,
(SELECT * FROM @ta WHERE 月份 = @MONTH - 1)B
WHERE A.姓名 = B.姓名Top
4 楼coolingpipe（冷箫轻笛）回复于 2006-12-01 11:58:05 得分 7
select t1.姓名,t1.金额,t1.月份,
case when t1.金额-t2.金额 > 0 then '增' when t1.金额-t2.金额 < 0 then '减' else '平' end as [增减],
排名 = (select count(1) from table where 月份=2 and and 姓名 = t2.姓名 and 金额 >= t1.金额)
from
(select 姓名,金额,月份 from TABLE where 月份=2)t1,
(select 姓名,金额,月份 from TABLE where 月份=1)t2
where t1.姓名 = t2.姓名
Top
5 楼baggio328（将狼踩尽）回复于 2006-12-01 13:38:24 得分 0
select t2.姓名,t2.金额,t2.月份,
case when t2.金额>=t1.金额 then '增' else '减' end as 增减,
(select count(*)
from [table] t left join [table] tt
on tt.月份=t.月份
and tt.金额<=t.金额
where tt.姓名=t2.姓名
and tt.月份=2
) as 排名
from
(select 姓名,金额,月份 from [table] where 月份=1) t1,
(select 姓名,金额,月份 from [table] where 月份=2) t2
where t1.姓名 = t2.姓名
Top
