时间戳

首页 > 解决方案 > 有没有办法根据另一列的值更新列值？

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

问题描述

我的 SQL 任务的第 1 部分涉及重组数据。我的任务要点如下：基于 event_type，如果它是“开始”，我试图使用那个“时间”来找到它的停止时间（在另一行）并将其添加到列（event_end）与开始时间在同一行，以便事件的所有数据很好地放在一行中。

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 'begin' 行。

我已经尝试过 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

我希望在一行中有一个包含所有事件数据的表，而不是分布在多行中。如果这是少数，我表示歉意，但提前表示感谢。谢谢

标签： mysql

解决方案

问题陈述：

  • 作为额外属性添加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';

  • 现在，每个事件类型为 as 的记录begin在列中都有一个值，或者如果没有记录匹配为事件event_end，它将为空。stop

  • 对于事件类型为 as 的行stop，它们要么映射到事件类型为 as 的某些行，要么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'

推荐阅读