表A:
uid name
---- ------
1 admin
2 user表B:
tid uid title date
---- --- ------ ----------
1 2 aaa 2010-07-01
2 2 bbb 2010-07-02
3 1 ccc 2010-08-01
4 2 ddd 2010-08-02
5 1 eee 2010-08-03
6 2 fff 2010-08-04
7 2 ggg 2010-08-05输出结果:
uid name count
---- ---- -----
2 user 5
1 admin 2
根据A表查询B表的数量,以统计的到数量排序...
不知道是否能用一条SQL语句取得结果.
是否能按日期统计?
from 表A inner join 表B on 表A.uid=表B.uid
group by 表A.uid,表A.name
order by 3 desc
FROM a LEFT JOIN b ON a.uid=b.uid
GROUP BY a.uid a.name
ORDER BY `count` DESC --这个按姓名 日期分组
SELECT a.uid a.name,b.date,count(b.tid) AS `count`
FROM a JOIN b ON a.uid=b.uid
GROUP BY a.uid a.name,b.date
ORDER BY `count` DESC