mysql update from 子查询_MySQL：从子查询结果更新表列

在Members表上有“MemberID”和“PointsEarned”列.

我想从此查询的结果更新PointsEarned列：

SELECT m.MemberID, m.UserName,

( (SELECT COUNT(*) FROM EventsLog as e WHERE e.MemberID=m.MemberID AND e.EventsTypeID=2)*10 ) +

( (SELECT COUNT(*) FROM EventsLog as e WHERE e.MemberID=m.MemberID AND e.EventsTypeID=3)*3 ) +

( (SELECT COUNT(*) FROM ChatMessages as c WHERE c.MemberID=m.MemberID)*.1 )

as PointsEarned

FROM Members as m

任何人都能告诉我如何使用单个查询来完成它吗？

谢谢！

解决方法:

您可能想尝试以下方法：

UPDATE Members m

SET PointsEarned =

( (SELECT COUNT(*) FROM EventsLog as e WHERE e.MemberID=m.MemberID AND e.EventsTypeID=2)*10 ) +

( (SELECT COUNT(*) FROM EventsLog as e WHERE e.MemberID=m.MemberID AND e.EventsTypeID=3)*3 ) +

( (SELECT COUNT(*) FROM ChatMessages as c WHERE c.MemberID=m.MemberID)*.1 );

测试用例：

CREATE TABLE Members (MemberId int, PointsEarned int);

CREATE TABLE EventsLog (MemberID int, EventsTypeID int);

CREATE TABLE ChatMessages (MemberID int);

INSERT INTO Members VALUES (1, 0);

INSERT INTO Members VALUES (2, 0);

INSERT INTO Members VALUES (3, 0);

INSERT INTO EventsLog VALUES (1, 2);

INSERT INTO EventsLog VALUES (1, 2);

INSERT INTO EventsLog VALUES (1, 3);

INSERT INTO EventsLog VALUES (2, 2);

INSERT INTO EventsLog VALUES (3, 3);

INSERT INTO ChatMessages VALUES (1);

INSERT INTO ChatMessages VALUES (1);

INSERT INTO ChatMessages VALUES (2);

结果：

SELECT * FROM Members;

+----------+--------------+

| MemberId | PointsEarned |

+----------+--------------+

| 1 | 23 |

| 2 | 10 |

| 3 | 3 |

+----------+--------------+

3 rows in set (0.00 sec)

标签：mysql,sql-update

来源： https://codeday.me/bug/20190606/1190626.html