表caifujifen结构如下:id to2 cons
1 2 30
2 2 20
3 3 30
4 3 40表member结构如下user_id username
2 admin
3 vipstar其中 caifujifen.to2对应member.user_id想要实现分别列出 to2中 2、3用户的积分 (cons) 总合,以用户名的形式表现出来,结果现实如下
admin 50
vipstar 70请给出代码及思路,谢谢
1 2 30
2 2 20
3 3 30
4 3 40表member结构如下user_id username
2 admin
3 vipstar其中 caifujifen.to2对应member.user_id想要实现分别列出 to2中 2、3用户的积分 (cons) 总合,以用户名的形式表现出来,结果现实如下
admin 50
vipstar 70请给出代码及思路,谢谢
left join member m on m.user_id=c.to2
gorup by m.username
SELECT m.username, IFNULL(sum(c.cons), 0) AS cons
FROM member AS m LEFT JOIN caifujifen AS c ON c.to2=m.user_id
WHERE 1 GROUP BY m.username
FROM caifujifen AS A JOIN member AS B ON A.id = B.user_id
GROUP BY (A.id)
SELECT m.username, sum( c.cons ) AS cons
FROM caifujifen c
LEFT JOIN member m ON m.user_id = c.to2
GROUP BY c.to2
那么就要对 cons 求和,按 username 分组
FROM member AS m LEFT JOIN caifujifen AS c ON c.to2=m.user_id
WHERE 1 GROUP BY m.username
ORDER BY cons ASC ####DESC 倒序 试试
####WHERE 1 就是全部了,可以省略,你也可以自己加上其他条件,比如member的注册时间等
http://dev.mysql.com/doc/refman/5.1/zh/index.html
select m.username,sum(cons) as '总分' from caifujifen c,member m
where c.to2 = m.user_id group by m.username
谢谢。如果我想查询cons>30的,
这样写
SELECT m.username, IFNULL(sum(c.cons), 0) AS cons
FROM member AS m LEFT JOIN caifujifen AS c ON c.to2=m.user_id
WHERE cons>30 GROUP BY m.username
ORDER BY cons ASC 执行的不对,请指教,,,
(SELECT m.username, IFNULL(sum(c.cons), 0) AS cons
FROM member AS m LEFT JOIN caifujifen AS c ON c.to2=m.user_id
WHERE 1 GROUP BY m.username
ORDER BY cons ASC) AS t
WHERE cons>30