sql-server - 如果相同的名称出现在相邻行中，则用于显示计数的 SQL 查询应显示计数，否则为 1

我有一个包含 tb1 列的表 id,name ，

如果相同的名称出现在相邻的行中，则应显示计数，否则为 1

例如:

id                name

1                 sam

2                 jose

3                 sam

4                 sam

5                 dev

6                 jose

结果想成为

name                 counts

 sam                   1

 jose                  1

 sam                   2

 dev                   1

 jose                  1

请帮忙。

最佳答案

尝试与子查询“COUNT(*) OVER (PARTITION”和 row_number() 的组合:

--DROP TABLE #Test;
SELECT id = IDENTITY(INT,1,1), name INTO #Test FROM
(
    SELECT name = 'sam' UNION ALL
    SELECT 'jose' UNION ALL
    SELECT 'sam ' UNION ALL
    SELECT 'sam ' UNION ALL
    SELECT 'sam ' UNION ALL
    SELECT 'dev ' UNION ALL
    SELECT 'dev ' UNION ALL
    SELECT 'jose' UNION ALL
    SELECT 'sam ' UNION ALL
    SELECT 'sam ' UNION ALL
    SELECT 'jose'
) a;

GO

WITH GetEndID AS (
    SELECT *
    , EndID =(SELECT MIN(id) FROM #Test b WHERE b.name != a.name AND b.id > a.id)
    FROM #Test a
), GetCount AS
(
    SELECT
    *
    , NameCount = COUNT(*) OVER (PARTITION BY EndID)
    , OrderPrio = ROW_NUMBER() OVER (PARTITION BY EndID ORDER BY id)
    FROM GetEndID
)
SELECT id, name, NameCount FROM GetCount WHERE OrderPrio = 1 ORDER BY id;

关于sql-server - 如果相同的名称出现在相邻行中，则用于显示计数的 SQL 查询应显示计数，否则为 1，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/26667897/