查询以获取列中具有两个连续零的行 [英] Query to get rows with two consecutive zeros in a column

问题描述

我有一个数据集:

sid  name     sub     marks  subid
11    kittu   eng     55     1
11    kittu   math    0      2
11    kittu   sci     0      3
12   bunnu    eng    0        1
12   bannu    math    44      2
12    bannu   sci     0       3
13    siva    eng     0       1
13    siva    math    0        2
13    siva   sci      88       3

我想得到这样的输出(有连续的零标记):

and I want to get output like this (which have consecutive zero marks):

11 kittu 
13  siva

推荐答案

SELECT a row from T WHERE存在另一个具有相同 sid 和较低 subid 的行，并且两行都有零标记.然后做一个 DISTINCT 以防万一有超过限定条件这样你就不会得到重复.

SELECT a row from T WHERE exists a row another with same sid and a lower subid sequentially and both rows have zero marks. Then Do a DISTINCT just in case there are more than qualifying condition so you don't get duplicates.

SELECT DISTINCT sid,name
FROM t
WHERE EXISTS (SELECT 1 FROM T T2 
              WHERE T2.marks = 0 
              AND t.marks = 0
              AND T2.sid = T.sid
              AND T2.subid = t.subid + 1)

sqlfiddle

这篇关于查询以获取列中具有两个连续零的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！