在表格中检测模式

Question

有这样一个表：

Table: statuses
ID  | status
----|---------
777 | 1
675 | 1
651 | 0
611 | 1
600 | 0
554 | 1
443 | 0
323 | 0
222 | 1
112 | 1

如何选择只有两个（或更多）状态为0的行？（在样例中仅有443、323），并按系列中的第一个ID进行分组。

因此，输出将是：

ID  | status | group
----|--------|---------
443 | 0      | 443
323 | 0      | 443

- Sfisioza

---

根据您想使用的编程语言，可以通过编程方式完成吗？ - Subhan

SQL不是这个目的的合适工具。选择所有符合条件的行，然后在应用程序代码中进行过滤和分组。 - axiac

你的数据是如何排序的？如果没有其他字段指示顺序（因此您依赖插入顺序），则无法获得一致的结果。表是集合，行没有固有的顺序，除非指定。 - 1010

Answer 1

如果无法使用编程方法，您可以尝试此查询（但其性能将更差）。

 select
    s.id,
    0 as status,
    group_field.id as group
from 
    statuses s,
    (select id from statuses where status = 0 group by id having count(1) > 1 limit 1) group_field
where 
    s.id in(select id from statuses where id = 0 group by id having count(1) > 1)

- Carlos Meseguer

Answer 2

select 
id, STATUS,
@st_order:=if(@pre_st!=status,0,@st_order+1) as status_order,
@group_leader:=if(@pre_st!=status,id,@group_leader) as group_leader,
@pre_st:=status
from statuses,(select @st_order:=-1, @pre_st:=-1, @group_leader:=-1) val 

这个SQL语句会给你输出结果：

id,
状态,
连续相同状态的行数,
相同状态的组长,
无用的最后一列

根据你的输入，前四列的输出结果是：

ID|状态|状态行数|状态组长
777| 1 | 0 | 777
675| 1 | 1 | 777
651| 0 | 0 | 651
611| 1 | 0 | 611
600| 0 | 0 | 600
554| 1 | 0 | 554
443| 0 | 0 | 443
323| 0 | 1 | 443
222| 1 | 0 | 222
112| 1 | 1 | 222

因此，你可以在外部选择中进行任何想要的操作。

- amow

---

请参考此链接：https://dev59.com/eWQn5IYBdhLWcg3wmIAs#16715618。在查询中多次使用变量的顺序不能保证被评估。 - 1010

Answer 3

你可以使用子查询并进行简单的逻辑操作来实现这一点。

set @cnt=0;
set @id=-1;
SELECT 
    t3.id, t3.status, t3.dup_id AS `group`
FROM
    (SELECT 
        id,
            status,
            IF(status = 0, @cnt:=@cnt + 1, @cnt:=0) AS great,
            IF(@cnt = 1, @id:=id, IF(status = 0, @id, - 1)) AS dup_id
    FROM
        statuses) t3
WHERE
    t3.dup_id IN (SELECT 
            t1.dup_id
        FROM
            (SELECT 
                id,
                    status,
                    IF(status = 0, @cnt:=@cnt + 1, @cnt:=0) AS great,
                    IF(@cnt = 1, @id:=id, IF(status = 0, @id, - 1)) AS dup_id
            FROM
                statuses) t1
        GROUP BY t1.dup_id
        HAVING COUNT(t1.dup_id) > 1
            AND t1.dup_id != - 1);

结果将会如您所期望的那样。

id  | status | group
----|--------|---------
443 | 0      | 443
323 | 0      | 443

- mahesh

---

过于冗长和复杂，请再尝试以简单的方式进行。 - A_Sk