TSQL 不同计数 [英] TSQL Distinct Counts

问题描述

我有一张看起来像这样的表格:

I have a table that looks like this:

ID SuppressionTypeID PersonID
------------------------------
1  1                 123
2  1                 456
3  2                 456

我想要滚动计数(不同的人)而不是按计数的正常组.

I want to get a rolling count (distinct people) rather than a normal group by count.

例如不是这个:

SuppressionTypeID    Count
---------------------------
1                    2
2                    1

这个:

SuppressionTypeID    RecordsLost
----------------------------------
1                    2
2                    0

后者为零，因为我们在 suppresiontypeid 1 上丢失了 456 个人.

The latter being zero as we lost person 456 on suppresiontypeid 1.

提前致谢.

推荐答案

您可能需要使用如下所示的临时表或表变量

You may need to use a temporary table or a table variable as shown below

     DECLARE @t TABLE (
    ID INT
    ,SuppressionTypeID INT
    ,PersonID INT
    )

INSERT INTO @t
SELECT 1
    ,1
    ,123

UNION ALL

SELECT 2
    ,1
    ,456

UNION ALL

SELECT 3
    ,2
    ,456

DECLARE @t1 TABLE (
    ID INT
    ,SuppressionTypeID INT
    ,PersonID INT
    ,firstid INT
    )

INSERT INTO @t1
SELECT *
    ,NULL
FROM @t

UPDATE t1
SET t1.firstid = t2.firstid
FROM @t1 AS t1
INNER JOIN (
    SELECT personid
        ,min(SuppressionTypeID) AS firstid
    FROM @t1
    GROUP BY personid
    ) AS t2 ON t1.PersonID = t2.PersonID

SELECT coalesce(t2.firstid, t1.SuppressionTypeID) AS SuppressionTypeID
    ,count(DISTINCT t2.personid) AS count
FROM @t1 AS t1
LEFT JOIN @t1 AS t2 ON t1.personid = t2.personid
    AND t1.SuppressionTypeID = t2.firstid
GROUP BY coalesce(t2.firstid, t1.SuppressionTypeID)

结果是

SuppressionTypeID count
----------------- -----------
1                 2
2                 0

这篇关于TSQL 不同计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！