过滤掉孤立表条目

Question

假设有一个只有两列的表（示例如下）。每个“1”条目后应跟一个“0”（按下面给出的排序顺序）。然而，正如您所看到的，在表中，有一些“孤儿”，其中有两个连续的“1”。

如何创建一个返回所有行（除了任何连续“1”中的第一个行）的查询？ （这会将下面的示例从 16 行减少到 14 行）

1 E
0 A
1 T
0 S
1 R
0 E
1 F
0 T
1 G
1 T
0 R
1 X
1 R
0 R
1 E
0 T

---

我将尝试澄清我的问题，我认为上面我过于简化了它。想象一个名为 logs 的表，有四列：

• user （包含用户名的字符串）
• machine （唯一标识各种 PC 的字符串）
• type（事件类型：1 表示 login，0 表示 logout）
• time（事件发生的时间正在记录的事件）

[机器/时间对提供了一个唯一的密钥，如任何机器都不能同时登录或退出两次。如果需要的话，大概可以根据机器/时间排序人为创建“ID”列。]

这个想法是每个登录事件都应该伴随一个注销事件。理想情况下，可以很容易地将登录与注销进行匹配，从而分析登录所花费的时间。

但是，在断电的情况下，不会记录注销。因此（仅考虑一台机器的数据，按时间排序）如果连续有两个登录事件，我们希望忽略第一个登录，因为我们没有从中获得任何可靠的数据。这就是我正在努力解决的问题。

Answer 1

前提是，

• 只有 1 是骗人的，绝不会是 0。
• 如果有更多 1，您希望删除所有前一个 1。

你的文字说“除了任何连续的第一个”，但我认为，这就是你想要的。或者只能有 2 个，那么它是相同的。

SELECT x.*
FROM   x
LEFT   JOIN x y on y.id = (x.id + 1)
WHERE  (x.nr = y.nr) IS NOT TRUE -- OR x.nr = 0
ORDER  BY x.id

如果您想保留双 0，请另外使用注释子句，但可能不需要。

问题编辑后编辑：

您可能需要向数据添加自动增量列以使其更简单：
生成（即写入）行号MySQL 中的索引列

其他 RDBMS（PostgreSQL、Oracle、SQL Server 等）具有诸如 row_number() 或lag() 和 lead() 使此类操作变得更加容易。

Answer 2

假设您获得一个 id（添加列，设置列 id = 数据库中的记录号）使用：

select a.*
  from the_table a
  left join the_table b on b.id = a.id + 1
                       and b.col1 = 0
 where a.col1 = 1
   and b.id is null

Answer 3

尝试：

select l.*
from logs l
where l.type = 0 or
      not (select type
           from (select * from logs order by `time` desc) n
           where n.machine = l.machine and
                 n.user = l.user and
                 n.time > l.time)
           group by () )

Answer 4

使用 CTE 将滞后逻辑与选择标准分开。

DROP TABLE tmp.bits;
CREATE TABLE tmp.bits
    ( id SERIAL NOT NULL
    , bit INTEGER NOT NULL
    , code CHAR(1)
    );
INSERT INTO tmp.bits(bit, code) VALUES
(1, 'T' )
, (0, 'S' )
, (1, 'R' )
, (0, 'E' )
, (1, 'F' )
, (0, 'T' )
, (1, 'G' )
, (1, 'T' )
, (0, 'R' )
, (1, 'X' )
, (1, 'R' )
, (0, 'R' )
, (1, 'E' )
, (0, 'T' )
    ;

SET search_path='tmp';
SELECT * FROM bits;

-- EXPLAIN ANALYZE
WITH prevnext AS (
SELECT
    bt.id AS thisid
    , bt.bit  AS thisbit
    , bt.code AS thiscode
    , bp.bit AS prevbit
    , bp.code AS prevcode
    FROM bits bt
    LEFT JOIN bits bp ON (bt.id > bp.id)
    AND NOT EXISTS ( SELECT * FROM bits nx
        WHERE nx.id > bp.id
        AND nx.id < bt.id
        )   
    )
SELECT thisid, thisbit, thiscode
FROM prevnext
WHERE thisbit=0
OR prevbit IS NULL OR thisbit <> prevbit
    ;

编辑：

对于那些无法使用 CTE 的较差的解决方案，很容易创建一个视图：

CREATE VIEW prevnext AS (
SELECT
    bt.id AS thisid
    , bt.bit  AS thisbit
    ,bt.code AS thiscode
    , bp.bit AS prevbit
    , bp.code AS prevcode
    FROM bits bt
    LEFT JOIN bits bp ON (bt.id > bp.id)
    AND NOT EXISTS ( SELECT * FROM bits nx
        WHERE nx.id > bp.id
        AND nx.id < bt.id
        )
    )
    ;
SELECT thisid, thisbit, thiscode
FROM prevnext
WHERE thisbit=0
OR prevbit IS NULL OR thisbit <> prevbit
    ;