①数据库 bet 表 betsid user_name agent_id amount user_amount
1 TS000A01 10 500 1000
2 TS000A02 10 600 2000
3 TS000A03 10 700 3000
4 BT000A01 20 100 200
5 BT000B01 20 200 300
6 BT000C01 20 300 600②数据库 membership 表 usersid name parentid
99 TS000A 10
88 BT000A 20
77 BT000B 20
66 BT000C 20现在我要对bet库的bets表的amount,user_amount两个字段按agent_id分组求和.
条件是bet库bets表的user_name取前6位(如TS000A01取前6位为TS000A)和agent_id到membership库users去对应查找name和parentid.
如果查到则分组求和.
1 TS000A01 10 500 1000
2 TS000A02 10 600 2000
3 TS000A03 10 700 3000
4 BT000A01 20 100 200
5 BT000B01 20 200 300
6 BT000C01 20 300 600②数据库 membership 表 usersid name parentid
99 TS000A 10
88 BT000A 20
77 BT000B 20
66 BT000C 20现在我要对bet库的bets表的amount,user_amount两个字段按agent_id分组求和.
条件是bet库bets表的user_name取前6位(如TS000A01取前6位为TS000A)和agent_id到membership库users去对应查找name和parentid.
如果查到则分组求和.
u.parenttid,
SUM(b.amount),
SUM(b.user_amount)
FROM bet.bets AS b,
membership.users AS u
WHERE u.name = SUBSTR(b.user_name,1,6)
AND u.parentid = b.agent_id
GROUP BY u.parentid