access数据库排序问题，谁来帮帮忙呀？

[zhuangjunx]

access数据库
title abid email
--------------------------------
abc aa [e-mail]
aaa ddd [e-mail]
122 skl [e-mail]
sll ksk [e-mail]
aas kll [e-mail]

怎么按邮箱数量最大的排最前面？？
邮箱记录总数量。

[jobine]

select title, abid, email, count(email) as emailcount
from yourtable
order by emailcount

[zhuangjunx]

这样也不行的。

[pippe]

汗，email有两个，又忘记写表名了

select *
from a=yourtable,b=(select email,emailcount=count(email) from yourtable group by email)
where a.email=b.email
group by b.emailcount desc,a.email;

[zhuangjunx]

FROM 子句语法错误

还是这错误

[jobine]

SELECT A.title, A.abid, A.email, b.emailcount
FROM table1 AS A right JOIN (SELECT email, count(email) as emailcount FROM table1 GROUP BY email) AS B ON A.email=b.email
order by emailcount desc

[zhuangjunx]

SELECT A.title, A.abid, A.email, b.emailcount
FROM table1 AS A right JOIN (SELECT email, count(email) as emailcount FROM table1 GROUP BY email) AS B ON A.email=b.email
order by emailcount desc

这样搞定了，谢谢

[shilei831115]

嘎嘎 麻烦以后大家试试再跟帖 免得误导 处学者

[zhuangjunx]

试试。

[zhuangjunx]

不行呀，我用的是access数据库

[jobine]

我到access上面试过了，完全可行的。

[zhuangjunx]

我要的不是取一条，，是全部数据还是要取出来，只是按邮箱数量最多的排第一位，以此排列下来。。

[jobine]

-_____-你仔细看看我的语句取的就是全部数据。

[jobine]

select email, count(email)
from yourtable
order by count(email)

[jobine]

sorry.写错了。

select title, abid, A.email
from yourtable AS A
right join
(select email
from yourtable
group by email
order by email desc) AS B
on A.email = b.email

[zhuangjunx]

相同邮箱数量最多的不会排在第一位呀。

[pippe]

select *
from a=yourtable,b=(select email,emailcount=count(email) from yourtable)
where a.email=b.email
group by emailcount desc,email;

[zhuangjunx]

FROM 子句语法错误

[yan53125]

支持楼上,不过不用分组,可以用count么?楼上的取出的是每个email的出现次数?还是其他??


select title, abid, A.email
from yourtable AS A
right join
(select email
from yourtable
group by email
order by email desc) AS B
on A.email = b.email
这个应该差不多,只是我不太会右外连接

[pippe]

SORRY,忘记写group by了

select *
from a=yourtable,b=(select email,emailcount=count(email) from yourtable group by email)
where a.email=b.email
group by emailcount desc,email;

[zhuangjunx]

用这个不会按出现次数最多的记录排序。。