表ExChange
字段:
OrderNo--单号
ProductId--产品编号
ClientCode--公司编号
UserId--兑换人ID
数据:
OrderNo ProductId ClientCode UserId
1 10 C114 P26
2 11 C114 P28
3 10 C114 P30
4 5 C114 P26
根据公司来分组,怎么查出一个公司下面的兑换次数和人数?? 求大神帮忙 急;
------解决思路----------------------
--不知道理解的对不对
select ClientCode, count(UserId) as 次数, count(distinct userid) as 人数 from exhcange 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
------解决思路----------------------
;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