GROUP BY带有嵌套的case表达式-有更好的方法吗? [英] GROUP BY with nested case expression - is there better way?

问题描述

SQL Server 2012

SQL server 2012

我要收取已收取的费用，并根据客户已成为客户的时间将其乘以不同的因素. group by子句相当简单.但是，当我想以不同方式使用此条件时，我的select会很尴尬:

I am taking fees received and multiplying them by different factors based on how long the Client has been a Client. The group by clause is fairly straight forward. However, my select gets awkward when I want to use this criteria in different ways:

select mp.professionals
    ,case when sl.stmndate < dateadd(year, 3, m.qClientOpenDate) then 'New' else 'Old' end age -- straight forward
    ,case (case when sl.stmndate < dateadd(year, 3, m.qClientOpenDate) then 'New' else 'Old' end) -- nested case
        when 'New' then sum(fees) * 0.5
        when 'Old' then sum(fees) * 0.25
        else 0
    end Credit
    ,case (case when sl.stmndate < dateadd(year, 3, m.qClientOpenDate) then 'New' else 'Old' end) -- nested case
        when 'New' then 'Welcome!'
        when 'Old' then 'Thank you for being a long-time Client!'
    end Greeting
from mattersprofessionals mp
inner join matters m on m.matters = mp.matters
inner join stmnledger sl on sl.matters = mp.matters
group by mp.professionals, case when sl.stmndate < dateadd(year, 3, m.qClientOpenDate) then 'New' else 'Old' end

我想我应该提到这是我的实际case语句的简化版本.

I suppose I should mention this is a simplified version of my actual case statements.

我希望有一种方法可以将sl.stmndate < dateadd(year, 3, m.qClientOpenDate)分组为布尔值或其他值，这样我就不必执行嵌套的case表达式.

I was hoping there was a way to group by sl.stmndate < dateadd(year, 3, m.qClientOpenDate) as a boolean or something so I don't have to do the nested case expressions.

我知道我可以对基本情况进行子查询，然后在外部查询中进行更多的情况表达式.但这只是重新排列了相同的嵌套案例概念.

I know I could do a sub-query on the basic case and then do more case expressions in the outer query. But that's just rearranging the same nested case concept.

推荐答案

从技术上讲，我认为我的问题的答案是否".没有一种方法可以对case语句的布尔部分进行分组，因此可以在select中以多种方式使用它.

Technically I think the answer to my question is "No". There isn't a way to group by the boolean portion of a case statement so it can be used in various ways within the select.

我赞成GMB和Martin Smith的回答，因为它们很有帮助且提供了很多信息.我正在玩交叉申请，这是肯定的学习经历.

I upvoted GMB's and Martin Smith's answers as they are helpful and informative. I am playing with the cross apply, a learning experience for sure.

这篇关于GROUP BY带有嵌套的case表达式-有更好的方法吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！