22,209
社区成员
发帖
与我相关
我的任务
分享
;WITH [ExChange](OrderNo,ProductId,ClientCode,UserId)AS(
SELECT 1,10,'C114','P26'
UNION ALL SELECT'2','11','C114','P28'
UNION ALL SELECT'3','10','C114','P30'
UNION ALL SELECT'4','5','C114','P26'
)
--以上模拟表
--以下执行
SELECT ClientCode
,COUNT(OrderNo)[兑换次数]
,COUNT(DISTINCT UserId)[人数]
FROM ExChange
GROUP BY ClientCode
/* 测试数据
WITH ExChange(OrderNo,ProductId,ClientCode,UserId) AS (
SELECT 1,10,'C114','P26' UNION ALL
SELECT 2,11,'C114','P28' UNION ALL
SELECT 3,10,'C114','P30' UNION ALL
SELECT 4,5,'C114','P26'
)*/
SELECT ClientCode,
COUNT(*) 兑换次数,
COUNT(DISTINCT UserID) 人数
FROM ExChange
GROUP BY ClientCode
ClientCode 兑换次数 人数
---------- ----------- -----------
C114 4 3