mysql - 有没有办法根据另一列的值更新列值？

Question

我的 SQL 任务的第 1 部分涉及重组数据。我的任务要点如下:根据事件类型，如果它是“开始”，我试图使用那个“时间”来找到它的停止时间(在另一行)并将它添加到与开始时间位于同一行，以便事件的所有数据很好地位于一行中。

pID  customerID  locationID  event_type  time   event_end (new row)
1     1           a          begin       12.45
2     2           a          begin       11.10
3     1           a          stop        1.30
4     2           b          begin       9.45
5     3           b          stop        8.78

我想添加另一列 (event_end)，并让 event_end = event_start 的最小值 IF event_start = 'stop'，IF locationID = locationID，以及 IF customerID = customerID。最后一步是删除所有 event_start“开始”行。

我已经尝试了 UPDATE SET WHERE 序列和一些 CASE，但我的问题是我无法全神贯注于如何在没有像 VBA 这样的循环的情况下执行此操作。以下是我最好的尝试:

UPDATE table
SET event_end = MIN(time)
  WHERE event_type = 'stop'
  WHERE customerid = customerid
  WHERE locationid = locationid
  WHERE time > time

SELECT * 
FROM table

我希望有一个表格，所有事件数据都在一行中，而不是分布在多行中。如果这是少数，我深表歉意，但在此先表示感谢。谢谢

Answer 1

问题陈述:

• 添加event_end作为现有行的额外属性，数据将根据 customer_id, location_id 填充.
• 我们将在 event_end 中填充数据所有事件类型为 begin 的事件
• 将从具有相同 customer_id, location_id 的行中选取数据但事件类型为 stop .
• 最后，我们将删除类型为 stop 的所有事件.

解决方案:假设您的表名为 customer_events并将使用 self join相同的概念。

• 首先，确定哪些记录需要更新。我们可以使用 SELECT查询以识别此类记录。

• c1表格将代表带有 begin 的行事件类型。

• c2表格将代表带有 stop 的行事件类型。

SELECT *
FROM customer_events c1
LEFT JOIN customer_events c2 ON c1.customerID = c2.customerID AND c1.locationID = c2.locationID AND c1.event_type = 'begin' AND c2.event_type = 'stop'
WHERE c1.event_type = 'begin';  -- As we want to populate data in events with value as `begin`

• 编写查询以更新记录。

UPDATE customer_events c1
LEFT JOIN customer_events c2 ON c1.customerID = c2.customerID AND c1.locationID = c2.locationID AND c1.event_type = 'begin' AND c2.event_type = 'stop'
SET c1.event_end = c2.`time`          
WHERE c1.event_type = 'begin';

• 现在每条事件类型为begin的记录在 event_end 中有任一值列，否则如果没有记录匹配为 stop，它将为空事件。

• 对于事件类型为 stop 的行，或者它们被映射到一些事件类型为 begin 的行或者有些没有映射。在这两种情况下，我们都不想保留它们。删除事件类型为 stop 的所有记录.

DELETE FROM customer_events
WHERE event_type = 'stop';

注意:不要运行DELETE声明，除非您确定此解决方案适合您。

更新:我们可以有多个记录 begin & stop单个客户和位置的事件。

Sample Input:
| pID | customerID* | *locationID* | *event_type* | *time* | *event_end* |
| 1   | 1           | a            | begin        | 02:45:00  |          |
| 2   | 2           | a            | begin        | 03:10:00  |          |
| 3   | 1           | b            | begin        | 04:30:00  |          |
| 4   | 2           | b            | begin        | 05:45:00  |          |
| 5   | 2           | a            | stop         | 06:49:59  |          |
| 6   | 1           | a            | begin        | 07:38:00  |          |
| 7   | 3           | b            | begin        | 08:57:19  |          |
| 8   | 2           | b            | stop         | 09:57:43  |          |
| 9   | 3           | b            | stop         | 10:58:03  |          |
| 10  | 4           | a            | begin        | 11:58:34  |          |
| 11  | 1           | a            | stop         | 12:09:36  |          |
| 12  | 1           | b            | stop         | 13:09:50  |          |
| 13  | 1           | a            | stop         | 14:10:02  |          |

查询:

SELECT * 
FROM (
    SELECT 
       ce.*,
       IF(@c_id <> ce.customerId OR @l_id <> ce.locationID, @rank:= 1, @rank:= @rank + 1 ) as rank, 
        @c_id:= ce.customerId, 
        @l_id:= ce.locationID
    FROM customer_events ce, 
    (SELECT @c_id:= 0 c, @l_id:= '' l, @rank:= 0 r) AS t 
    WHERE event_type = 'begin'
    ORDER BY customerId, locationID, `time`) AS c1
LEFT JOIN (
    SELECT 
       ce.*,
       IF(@c_id <> ce.customerId OR @l_id <> ce.locationID, @rank:= 1, @rank:= @rank + 1 ) as rank, 
        @c_id:= ce.customerId, 
        @l_id:= ce.locationID
    FROM customer_events ce, 
    (SELECT @c_id:= 0 c, @l_id:= '' l, @rank:= 0 r) AS t 
    WHERE event_type = 'stop'
    ORDER BY customerId, locationID, `time`
) AS c2 ON c1.customerID = c2.customerID AND c1.locationID = c2.locationID AND c1.rank = c2.rank;

Output:
| pId | customerID| locationId| event_type| Start_Time|End_Id|  End_Time |
| 1   | 1         | a         | begin     | 02:45:00  | 11   | 12:09:36  |
| 6   | 1         | a         | begin     | 07:38:00  | 13   | 14:10:02  |
| 3   | 1         | b         | begin     | 04:30:00  | 12   | 13:09:50  |
| 2   | 2         | a         | begin     | 03:10:00  | 5    | 06:49:59  |
| 4   | 2         | b         | begin     | 05:45:00  | 8    | 09:57:43  |
| 7   | 3         | b         | begin     | 08:57:19  | 9    | 10:58:03  |
| 10  | 4         | a         | begin     | 11:58:34  |      |           |

更新语句:创建两列 end_pID和 event_end用于迁移。

UPDATE customer_events 
INNER JOIN (
SELECT c1.pId, c2.pID End_Id, c2.time AS End_Time
FROM (
    SELECT 
       ce.*,
       IF(@c_id <> ce.customerId OR @l_id <> ce.locationID, @rank:= 1, @rank:= @rank + 1 ) as rank, 
        @c_id:= ce.customerId, 
        @l_id:= ce.locationID
    FROM customer_events ce, 
    (SELECT @c_id:= 0 c, @l_id:= '' l, @rank:= 0 r) AS t 
    WHERE event_type = 'begin'
    ORDER BY customerId, locationID, `time`) AS c1
LEFT JOIN (
    SELECT 
       ce.*,
       IF(@c_id <> ce.customerId OR @l_id <> ce.locationID, @rank:= 1, @rank:= @rank + 1 ) as rank, 
        @c_id:= ce.customerId, 
        @l_id:= ce.locationID
    FROM customer_events ce, 
    (SELECT @c_id:= 0 c, @l_id:= '' l, @rank:= 0 r) AS t 
    WHERE event_type = 'stop'
    ORDER BY customerId, locationID, `time`
) AS c2 ON c1.customerID = c2.customerID AND c1.locationID = c2.locationID AND c1.rank = c2.rank) AS tt ON customer_events.pID = tt.pId
SET customer_events.end_pID = t.End_Id, customer_events.event_end = t.End_Time;

最后，删除所有带有 event_type = 'stop' 的事件