php - 请教大家三表连接查询的sql怎么写-PHP中文网问答

文章专题 AI工具学习下载问答源码最近更新

PHP

会员中心讲师中心微信公众号

php - 请教大家三表连接查询的sql怎么写

PHPz 2017-04-10 15:15:05

[PHP讨论组]

778

有如下三张表(第一行为字段名)

user用户表:

id	name
1	aaa
2	bbb
3	ccc

chargeRecord充值记录表:

id	userId	money
1	1	10
2	1	2
3	2	4
4	2	6

record通话记录表:

id	callId(主叫)	beCallId(被叫)	time(时长)
1	2	1	23
2	3	1	2
3	2	3	16

如何利用user表里面的id 和充值记录表的userId 以及record表的callId/beCallId做连接
查询用户名，充值总金额，和通话总时长(主叫时长)
新手谢过大家了！！

PHPz

学习是最好的投资！

全部回复(2)

PHP中文网2017-04-10 15:17:05 2楼

SELECT user.id AS id, user.name AS name, charge.money AS money, record.time AS time
FROM user
LEFT JOIN (SELECT userId, SUM(money) AS money FROM chargerecord GROUP BY userId) AS charge ON user.id = charge.userId
LEFT JOIN (SELECT callId, SUM(time) AS time FROM record GROUP BY callId) AS record ON user.id = record.callId
ORDER BY user.id