代码之家 › 专栏 › 技术社区 › Naji

MySQL只显示对列的唯一更改

sql mysql

-1

Naji · 技术社区 · 6 年前

不确定这是不是一个简单的查询,但由于某种原因,无法理解…

我按降序(从最旧到最新)记录状态更改:

  Name       Status               Time
   A          Up        2018-06-21 00:07:00.000000
   A          Up        2018-05-21 00:07:00.000000
   A         Down       2018-04-21 00:07:00.000000
   A         Down       2018-03-21 00:07:00.000000
   A          Up        2018-02-21 00:07:00.000000
   A         Down       2018-01-21 00:07:00.000000

但我只想回来 独特的 改变到 Status 列,从而给出状态更改的时间列表。所以答案是这样的:

  Name       Status               Time
   A          Up        2018-05-21 00:07:00.000000
   A         Down       2018-03-21 00:07:00.000000
   A          Up        2018-02-21 00:07:00.000000
   A         Down       2018-01-21 00:07:00.000000

请注意,结果如何从3月和5月(而不是4月和6月)返回条目,因为这是状态从不同状态更改的时间。

我的查询当前正在进行一些联接,因此我也不确定如何/在何处将其与查询匹配:

SELECT
      Instrument.Name AS Name,
      Status.Name AS Status,
      Operation.Time AS Time,
FROM Operation
      INNER JOIN Instrument ON Instrument.Id = Operation.InstrumentId
      INNER JOIN Status ON Status.Id = Operation.StatusId
WHERE Instrument.Name = ?;

3 回复 | 直到 6 年前

Gordon Linoff 6 年前

理想情况下,你想要 lag() 从中选择。假设您是MySQL的V8之前版本。

一种方法是相关子查询:

select t.*
from (select t.*,
             (select t2.status
              from t t2
              where t2.time < t.time
              order by t2.time desc
              limit 1
             ) as prev_status
      from t
     ) t
where prev_status is null or prev_status <> status;

在较新版本的MySQL中,您只需执行以下操作:

select t.*
from (select t.*,
             lag(status) over (order by time) as prev_status
      from t
     ) t
where prev_status is null or prev_status <> status;

Himanshu 6 年前

我做了一些常规的尝试,让外部查询逐行运行,通过匹配子查询结果 groups 之所以是输出,是因为行的排序方式不同。可以帮你检查一下这个是否有效。

      SELECT
      Instrument.Name AS Name,
      DISTINCT(Status.Name) AS Status,
      Operation.Time AS Time,
FROM Operation
      INNER JOIN Instrument ON Instrument.Id = Operation.InstrumentId
      INNER JOIN Status ON Status.Id = Operation.StatusId
WHERE ( Name,
      Status,
      Time ) 
      IN
   (SELECT
      Instrument.Name AS Name1,
      Status.Name AS Status1,
      Operation.Time AS Time1,
FROM Operation
      INNER JOIN Instrument ON Instrument.Id = Operation.InstrumentId
      INNER JOIN Status ON Status.Id = Operation.StatusId
     group by NAME1, Status1 having Time1=min(Time1)
   )

Booboo 6 年前

另一种方法使用伪临时表(不能使用实际的临时表,因为MySQL不允许对一个表发出多个子查询),该表是原始表,其中附加了“行号”作为主键:

SET @row_number = 0;
create table temp as
  SELECT (@row_number:=@row_number + 1) AS row_num, t.* FROM t order by time;
ALTER TABLE temp ADD PRIMARY KEY (row_num);

然后查询变得简单:

select name, status, time from temp temp1
    where temp1.row_num = 1
    or temp1.status <>
       (select temp2.status from temp temp2 where temp2.row_num = temp1.row_num - 1);

drop table temp; /* no longer need this table */