如何在选择查询(MySQL)中仅消除连续重复项而不消除所有重复项? [英] How to eliminate only continuous duplicates but not all duplicates in a select query (MySQL)?

问题描述

我有一个这样的表:

我要输出的是(按此顺序)通过消除连续重复项而不是所有重复项:
100
300
500
300
400
100

What I want to output is (in this order) by eliminating the continuous duplicates but not all duplicates:
100
300
500
300
400
100

我无法选择Distinct，因为它将消除300和100的第二个实例.在MySQL中有没有办法实现此结果? 谢谢！

I cannot select Distinct, as it will eliminate the second instances of 300, 100. Is there a way to achieve this result in MySQL? Thanks!

推荐答案

您要获取先前的值.如果日期确实没有间隔或重复，请执行以下操作:

You want to get the previous value. If the dates really have no gaps or duplicates, just do:

select t.*
from t left join
     t tprev
     on t.col1 = date_add(tprev.col1, interval 1 day)
where tprev.col2 is null or tprev.col2 <> t.col2;

如果日期不符合这些条件，则可以使用变量:

If the dates don't meet these conditions, then you can use variables:

select t.*
from (select t.*,
             (@rn := if(@v = col2, @rn + 1,
                        if(@v := col2, 1, 1)
                       )
             ) as rn
      from t cross join
           (select @v := 0, @rn := 0) params
      order by t.col1
     ) t
where rn = 1;

请注意，MySQL不保证SELECT中表达式的求值顺序.因此，不应在一个表达式中分配变量，而后在另一个表达式中使用变量-应该在单个表达式中分配变量.

Note that MySQL does not guarantee the order of evaluation of expressions in the SELECT. So variables should not be assigned in one expression and then used in another -- they should be assigned in a single expression.

这篇关于如何在选择查询(MySQL)中仅消除连续重复项而不消除所有重复项?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！