关于统计后进行排名的问题，请指教！(50分)

有个难题请教各位大侠：
TABLE1中的字段：NAME CISHU

我现在用adoquery1: select NAME,sum(CISHU) from TABLE1 group by NAME order by sum(CISHU) desc 语句来
统计每一个人的次数（CISHU），另外，我还想按SUM（CISHU）从高到低进行排名，并用DBGRID显示，如何实现？

select a.name,sum(a.cishu)as cs,
(select count(*)+1 from (select name,sum(cishu)as cs from table1 group by name) b where a.name=b.name and b.sum(cishu)>a.sum(cishu)) as mc
from table1 a
group by a.name

select name,tmp,(select count(name)
from (select name,sum(cishu) as tmp
from table1
group by name
order by sum(cishu) desc) BBB
where tmp>AAA.tmp)+1
from
(select name,sum(cishu) as tmp
from table1
group by name
order by sum(cishu) desc) AAA

QuickSilver的可行，但是如果我想设此表为从表，另一个表（TABLE-MAIN）为主表，关键字段

为ID，如何写SQL呢？我弄了好久，没有成功。怎么办？

作为从表? 你是不是要显示每个人的其它一些信息。
可以这样，在要选择的地方插入子查询。比较要增加年令：

select name,(select age from table2 where table2.id=aaa.id), tmp,(select count(name)
==================================================
。。。。。。。。。。。。。。。。。。。。。。。
在上面的From子句中添加个Id
from
(select name,id,sum(cishu) as tmp
from table1
group by name
order by sum(cishu) desc) AAA
试试看吧

抱歉，昨天你发消息的时候我已经下了，今天才收到

table_main：id,name,other
table1 ：id,cishu

select AAA.id,BBB.name,BBB.other,tmp,(select count(id)
from (select id,sum(cishu) as tmp
from table1
group by id
order by sum(cishu) desc) BBB
where tmp>AAA.tmp)+1
from
(select id,sum(cishu) as tmp
from table1
group by id
order by sum(cishu) desc) AAA,table_main BBB
where AAA.id=BBB.id
order by AAA.tmp desc

不知这是不是你想要的效果，如不是，请列出相应的表结构

table_main：id
1
2
3
table1 ：id,name,cishu 主从表关键字段是id
1 AAA 2
1 AAA 8
1 AAA 1
1 BB 2
1 BB 4
1 CC 1
2 AAA 100
2 BB 200
3 。。。。。。
现在我想统计的是从表（table1）中的每一个人（即name）的总次数(即cishu),并且对他们的次数进行
从高到低排名（列出名次）。
如果当前table_main中的id为 2 则列出如下内容：
name cishu paiming
BB 200 1
AAA 100 2
...................
如果当前table_main中的id为 1 则列出如下内容：
name cishu paiming
AAA 11 1
BB 6 2
CC 1 3

select table1.id,sum(table1.cishu) as cishu from table1 inner join table_main
on table1.id=table_Main.id group by id order by cishui dese

select name,tmp,(select count(name)
from (select name,sum(cishu) as tmp
from table1
where id='1'
group by name
order by sum(cishu) desc) BBB
where tmp>AAA.tmp)+1
from
(select name,sum(cishu) as tmp
from table1
where id='1'
group by name
order by sum(cishu) desc) AAA

id='1'或id='2'，自己把参数带进去就可以了

SELECT NAME, MDD
FROM
(SELECT NAME, SUM(cishu) AS MDD
FROM Table1
WHERE ID= :queryid
GROUP BY TWO)
ORDER BY MDD DESC

如果一定要输出paiming
先建一张表
create table tmp
(
name char(..),
cishu int,
paiming AutoInc
)

insert into tmp (name, cishu)
SELECT NAME, MDD
FROM
(SELECT NAME, SUM(cishu) AS MDD
FROM Table1
WHERE ID= :queryid
GROUP BY TWO)
ORDER BY MDD DESC

select * from tmp order by paiming

：QuickSilver的方法很对，不过子查询太多，数据量大时会影响性能！

to QuickSilver, i m here glad to see u again

最高分给QuickSilver 25分 其它的平均分配，没有意见吧？如果有的话下次我就多给点分，好吗？