添加值的计数

Question

还有其他方法可以用更少的代码来总结计数吗？

我正在使用一个视图(我唯一的选择)来试图找出一个客户是否在过去的5个日历年中的任何两个年里花了钱

表名: V_PERSON V_REVENUE

列: V_PERSON.ID

V.REVENUE.PersonID
V.REVENUE.Year1 revenue for the year (currently 2016)
V.REVENUE.Year2 revenue for the year (currently, 2015)
V.REVENUE.Year3
V.REVENUE.Year4
V.REVENUE.Year5

以下是我尝试过的：

SELECT V_PERSON.ID

FROM V_PERSON

WHERE 
(

(
SELECT '1'
FROM V_REVENUE
WHERE V_REVENUE.PersonID = V_PERSON.ID  
AND V_REVENUE.Year1 > 0
)

+

(
SELECT '1'
FROM V_REVENUE
WHERE V_REVENUE.PersonID = V_PERSON.ID  
AND V_REVENUE.Year2 > 0
)

+

(
SELECT '1'
FROM V_REVENUE
WHERE V_REVENUE.PersonID = V_PERSON.ID  
AND V_REVENUE.Year3 > 0
)

+

(
SELECT '1'
FROM V_REVENUE
WHERE V_REVENUE.PersonID = V_PERSON.ID  
AND V_REVENUE.Year4 > 0
)

+

(
SELECT '1'
FROM V_REVENUE
WHERE V_REVENUE.PersonID = V_PERSON.ID  
AND V_REVENUE.Year5 > 0
)

) >= 2

Answer 1

下面有一个使用exists和多个case语句的选项：

select id
from person p 
where exists (
    select 1
    from revenue r 
    where p.id = r.personid
        and case when r.year1 > 0 then 1 else 0 end +
            case when r.year2 > 0 then 1 else 0 end +
            case when r.year3 > 0 then 1 else 0 end +
            case when r.year4 > 0 then 1 else 0 end +
            case when r.year5 > 0 then 1 else 0 end >= 2
)

Answer 2

CASE语句如何：

SELECT V_PERSON.ID
FROM V_PERSON
WHERE 
(
SELECT  (case when V_REVENUE.Year1 > 0 then 1 else 0 end) +  
    (case when V_REVENUE.Year2 > 0 then 1 else 0 end) +
    (case when V_REVENUE.Year3 > 0 then 1 else 0 end) +
    (case when V_REVENUE.Year4 > 0 then 1 else 0 end) +
    (case when V_REVENUE.Year5 > 0 then 1 else 0 end) 
FROM V_REVENUE
WHERE V_REVENUE.PersonID = V_PERSON.ID) >= 2