mysql 父级和子级的求和sql

查询sql

SELECT z.username,SUM(z.payprice) FROM (
SELECT up.username,SUM(payprice) AS payprice FROM userpay up
JOIN (SELECT username FROM userpay GROUP BY username ) n
WHERE up.username = n.username GROUP BY up.username
UNION ALL
SELECT n.username ,SUM(payprice) AS payprice
FROM userpay up JOIN (SELECT username FROM userpay GROUP BY username) n
WHERE up.`username` IN (SELECT u1.username FROM userinfo u1 WHERE u1.`parent_name` = n.username)
GROUP BY n.username
) z GROUP BY z.username

查询的结果集

 

SELECT * FROM userpay

SELECT * FROM userinfo

 

转载于:https://my.oschina.net/u/1260823/blog/715081