使用MySQL查询具有不同列值的后续行

Question

我有一个类似这样的表格：

sn(char)   ts(int)     data(char) flag(tinyint)
'a'        1494825611  'a0'       1
'a'        1494825613  'a1'       0
'a'        1494825617  'a2'       0
'a'        1494825623  'a3'       1
'a'        1494825634  'a4'       1
'b'        1494825644  'b1'       1
'b'        1494825643  'b0'       0
'a'        1494825645  'a5'       1
'a'        1494825648  'a6'       0
'b'        1494825658  'b2'       1

行可能顺序不正确（例如b1和b0），因此它们应该首先按ts排序。

我正在尝试为sn制作高效的查询，以获取当前和下一个flag不同的行。

结果，对于sn 'a'，我想要像这样的东西：

old_ts     ts         old_data data flag
1494825611 1494825613 'a0'     'a1' 0
1494825617 1494825623 'a2'     'a3' 1
1494825645 1494825648 'a5'     'a6' 0

对于sn'b'：

old_ts     ts         old_data data flag
1494825643 1494825644 'b0',    'b1' 1

创建额外的列或表不是问题。

- Andrew Shulgin

---

最后两行的“ts”是相同的，这是打字错误吗？ - P.Salmon

是的，那是个打字错误。已经修正了，谢谢！ - Andrew Shulgin

Answer 1

您可以使用@rowFlag变量。每一行都检查它是否等于flag。如果是，则将过滤器字段设置为0以后跳过它。

select old_ts, ts, old_data, data, new_flag as flag
from
    (select
      t.ts,
      t.data,
      case when @rowFlag=flag then 0 else 1 end as filter,
      flag as new_flag,
      @rowFlag:=flag as old_flag,
      @old as old_data,
      @old:=data,
      @old_ts as old_ts,
      @old_ts:=ts
    from  test t, (select @rowFlag:=-1, @old:=null, @old_ts:=null) as def
    where sn='a'
    order by ts) as sub
where filter=1 and old_ts is not null;

SQL Fiddle

- StanislavL

---

谢谢，看起来是一个很好的解决方案，但这里似乎有一个错误：(@rowFlag:=-1, :old=null)。 - Andrew Shulgin

谢谢，但仍然有一个错误： 您的SQL语法有误; ... 在 ':old=null)' 附近使用 - Andrew Shulgin

还有一个问题 - 每个派生表都必须有自己的别名，我已经添加了 as def。但是最后一个问题是 - old_data 总是 null。 - Andrew Shulgin

这是一个打字错误。已提交修复。请批准修复，我会接受您的答案。谢谢！ - Andrew Shulgin

Answer 2

你可以检查这个，因为它包含旧值和新值。将new_table更改为你实际的表名。

select a.sn, a.ts as oldts, b.ts as newts, 
a.data as old_data, b.data as data, a.flag  as old_flag , b.flag as flag
from (
select sn, ts, data, flag , 
if(@oldSn = sn,@rowNumber := @rowNumber + 1,@rowNumber := 1) as row_number, 
@oldSn := sn as curentsn
from new_table
order by sn, ts ) a 
join  (
select sn, ts, data, flag , 
if(@oldSn1 = sn,@rowNumber := @rowNumber + 1,@rowNumber := 1) as row_number, 
@oldSn1 := sn as curentsn
from new_table
order by sn, ts ) b  on  a.sn = b.sn 
and a.row_number + 1 = b.row_number
where a.flag != b.flag

以上查询的输出

sn, oldts, newts, old_data, data, old_flag, flag
a, 1494825611, 1494825613, a0, a1, 1, 0
a, 1494825617, 1494825623, a2, a3, 0, 1
a, 1494825645, 1494825648, a5, a6, 1, 0
b, 1494825643, 1494825644, b0, b1, 0, 1

- Fahad Anjum