[求助]怎样在查询里做个排名列？

打字比你快

在查询里把“表1”里的金额进行了统计，统计出来的金额按由大到小顺序排列，现在想在查询表里再加一列排名，排名号由大到小如：1，2，3。想要达到的郊果如“表2”那样。不知能否达到。谢谢

wwwwa

try:

select a.卡号, a.姓名,a.ma,count(*) as pm from (
SELECT 卡号, 姓名, sum(消费金额) as ma
FROM 表1 group by 卡号, 姓名) a left join
(SELECT 卡号, 姓名, sum(消费金额) as ma FROM 表1 group by 卡号, 姓名) b
on a.ma<=b.ma
group by a.卡号, a.姓名,a.ma
order by 4

打字比你快

以下是引用wwwwa在2006-9-7 14:38:00的发言：


try:

select a.卡号, a.姓名,a.ma,count(*) as pm from (
SELECT 卡号, 姓名, sum(消费金额) as ma
FROM 表1 group by 卡号, 姓名) a left join
(SELECT 卡号, 姓名, sum(消费金额) as ma FROM 表1 group by 卡号, 姓名) b
on a.ma<=b.ma
group by a.卡号, a.姓名,a.ma
order by 4

你好，能否再改一下，我想再增加一列，把消费次数合计也加进去，如：他们每人都消费了3次。

wwwwa

你自己改造一下，

SELECT a.卡号, a.姓名, a.ma, a.cs, count(*) AS pm
FROM (SELECT 卡号, 姓名, sum(消费金额) as ma,count(*) as cs
FROM 表1 group by 卡号, 姓名) AS a LEFT JOIN (SELECT 卡号, 姓名, sum(消费金额) as ma FROM 表1 group by 卡号, 姓名) AS b ON a.ma<=b.ma
GROUP BY a.卡号, a.姓名, a.ma, a.cs
ORDER BY 4;

打字比你快

以下是引用wwwwa在2006-9-8 8:07:00的发言：


你自己改造一下，

SELECT a.卡号, a.姓名, a.ma, a.cs, count(*) AS pm
FROM (SELECT 卡号, 姓名, sum(消费金额) as ma,count(*) as cs
FROM 表1 group by 卡号, 姓名) AS a LEFT JOIN (SELECT 卡号, 姓名, sum(消费金额) as ma FROM 表1 group by 卡号, 姓名) AS b ON a.ma<=b.ma
GROUP BY a.卡号, a.姓名, a.ma, a.cs
ORDER BY 4;

谢谢了，OK了。