mysql - 如何根据另一个表的值更新一个表的所有行？

Question

我有一个这样的表:

// requests
+----+----------------+----------------+-------------+
| id | user_id        |      ip        |  unix_time  |
+----+----------------+----------------+-------------+
| 1  | 12353          | NULL           | 1339412843  |
| 2  | 12353          | NULL           | 1339412864  |
| 3  | NULL           | 178.253.29.175 | 1339412894  |
| 4  | 3422           | NULL           | 1339412899  |
| 5  | 3422           | NULL           | 1339412906  |
| 6  | 3422           | NULL           | 1339412906  |
| 7  | NULL           | 148.23.29.109  | 1339413640  |
| 8  | NULL           | 148.23.29.109  | 1339413621  |
| 9  | 5462           | NULL           | 1339414490  |
| 10 | NULL           | 178.253.29.175 | 1339419901  |
| 11 | 8007           | NULL           | 1339424860  |
| 12 | 8007           | NULL           | 1339424822  |
| 13 | 12353          | NULL           | 1339424902  |
| .  | .              | .              | .           |
| .  | .              | .              | .           |
| .  | .              | .              | .           |
+----+----------------+----------------+-------------+

我还有这张表:

// per_days
+----+---------+--------------+----------------+-----------------+--------------+
| id | user_id |  AllVisited  | MaxConsecutive | LastConsecutive | request_numb |
+----+---------+--------------+----------------+-----------------+--------------+
| 1  | 12353   | 43           | 8              | 3               | 47           |
| 2  | 3422    | 530          | 130            | 32              | 100          |
| .  | .       | .            | .              | .               | .            |
| .  | .       | .            | .              | .               | .            |
| .  | .       | .            | .              | .               | .            |
+----+---------+--------------+----------------+-----------------+--------------+
-- each user has one row into this ^ table (I mean user_id column is unique)

<小时/>

我需要一个查询来选择 requests 表中最后一天的所有行(我将每天通过事件执行该查询)，然后更新所有行per_days 表的行(分别针对每个用户)。像这样:

UPDATE 
     per_days AS p
JOIN requests AS r
ON   p.user_id = r.user_id
SET  p.AllVisited      = p.AllVisited + IF( /* there is a row */, 1, 0),
     p.MaxConsecutive  = IF( p.LastConsecutive > p.MaxConsecutive, LastConsecutive, MaxConsecutive),    
     p.LastConsecutive = IF( /* there is a row */, p.LastConsecutive + 1, 1),
     p.request_numb    = { /* count(1) - the number of all requests (all selected rows) for specific user in this day */ }
WHERE r.unix_time > subdate(now(), interval '1' day)

如何解决我的查询？

Answer 1

连接一个子查询，该子查询返回时间范围内requests中的所有用户ID。使用LEFT JOIN，然后您的if there's a row测试将变为r.user_id IS NOT NULL。

UPDATE 
     per_days AS p
LEFT JOIN (
    SELECT DISTINCT user_id
    FROM requests 
    WHERE unix_time > subdate(now(), interval '1' day)) AS r
ON   p.user_id = r.user_id
SET  p.AllVisited      = p.AllVisited + IF(r.user_id IS NOT NULL, 1, 0),
     p.MaxConsecutive  = IF( p.LastConsecutive > p.MaxConsecutive, LastConsecutive, MaxConsecutive),    
     p.LastConsecutive = IF(r.user_id IS NOT NULL, p.LastConsecutive + 1, 1)