根据行顺序获取计数 [英] Get a count based on the row order

问题描述

我有一张这种结构的表

Create Table Example (
[order] INT,
[typeID] INT
)

使用这些数据:

order|type
1   7
2   11
3   11
4   18
5   5
6   19
7   5
8   5
9   3
10  11
11  11
12  3

我需要根据订单获取每种类型的数量，例如:

I need to get the count of each type based on the order, something like:

type|count
7      1
11     **2**
18     1
5      1
19     1
5      **2**
3      1
11     **2**
3      1

背景

假设这张表是关于房子的，所以我有一个按顺序列出的房子.所以我有

Lets say that this table is about houses, so I have a list houses in an order. So I have

• 订单 1:红房子
• 2:白宫
• 3:白宫
• 4:红房子
• 5:蓝色的房子
• 6:蓝色的房子
• 7:白宫

所以我需要显示浓缩的信息.我要说:

So I need to show that info condensed. I need to say:

• 我有 1 个红房子
• 然后我有两座白色的房子
• 然后我有 1 个红房子
• 然后我有两个蓝房子
• 然后我有 1 个白宫

所以计数是基于顺序的.DENSE_RANK 函数将如果我能够在分区更改时重置 RANK，请帮助我.

So the count is based on the order. The DENSE_RANK function would help me if I were able to reset the RANK when the partition changes.

推荐答案

此解决方案使用递归 CTE，并依赖于无间隙的 order 值.如果你没有这个，你可以用 ROW_NUMBER() on the fly 创建它:

This solution is using a recursive CTE and is relying on a gapless order value. If you don't have this, you can create it with ROW_NUMBER() on the fly:

DECLARE @mockup TABLE([order] INT,[type] INT);
INSERT INTO @mockup VALUES
 (1,7)
,(2,11)
,(3,11)
,(4,18)
,(5,5)
,(6,19)
,(7,5)
,(8,5)
,(9,3)
,(10,11)
,(11,11)
,(12,3);

WITH recCTE AS
(
    SELECT m.[order]
          ,m.[type] 
          ,1 AS IncCounter
          ,1 AS [Rank]
    FROM @mockup AS m
    WHERE m.[order]=1

    UNION ALL

    SELECT m.[order]
          ,m.[type]
          ,CASE WHEN m.[type]=r.[type] THEN r.IncCounter+1 ELSE 1 END
          ,CASE WHEN m.[type]<>r.[type] THEN r.[Rank]+1 ELSE r.[Rank] END
    FROM @mockup AS m
    INNER JOIN recCTE AS r ON m.[order]=r.[order]+1
)
SELECT recCTE.[type]
      ,MAX(recCTE.[IncCounter])
      ,recCTE.[Rank]
FROM recCTE
GROUP BY recCTE.[type], recCTE.[Rank];

如果类型不变，则递归向下遍历，增加计数器，如果类型不同，则增加等级.

The recursion is traversing down the line increasing the counter if the type is unchanged and increasing the rank if the type is different.

剩下的就是一个简单的GROUP BY

这篇关于根据行顺序获取计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！