时间戳

首页 > 解决方案 > TSQL：按一列分组，计算所有行并根据row_number保留第二列的值

sql-server - TSQL：按一列分组，计算所有行并根据row_number保留第二列的值

问题描述

我有一个基于某些规则返回 Id、Name 和 Row_Number() 的查询。

查询看起来像这样

SELECT 
    tm.id AS Id,
    pn.Name AS Name,
    ROW_NUMBER() OVER(PARTITION BY tm.id ORDER BY tm.CreatedDate ASC) AS Row
FROM 
    #tempTable AS tm
LEFT JOIN 
    names pn WITH (NOLOCK) ON tm.nameId = pn.NameId
WHERE ....

上述查询的输出如下表所示，其中包含虚拟数据

CREATE TABLE people
(
    id int,
    name varchar(55),
    row int
);

INSERT INTO people 
VALUES (1, 'John', 1), (1, 'John', 2), (2, 'Mary', 1),
       (3, 'Jeff', 1), (4, 'Bill', 1), (4, 'Bill', 2),
       (4, 'Bill', 3), (4, 'Billy', 4), (5, 'Bobby', 1),
       (5, 'Bob', 2), (5, 'Bob' , 3), (5, 'Bob' , 4);

我尝试做的是按id字段分组，计算所有行，但对于名称，使用与row = 1

我的尝试是这样的，但显然，我得到了不同的行，因为我x.name在 group by 中包含了。

SELECT 
    x.id,
    x.name,
    COUNT(*) AS Value
FROM
    (SELECT 
         tm.id AS Id,
         pn.Name  AS Name,
         ROW_NUMBER() OVER(PARTITION BY tm.id ORDER BY tm.CreatedDate ASC) AS Row
     FROM 
         #tempTable AS tm
     LEFT JOIN 
         names pn WITH (NOLOCK) ON tm.nameId = pn.NameId
     WHERE ....
) x
GROUP BY 
    x.id, x.name
ORDER BY 
    COUNT(*) DESC

虚拟数据的预期结果是：

id    name   count
------------------
 1    John     2
 2    Mary     1
 3    Jeff     1
 4    Bill     4
 5    Bobby    4

标签： sql-servertsqlsql-server-2008

解决方案

您可以使用FIRST_VALUE()窗口函数来获取行号 = 1 的行的名称，并且使用关键字 DISTINCT 不需要GROUP BY：

SELECT DISTINCT tm.id AS Id
     , FIRST_VALUE(pn.Name) OVER (PARTITION BY tm.id ORDER BY tm.CreatedDate ASC) AS Name
     , COUNT(*) OVER (PARTITION BY tm.id) AS counter
FROM #tempTable AS tm
LEFT JOIN names pn WITH (NOLOCK) ON tm.nameId = pn.NameId
WHERE ....

如果你不能使用FIRST_VALUE()，那么你可以使用条件聚合来做到这一点：

SELECT id,
       MAX(CASE WHEN Row = 1 THEN Name END) AS NAME,
       COUNT(*) AS Counter
FROM (
  SELECT tm.id AS Id
       , pn.Name  AS Name
       , ROW_NUMBER() OVER(PARTITION BY tm.id ORDER BY tm.CreatedDate ASC) AS Row
  FROM #tempTable AS tm
  LEFT JOIN names pn WITH (NOLOCK) ON tm.nameId = pn.NameId
  WHERE ....
) t
GROUP BY id

推荐阅读