我有两张表。第一张表是 会员数据。 第二张表 是会员发帖的数据。我要给会员发帖排序。就是以发帖量 列出排行。现在可以列出在信息表发过帖子的用户。但是无法列出没有发帖的用户。sql :select u.username,u.userid,p.userid,count(p.userid)as myorder from phome_ecms_photo p , phome_enewsmember u where p.userid = u.userid group by p.userid order by myorder desc 我会员表有10条记录。但是这个sql只能显示 4 条。 也就是在信息表发表过信息的会员。但是没有发布信息的会员就没法显示。求大神完善sql、查询的结果是:username userid userid myorder
admin 1 1 13
admin3 7 7 3
admin2 6 6 2
admin1 5 5 1
--------------------------------我的会员表有10条记录的: userid username
1 admin
2 admin9
3 admin8
4 admin7
5 admin1
6 admin2
7 admin3
8 admin4
9 admin5
10 admin6-------下面是信息表的数据 id userid username
54 1 admin
55 1 admin
56 1 admin
53 1 admin
51 1 admin
50 1 admin
52 1 admin
57 1 admin
58 1 admin
59 1 admin
60 1 admin
61 1 admin
62 1 admin
68 7 admin3
67 7 admin3
66 7 admin3
65 6 admin2
64 6 admin2
63 5 admin1
请帮帮我啊mysql联合查询
admin 1 1 13
admin3 7 7 3
admin2 6 6 2
admin1 5 5 1
--------------------------------我的会员表有10条记录的: userid username
1 admin
2 admin9
3 admin8
4 admin7
5 admin1
6 admin2
7 admin3
8 admin4
9 admin5
10 admin6-------下面是信息表的数据 id userid username
54 1 admin
55 1 admin
56 1 admin
53 1 admin
51 1 admin
50 1 admin
52 1 admin
57 1 admin
58 1 admin
59 1 admin
60 1 admin
61 1 admin
62 1 admin
68 7 admin3
67 7 admin3
66 7 admin3
65 6 admin2
64 6 admin2
63 5 admin1
请帮帮我啊mysql联合查询
from 会员表 B left join 信息表 A on B.userid=A.userid
group by B.userid
from 会员表 B inner join 信息表 A on B.`userid`=A.`userid`
group by B.`username`,A.`userid`,b.`userid`
楼上给的都只会列出4条数据。无法列出全部。SELECT B.`username` , A.`userid` , b.`userid` , count( * ) AS myorder
FROM phome_enewsmember B
INNER JOIN phome_ecms_photo A ON B.`userid` = A.`userid`
GROUP BY B.`username` , A.`userid` , b.`userid`
LIMIT 0 , 30执行后的数据为:
username userid userid myorder
admin 1 1 13
admin1 5 5 1
admin2 6 6 2
admin3 7 7 3我想要的格式为:username userid userid myorder
admin 1 1 13
admin1 5 5 1
admin2 6 6 2
admin3 7 7 3
admin9 2
admin8 3
admin7 4
admin4 8
admin5 9
admin6 10
查询的结果是: username userid userid myorder
admin 1 1 13
admin3 7 7 3
admin2 6 6 2
admin1 5 5 1
--------------------------------
SELECT B.`username` , A.`userid` , b.`userid` , count( * ) AS myorder
FROM phome_enewsmember B
left JOIN phome_ecms_photo A ON B.`userid` = A.`userid`
GROUP BY B.`username` , A.`userid` , b.`userid`
order by 4,2
(select count(*) from phome_ecms_photo where userid = u.userid) as myorder
from phome_enewsmember u
order by 4 desc
from phome_enewsmember u ,phome_ecms_photo p group by u.userid order by myorder desc,u.userid asc
,正确的显示格式为:username userid myorder
admin 1 13
admin3 7 3
admin2 6 2
admin1 5 1
admin9 2 0
admin8 3 0
admin7 4 0
admin4 8 0
admin5 9 0
admin6 10 0
---------------在稍带问下版主,这段sql 还能优化下吗? 谢谢您!