如何在SQL Server中根据客户分区的记录的列值和COUNT()添加标志列

Question

我的SQL Server表如下所示：

DayNo. Customer    AgentsInvolved  CallID   DesiredFlag
-------------------------------------------------------
0      AAA              1        1858          0 
0      AAA              3        1859          0
2      AAA              1        1860          0
0      BBB              2        1862          0
0      CCC              1        1863          1
0      DDD              3        1864          0
9      DDD              1        1865          0
9      DDD              4        1866          0

我该如何添加一个新列来表示；

WHEN (DayNo. = 0, and AgentsInvolved = 1) AND COUNT(callID) = 1 (grouped by customer)
THEN 1 
ELSE 0

我理解的列值可以封装到case语句中，但不确定如何为COUNT(CallID)添加条件。请参阅DesiredFlag列，查看我尝试实现的结果。

任何帮助都会很棒。

Answer 1

我会使用NOT EXISTS:

SELECT t.*,
(CASE WHEN NOT EXISTS (SELECT 1 
FROM table t1 
WHERE t1.Customer = t.Customer AND t1.CallID <> t.CallID
) AND t.DayNo = 0 AND t.AgentsInvolved = 1
THEN 1 
ELSE 0
END) AS DesiredFlag
FROM table t;

Answer 2

带样本数据：

declare @T Table (DayNo int,Customer varchar(10),AgentsInvolved int,CallID int)
insert into @T values
(0,'AAA',1,1858),
(0,'AAA',3,1859),
(2,'AAA',1,1860),
(0,'BBB',2,1862),
(0,'CCC',1,1863),
(0,'DDD',3,1864),
(9,'DDD',1,1865),
(9,'DDD',4,1866)

你可以通过获得你的结果(正如我正确理解的那样(

;with summation as
(
select Customer, count(1) as qty
from @T
group by Customer
)
select
T.*,
coalesce(F.flag, 0) as flag
from
@T T
inner join summation s on s.Customer = T.Customer
outer apply (select 1 as flag where DayNo = 0 and AgentsInvolved = 1 and qty = 1) F

结果：

DayNo       Customer   AgentsInvolved CallID      flag
----------- ---------- -------------- ----------- -----------
0           AAA        1              1858        0
0           AAA        3              1859        0
2           AAA        1              1860        0
0           BBB        2              1862        0
0           CCC        1              1863        1
0           DDD        3              1864        0
9           DDD        1              1865        0
9           DDD        4              1866        0