尝试使用SQL Server 2012在一条select语句中查看这是否可行。
表如下:
订单
Order_Index Audit_ID
--------------------
101 CB
102 MM
103 CN
Audit_ID
、CB和CN是同一个人,应该从结果中排除MM,所以我尝试显示如下内容:
SELECT
CASE
WHEN Audit_Id IN ('CB’, ’CN’)
THEN 'Cindy’
ELSE Audit_Id
END AS Audit_Id,
COUNT(DISTINCT Order_Index) AS ‘Total Orders’
FROM
Orders
WHERE
Audit_Id IN ('CB', 'CN’)
GROUP BY
Audit_Id
结果:
Audit_Id Total Orders
-------------------------
Cindy 1
Cindy 1
期望的结果:
Audit_Id Total Orders
-------------------------
Cindy 2
可以在单个查询中得到结果吗?
谢谢,
布赖恩
发布于 2018-10-15 06:56:03
由于您的所有结果显然都在同一组中,因此这里实际上没有必要使用整个group by
子句:
select 'Cindy' as "Audit ID", count(*) as "Total Orders"
from Orders
where Audit_Id in ('CB','CN');
现在,如果你真正想要实现的是某种“自定义”分组,那么你很可能是在追求更像下面这样的东西:
select
case when Audit_ID in ('A', 'B') then 'Group1' ... else Audit_ID end as "Audit ID",
count(*) as "Total Orders"
from Orders
group by case when Audit_ID in ('A', 'B') then 'Group1' ... else Audit_ID end;
您必须在group by
和select
列表之间复制表达式,除非您只是将表达式嵌套在另一个引用中,如派生表或CTE。
https://stackoverflow.com/questions/52807670
复制相似问题