时间戳

首页 > 解决方案 > CASE WHEN 具有特定条件和范围条件

sql - CASE WHEN 具有特定条件和范围条件

问题描述

我正在尝试编写一个查询来返回一系列帐户和一些特定帐户的值，到目前为止我编写的代码如下

Select 

Case 
        When right(m.account,9) = '1110.0130' then 'A'
        when right(m.account,9) = '1110.0131' then 'B'
        When right(m.account,9) = '1110.0132' then 'C'
        when right(m.account,9) between '3000.0000' and '3999.9999' then 'D' 
    ELSE '' END AS [Type], Left(M.Account, 4) as Entity, m.activity
FROM Test 

Where
 T.Accounting_Date between '02/01/2019' and '02/28/19' and (Left(M.Account,4) IN (@Entity) OR '' IN (@Entity)) and 
    (Right(M.Account,9) IN ('1110.0130','1110.0131','1110.0132') or substring(T.account,9,4) Between '3000' and '3999')

我遇到的问题是，如何在 where 子句之后放置一个条件，以仅提取一系列帐户，即 3000 - 4000 以及语句中已经存在的一些特定帐户。

标签： sqlreporting-services

解决方案

使用子查询选择您需要的列并对其进行过滤：

 Select TYPE, ENTITY, ACTIVITY from (
        Select
        Case 
                When right(m.account,9) = '1110.0130' then 'A'
                when right(m.account,9) = '1110.0131' then 'B'
                When right(m.account,9) = '1110.0132' then 'C'
                when right(m.account,9) between '3000.0000' and '3999.9999' then 'D' 
            ELSE '' END AS [Type], Left(M.Account, 4) as Entity, m.activity
        FROM Test 

        Where
         T.Accounting_Date between '02/01/2019' and '02/28/19' and (Left(M.Account,4) IN (@Entity) OR '' IN (@Entity)) and 
            (Right(M.Account,9) IN ('1110.0130','1110.0131','1110.0132') or substring(T.account,9,4) Between '3000' and '3999')
 ) t 
where TYPE = 'A'; -- your where clauses...

推荐阅读