mysql - 如何获取连续记录在SQL中具有相同的值

假设我有一个如下的表，我如何才能得到所有记录都有相同的帐户id，按开始日期排序，并且连续的记录都有相同的值

begin_date       value    account_id   other
20180701    1013        222         11
20180702    1013        222         11
20180701    1022        222         12
20180702    1022        222         12
20180703    1023        222         12
20180701    1024        111         13
20180702    1024        111         13

在这种情况下，应返回以下内容：

begin_date       value    account_id   other
20180701    1013        222         11
20180702    1013        222         11
20180701    1022        222         12
20180702    1022        222         12
20180701    1024        111         13
20180702    1024        111         13

我试过用关键词“WITH”，但看起来像it is not valid anymore，这是我写的：

WITH Ordered AS (
            SELECT *,  ROW_NUMBER() OVER (ORDER BY account_id, begin_date, value) AS order_id
            FROM addresses)
SELECT o1.account_id, o1.value
FROM Ordered o1
    INNER JOIN Ordered o2 ON o2.account_id == o1.account_id AND o2.value = o1.value AND s2.order_id = s1.order_id + 1
UNION
SELECT o2.account_id, o2.value
FROM Ordered o1
    INNER JOIN Ordered o2 ON o2.account_id == o1.account_id AND o2.value = o1.value AND s2.order_id = s1.order_id + 1
order by
        1,2;

最佳答案

这个应该对你有用

select
    x.*
from
    <table_name> as x
    join (  select
                value,
                account_id,
                other
            from
                <table_name>
            group by
                value,
                account_id,
                other
            having
                count(*) > 1) as y on   x.value = y.value
                                        and x.account_id = y.account_id
                                        and x.other = y.other