此类问题之前已被问过几次,但并不完全是我想要的。我需要SET
两行等于子查询的不同部分。
我目前正在使用:
UPDATE records
SET leads=(SELECT COUNT(*) FROM leads_table WHERE leads_table.blah=records.blah),
earnings=(SELECT SUM(amount) FROM leads_table WHERE leads_table.blah=records.blah)
WHERE 语句显然被简化了......但基本上它是相同的子查询,但我认为我不应该运行它两次?
我想做一些像...
UPDATE records
SET (leads,earnings)=(SELECT COUNT(*),SUM(amount) FROM leads_table WHERE leads_table.blah=records.blah)
您可以简单地将表连接到执行一些计算的子查询中,
UPDATE records a
INNER JOIN
(
SELECT blah,
COUNT(*) totalCount,
SUM(amount) totalSum
FROM leads_table
GROUP BY blah
) b ON b.blah = a.blah
SET a.leads = b.totalCount
a.earnings = b.totalSum
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)