SQL LEFT JOIN SUM()函数怎么用
A表为用户表,B表为用户消费记录表,查询A表全部,在混合查询结果里添加一个字段显示用户已经消费了多少金额。SELECTA.*,B.uid,B.moneyashejiFRO...
A表为用户表,B表为用户消费记录表,查询A表全部,在混合查询结果里添加一个字段显示用户已经消费了多少金额。
SELECT A.*,B.uid,B.money as heji FROM A LEFT JOIN (SELECT uid,sum(money) FROM A) b ON A.uid=B.uid
这样写出错不知错在哪里!还有表B的条件加在哪里?
SELECT go_member.*,go_member_account.uid,go_member_account.money as heji FROM `go_member` LEFT JOIN (SELECT uid,sum(money) FROM go_member_account ) b ON go_member.uid=go_member_account.uid 展开
SELECT A.*,B.uid,B.money as heji FROM A LEFT JOIN (SELECT uid,sum(money) FROM A) b ON A.uid=B.uid
这样写出错不知错在哪里!还有表B的条件加在哪里?
SELECT go_member.*,go_member_account.uid,go_member_account.money as heji FROM `go_member` LEFT JOIN (SELECT uid,sum(money) FROM go_member_account ) b ON go_member.uid=go_member_account.uid 展开
1个回答
推荐于2017-09-07
展开全部
SELECT A.*,B.uid,B.money as heji FROM A
LEFT JOIN (SELECT uid,sum(money) FROM A) b ON A.uid=B.uid
解析
SELECT uid,sum(money) FROM A
sum()为集合函数 查询多个列时需要使用GROUP BY
正确语法
SELECT uid,sum(money) FROM A GROUP BY uid
第二条SQL语句
SELECT go_member.*,go_member_account.uid,go_member_account.money as heji
FROM `go_member` LEFT JOIN
(SELECT uid,sum(money) FROM go_member_account ) b
ON go_member.uid=go_member_account.uid
解析
ON go_member.uid=go_member_account.uid
条件错误
LEFT JOIN
(SELECT uid,sum(money) FROM go_member_account ) b
子查询 相当于把表go_member_account
复制给B了 这个表go_member_account不存在
ON go_member.uid=b.uid
追答
SELECT * FROM go_member A LEFT JOIN
(SELECT 用户ID,sum(消费金额) FROM go_member_account GROUP BY 用户ID) B
ON A.用户ID=B.用户ID
上面没有消费记录的消费金额为0
SELECT A.用户ID,SUM(消费金额) FROM go_member_account A,go_member B
WHERE A.用户ID=B.用户ID
GROUP BY A.用户ID
上面没有消费的不会显示
推荐律师服务:
若未解决您的问题,请您详细描述您的问题,通过百度律临进行免费专业咨询