如果值为空，则合并行

Question

我正在使用 SQL-Server 2008。我需要合并同一Name的行，并在以下情况下增加计数器：

1. 一个或多个相同Name的Id为空
2. 如果Id为NULL，则不要合并行！
3. 如果具有相同的Name但不同的Ids，则不要合并行

目前的输出：

Name    Id   Cnt
John    1     1
Peter   2     2 -- This Peter with the same Id have 2 entries so Cnt = 2
Peter   3     1 -- This is other Peter with 1 entry so Cnt = 1
Lisa    4     1
Lisa   NULL   1
David   5     1
David         1 -- here Id is blank ''
Ralph         2 -- Ralph have both rows with blank Id so Cnt = 2

期望的输出：

Name    Id   Cnt
John    1     1
Peter   2     2
Peter   3     1
Lisa    4     1
Lisa   NULL   1 -- null still here
David   5     2 -- merged with blank '' so Cnt = 2
Ralph         2 -- merged both blanks '' so Cnt = 2

SQL查询:

这是我现在使用的样本查询:

SELECT Name, 
       Id, 
       COUNT(Id) AS Cnt
FROM Employees                       
WHERE Condition = 1                 
GROUP BY Name, Id

我尝试了以下方法： 在SELECT子句中添加聚合函数MAX到Id，并仅按Name分组，但在这种情况下，合并了具有不同Id但名称相同且带有NULL值的行，这对我来说是错误的。

SELECT Name, 
       MAX(Id), -- added aggregate
       COUNT(Id) AS Cnt
FROM Employees                       
WHERE Condition = 1                 
GROUP BY Name -- grouped by Name only

你有什么想法吗？如果问题不清楚，请问我，我会提供更多细节。

更新：

DDL

CREATE TABLE Employees
(
  Name NVARCHAR(40),
  Id NVARCHAR(40)
);

DML

INSERT INTO Employees VALUES
 ('John' ,   '1')
,('Peter',   '2')
,('Peter',   '2')
,('Peter',   '3')
,('Lisa' ,   '4')
,('Lisa' ,  NULL)
,('David',   '5')
,('David',   '')
,('Ralph',   '')
,('Ralph',   '')

示例: SQL FIDDLE

- Stanislovas Kalašnikovas

---

+1 一个格式良好的问题。但最好包括相关表的DDL + DML以获取示例数据，这样任何想回答的人都可以轻松地复现它。 - Zohar Peled

您的表格数据设计有些奇怪... 您考虑修改数据吗？ - Anton

@Anton，这里的数据非常简化，没有任何改变的可能性。 - Stanislovas Kalašnikovas

当 Peter, 2, 2; Peter, 3, 1; Peter, （空格）, 1 合并时，您该如何操作？ - Eric

例如：更新员工表并将ID设置为空白的名称设置为非空白。在这种情况下，您的任务将变得更加容易。 - Anton

@Eric在这个案例中的结果应该是：Peter，2，3；Peter，3，2；对于两者都增加计数器并且应该去掉带有空值的行。 - Stanislovas Kalašnikovas

Answer 1

您可以在 SELECT 语句中使用 CASE 语句。它允许您为员工设置 Id = [某个值]，如果它为空。查询可能类似于这样：

SELECT E.Name, 
             CASE 
                  WHEN E.Id = ''
                     THEN 
                        (Select Employees.Id from Employees where Employees.Id <> '' and E.Name = Employees.Name)
                  ELSE E.Id 
             END as Idx, 
       COUNT(Id) AS Cnt
FROM Employees as E                     
WHERE Condition = 1                 
GROUP BY Name, Idx

- Anton

Answer 2

编辑

DECLARE @Data table (Name varchar(10), Id varchar(10)) -- Id must be varchar for blank value
INSERT @Data VALUES
('John', '1'),
('Peter', '2'),('Peter', '2'), 
('Peter', '3'),--('Peter', ''), --For test
('Lisa', '4'),
('Lisa', NULL),
('David', '5'),
('David', ''),
('Ralph', ''), ('Ralph', '')

---

SELECT 
    Name, 
    Id, 
    COUNT(*) + ISNULL(
        (SELECT COUNT(*) FROM @data WHERE Name = d.Name AND Id = '' AND d.Id <> '')
    , 0) AS Cnt 
FROM @data d 
WHERE 
    Id IS NULL 
    OR Id <> '' 
    OR NOT EXISTS(SELECT * FROM @data WHERE Name = d.Name AND Id <> '')
GROUP BY Name, Id

- Eric

Answer 3

一份带有窗口函数的版本：

SELECT Name,ID, Cnt from
( select *, sum(1-AmtBlank) over (partition by Name, ID) + sum(case id when 0 then 1 else 0 end) over (partition by Name) Cnt,
       rank()  over (partition by Name order by AmtBlank  ) rnk,
       row_number() over (partition by Name, ID order by AmtBlank) rnr
    FROM (select * , case id when '' then 1 else 0 end AmtBlank from Employees /*WHERE Condition = 1*/ ) e
) c where  rnr=1 and rnk = 1

这里使用 case id when '' then 1 else 0 end AmtBlank 来保留每行空白金额（使非空白金额为1-AmtBlank），并使用2个窗口函数，一个用于按名称和ID计数的ID (sum(1-AmtBlank) over (partition by Name, ID)) 和一个用于名称部分中所有空白的计数 (sum(case id when 0 then 1 else 0 end) over (partition by Name))。 row_number 用于随后仅获取组的第一行，而 rank 则用于仅在没有带有ID的记录时包括空白记录。

- Me.Name

Answer 4

尝试使用cte和join。

;with cte as (
SELECT Name, 
       Id, 
       COUNT(*) AS Cnt
FROM Employees                       
WHERE isnull(Id,1)<>''               
GROUP BY Name, Id
),

cte2 as (SELECT Name,id, COUNT(*) AS Cnt    FROM Employees WHERE Id='' GROUP BY Name,id)

select cte.Name,cte.Id,(cte.cnt + ISNULL(cte2.Cnt,0)) as cnt
from cte 
left JOIN  cte2
on cte.Name = cte2.Name 
union all 
select cte2.Name,cte2.Id,cte2.cnt
from cte2 
left JOIN  cte
on cte.Name = cte2.Name 
where cte.Name is null

- Jasqlg

Answer 5

这种简单的语法与旧版本或其他RDBMS兼容
-- 在注释中进行了自我解释
编辑：

select name, id, count(*) from (
    -- adds "normal" records
    select name, id from Employees where id is null or id <> ''
    -- adds one record to each name-notBlankId for each blank id  (David, Peter if you add 'Peter','')
    -- uncomment /*or id is null*/ if you want even null ids to recieve merged blanks
    union all
    select e1.name, e1.id
    from (select distinct name, id from Employees where id <> '' /*or id is null*/ ) as e1 
    inner join (select name, id from Employees where id = '') as e2 on e1.name = e2.name
    -- adds records that can't be merged (Ralph)
    union all
    select name, id from Employees e1
    where e1.id = ''
      and not exists(select * from Employees e2 where e1.name = e2.name and e2.id <> '')
) as fullrecords
group by name, id

- Saic Siquot

Answer 6

你可以尝试这样做。

;WITH NonBlanks AS
(
  SELECT Name, 
         Id, 
         COUNT(ISNULL(Id, 1)) AS Cnt
  FROM Employees
  WHERE ISNULL(Id,0) <> ''
  GROUP BY Name, Id
 )
,Blanks AS
(
  SELECT Name, 
         Id, 
         COUNT(ISNULL(Id, 1)) AS Cnt
  FROM Employees
  WHERE ID = ''
  GROUP BY Name, Id
)
SELECT CASE WHEN nb.NAME IS NULL THEN b.NAME ELSE nb.NAME END NAME,
       CASE WHEN nb.NAME IS NULL THEN b.Id ELSE nb.Id END Id,
       (ISNULL(nb.Cnt,0) + ISNULL(b.Cnt,0)) Cnt
FROM NonBlanks nb FULL JOIN  Blanks b 
     ON nb.Name = b.Name

- TLaV