如何通过另一列分组来计数不同的列值

I have the following problem - I have a table with 4 columns: Id, Name, InsertTime, UpdateTime. I want to count for each Name (NOT unique!) how many with the same name have different InsertTime or UpdateTime. For example, if I have the following data:

我有以下问题——我有一个包含4列的表:Id、名称、InsertTime、UpdateTime。我想计算每个名称(不是唯一的!)有多少名称相同的名称具有不同的InsertTime或UpdateTime。例如，如果我有以下数据:

ID NAME  INSERTED_TIME  UPDATE_TIME
1  maya  21-12-2015     21-12-2015
2  tal   22-12-2015     21-12-2015
3  maya  21-09-2015     21-12-2015
4  mark  21-12-2015     21-12-2015
5  mark  21-12-2015     21-12-2015

I want the outcome to be:

我希望结果是:

NAME  COUNT
maya  1
tal   0
mark  0

Explantion: "maya" has 1 duplicate with different InsertTime or UpdateTime. "tal" has no duplicates. And "mark" has a duplicate but both the InsertTime and UpdateTime are the same so it doesn't count.

解释:“maya”有1个副本，具有不同的InsertTime或UpdateTime。“tal”没有重复。而“mark”有一个副本，但是InsertTime和UpdateTime都是一样的，所以不算。

I have tried:

我有尝试:

SELECT COUNT(*)
FROM table t1
JOIN table t2 on t1.NAME=t2.NAME
where (t1.INSERTED_TIME<>t2.INSERTED_TIME or t1.UPDATE_TIME<>t2.UPDATE_TIME)

But this returns duplicates. I also tried different approaches with GROUP BY - nothing has worked so far.

但这返回副本。我也尝试了不同的方法，没有任何工作到目前为止。

Any help would be appreciated.

如有任何帮助，我们将不胜感激。

3 个解决方案

#1

Use GROUP BY and count the rows for each name:

使用GROUP BY并计算每个名称的行数:

;WITH CTE as
(
  SELECT NAME
  FROM yourtable
  GROUP BY NAME, INSERTED_TIME, UPDATE_TIME
)
SELECT NAME, count(*) - 1 COUNT
FROM CTE
GROUP BY NAME

Result:

结果:

NAME    COUNT
mark    0
maya    1
tal     0

#2

You can use windowed functions:

你可以使用窗口功能:

WITH cte AS
(
  SELECT NAME,
         RN = RANK() OVER (PARTITION BY NAME ORDER BY INSERTED_TIME,UPDATE_TIME) - 1
  FROM #table t1
)
SELECT NAME, [COUNT] = MAX(RN)
FROM cte
GROUP BY NAME
ORDER BY [COUNT] DESC

LiveDemo

LiveDemo

#3

You can use COUNT(DISTINCT to get the number of different values for INSERTED_TIME and UPDATE_TIME for each name, then simply deduct 1 from this number to get the output you require.

您可以使用COUNT(不同的方法来获得每个名称的INSERTED_TIME和UPDATE_TIME的不同值的数量，然后从这个数字中扣除1，以获得所需的输出。

DECLARE @T TABLE (ID INT, Name VARCHAR(50), INSERTED_TIME DATE, UPDATE_TIME DATE);
INSERT @T 
VALUES
    (1, 'maya', '2015-12-21', '2015-12-21'),
    (2, 'tal', '2015-12-22', '2015-12-21'),
    (3, 'maya', '2015-09-21', '2015-12-21'),
    (4, 'mark', '2015-12-21', '2015-12-21'),
    (5, 'mark', '2015-12-21', '2015-12-21');

SELECT  name, 
        [COUNT] = COUNT(DISTINCT CONCAT(INSERTED_TIME, UPDATE_TIME)) - 1
FROM    @T
GROUP BY Name
ORDER BY [COUNT] DESC;

#1