22,207
社区成员
发帖
与我相关
我的任务
分享
--测试数据
if not object_id(N'Tempdb..#tableA') is null
drop table #tableA
Go
Create table #tableA([ID] int,[买家号] nvarchar(22),[日期] Date,[是否无效订单] nvarchar(21))
Insert #tableA
select 1,N'张三','2018-6-29',N'是' union all
select 2,N'李四','2018-6-29',N'是' union all
select 3,N'张三','2018-6-29',N'是' union all
select 4,N'王五','2018-6-29',N'是' union all
select 5,N'李六','2018-6-29',N'是'
Go
--测试数据结束
SELECT ROW_NUMBER() OVER (ORDER BY 买家号) AS id,
买家号,
日期,
SUM( CASE
WHEN 是否无效订单 = '是' THEN
1
ELSE
0
END
) 无效订单次数
FROM #tableA
GROUP BY 买家号,
日期;