Office中国论坛/Access中国论坛

标题: [求助]怎样在查询里做个排名列？ [打印本页]

作者: 打字比你快 时间: 2006-9-7 22:04
标题: [求助]怎样在查询里做个排名列？
在查询里把“表1”里的金额进行了统计，统计出来的金额按由大到小顺序排列，现在想在查询表里再加一列排名，排名号由大到小如：1，2，3。想要达到的郊果如“表2”那样。不知能否达到。谢谢[attach]20203[/attach]

作者: wwwwa 时间: 2006-9-7 22:38
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

作者: 打字比你快 时间: 2006-9-8 06:32

以下是引用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 时间: 2006-9-8 16:07
你自己改造一下，

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;

作者: 打字比你快 时间: 2006-9-8 16:52

以下是引用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了。

欢迎光临 Office中国论坛/Access中国论坛 (http://www.office-cn.net/)