# SQL查找和组连续数，无重复[英] SQL find and group consecutive number in rows without duplicate

### 本文来自：IT宝库（https://www.itbaoku.cn）

```Taxi  Client  Time
Tom   A       1
Tom   A       2
Tom   B       3
Tom   A       4
Tom   A       5
Tom   A       6
Tom   B       7
Tom   B       8
Bob   A       1
Bob   A       2
Bob   A       3
```

，预期的结果将是这样:

```Tom    3
Bob    1
```

```Tom    A     2
Tom    A     3
Tom    B     2
Bob    A     2
```

## 推荐答案

```select taxi, count(*)
from (select t.taxi, t.client, count(*) as num_times
from (select t.*,
row_number() over (partition by taxi order by time) as seqnum,
row_number() over (partition by taxi, client order by time) as seqnum_c
from t
) t
group by t.taxi, t.client, (seqnum - seqnum_c)
having count(*) >= 2
)
group by taxi;
```

## 其他推荐答案

```  select taxi ,count( distinct cient)
from table_name
group by taxi
```

## 其他推荐答案

```SELECT FEE.taxi,
SUM(FEE.clientNotSameAsPreviousInSequence)
FROM
(
SELECT taxi,
CASE
WHEN PreviousClient IS NULL THEN
1
WHEN PreviousClient <> client THEN
1
ELSE
0
END AS clientNotSameAsPreviousInSequence
FROM
(
SELECT *,
LAG(client) OVER (PARTITION BY taxi ORDER BY taxi, time) AS PreviousClient
FROM table
) taxisWithPreviousClient
) FEE
GROUP BY FEE.taxi;
```

### 问题描述

So I have a table like this:

```Taxi  Client  Time
Tom   A       1
Tom   A       2
Tom   B       3
Tom   A       4
Tom   A       5
Tom   A       6
Tom   B       7
Tom   B       8
Bob   A       1
Bob   A       2
Bob   A       3
```

and the expected result will be like this:

```Tom    3
Bob    1
```

I have used the partition function to count the consecutive value but the result become this:

```Tom    A     2
Tom    A     3
Tom    B     2
Bob    A     2
```

## 推荐答案

This is a variation of a gaps-and-islands problem. You can solve it using window functions:

```select taxi, count(*)
from (select t.taxi, t.client, count(*) as num_times
from (select t.*,
row_number() over (partition by taxi order by time) as seqnum,
row_number() over (partition by taxi, client order by time) as seqnum_c
from t
) t
group by t.taxi, t.client, (seqnum - seqnum_c)
having count(*) >= 2
)
group by taxi;
```

## 其他推荐答案

use distinct count

```  select taxi ,count( distinct cient)
from table_name
group by taxi
```

It seems your expected output is wrong

## 其他推荐答案

I don't see where you get the number 3 from. If you're trying to do what your question says and group by client in consecutive order only and then get the number of different groups, I can help you out with the following query. Bob has 1 group and Tom has 4.

Partition by taxi, ORDER BY taxi, time and check if this client matches the previous client for this taxi. If yes, do not count this row. If no, count this row, this is a new group.

```SELECT FEE.taxi,
SUM(FEE.clientNotSameAsPreviousInSequence)
FROM
(
SELECT taxi,
CASE
WHEN PreviousClient IS NULL THEN
1
WHEN PreviousClient <> client THEN
1
ELSE
0
END AS clientNotSameAsPreviousInSequence
FROM
(
SELECT *,
LAG(client) OVER (PARTITION BY taxi ORDER BY taxi, time) AS PreviousClient
FROM table
) taxisWithPreviousClient
) FEE
GROUP BY FEE.taxi;
```