求助一个mysql统计语句,谢谢

[efanlx]

我有两个表,
一个表的结构是
SPID(int) 和 NAME两字段
一个表的结构是
ID SPCOUNT SPID 三字段都是INT

我现在要查询统计相同SPID字段下的SPCOUNT的值,并按统计值的大到小排序.
SPCOUNT的值是无规律的数字


可不可以通过一句语句实现啊?

[crhan]

select a.SPID, cnt = count(b.SPCOUNT)
from tableA as a
inner join tableB as b on a.SPID = b.SPID
group by a.SPID
order by cnt desc

[efanlx]

统计不出来,在线等

[efanlx]

Unknown column 'cnt' in 'field list'

[懒得去死]

1楼的查询修改一下吧。

select a.SPID, count(b.SPCOUNT) as cnt
from tableA as a
inner join tableB as b on a.SPID = b.SPID
group by a.SPID
order by cnt desc