在tsql中使用布尔代数以避免CASE语句或处理复杂的WHERE条件 [英] Use Boolean algebra in tsql to avoid CASE statement or deal complex WHERE conditions

问题描述

我遇到了一个场景，我将用一些虚拟数据来解释它.见下表

I came across a scenario,I will explain it with some dummy data. See the table Below

Select * from LUEmployee

empId   name    joiningDate
1049    Jithin  3/9/2009
1017    Surya   1/2/2008
1089    Bineesh 8/24/2009
1090    Bless   7/15/2009
1014    Dennis  1/5/2008
1086    Sus     9/10/2009

仅当月份为1月，3月，7月或12月时，我才需要将年份列增加1.

empId   name    joiningDate derived Year
1049    Jithin  3/9/2009    2010
1017    Surya   1/2/2008    2009
1089    Bineesh 8/24/2009   2009
1090    Bless   7/15/2009   2010
1014    Dennis  1/5/2008    2009
1086    Sus     9/10/2009   2009

派生的年份是必填列

我们可以通过以下案例陈述轻松实现这一目标

We were able to achieve this easily with a case statement like below

Select *,
YEAR(joiningDate) + CASE WHEN MONTH(joiningDate) in (1,3,7,12) THEN 1 ELSE 0 END 
from LUEmployee

但是，现场PM带来了附加条件，不要使用CASE语句，CASE效率低下. 经过研究，我们得出了以下解决方案，一种使用二进制K-map的解决方案，如下所示

But there came an added condition from onsite PM, Dont use CASE statement, CASE is inefficient. Insearch of a soultion, We resulted in a following solution, a solution using binary K-map, As follows

如果数字1到12代表从一月到十二月的月份，请参见二进制结果

If number 1 to 12 represents months from Jan to Dec, See the binary result

卡诺地图的表达方式如下.

the Karnaugh Map way of expressing is given below.

结果将是

我们需要使用sql server二进制操作来实现表达式

We need to realize the expression with sql server binary operations

eg: binary of 12 = 1100
    in the k-map, a = 1, b = 1, c = 0, d = 0
    Similarly, binary of 7 = 0111
    in the k-map, a = 0, b = 1, c = 1, d = 1

要获得最左边的位(d)，我们必须将其向右移动3个位置，并屏蔽除LSB以外的所有位.

to get the left most bit (d), we will have to shift the bit towards right by 3 positions and the mask all the bits except LSB.

eg: ((MONTH(joiningDate)/8)&1)

类似地，从左边(c)移出第二个位，我们需要将该位向右移2个位置，然后屏蔽除LSB以外的所有位

Similarly, second bit from left (c), we need to shift the bit towards right by 2 positions and then mask all the bits except LSB

eg: ((MONTH(joiningDate)/4)&1)

最后，每个位都可以表示为

Finally, each bit can be represented as

so  a = ((MONTH(joiningDate)/8)&1)
    b = ((MONTH(joiningDate)/4)&1)
    c = ((MONTH(joiningDate)/2)&1)
    d = (MONTH(joiningDate)&1)

a inverse = (((MONTH(joiningDate)/8)&1)^1)
b inverse = (((MONTH(joiningDate)/4)&1)^1)
c inverse = (((MONTH(joiningDate)/2)&1)^1)
d inverse = ((MONTH(joiningDate)&1)^1)

最终代码为

SELECT  *,
        YEAR(joiningDate) + CAST(
        ((MONTH(joiningDate)/8)&1)*((MONTH(joiningDate)/4)&1)*(((MONTH(joiningDate)/2)&1)^1)*((MONTH(joiningDate)&1)^1) |
        (((MONTH(joiningDate)/8)&1)^1)*(((MONTH(joiningDate)/4)&1)^1)*(MONTH(joiningDate)&1) |
        (((MONTH(joiningDate)/8)&1)^1)*((MONTH(joiningDate)/2)&1)*(MONTH(joiningDate)&1) 
        AS INT) [derivedYear]
FROM    LUEmployee

结果将是

问题: 可能有简单但不太复杂的想法，请分享.

Question: There may be simple and less complex ideas, please share it.

我喜欢找到一个更简单的方法，并且喜欢这个想法.这里可能的条件是12(12个月).我们可以使用k-map来处理更多的条件.感觉像k-map一样方便多达64个条件.

I like to find a simpler one ,as well as share the idea.Here the possible conditions are 12 (12 months). We can use k-map for even bigger number of conditions..Felt like k-map is convenient for up to 64 conditions.

推荐答案

在这种情况下，我的第一反应是捍卫case子句的使用.但是，如果绝对不允许使用该表，也许您可​​以简单地添加一个带有month和 increment 值的表:

My first reaction would be to defend the use of the case clause in this case. But if you are absolutely not allowed to use it, maybe you could simply add a table with the month and increment values:

LUMonthIncrement

Month   Increment
 1      1  
 2      0  
 3      1  
 4      0  
 5      0  
 6      0  
 7      1  
 8      0  
 9      0  
10      0  
11      0  
12      1  

然后，您可以加入该表并添加增量:

Then you can join in that table and just add the increment:

Select LUEmployee.*,
    YEAR(joiningDate) + LUMonthIncrement.Increment as derivedYear
from LUEmployee
    join LUMonthIncrement on MONTH(LUEmployee.joiningDate) = LUMonthIncrement.Month

这不太可能具有更高的性能，因为要连接到LUMonthIncrement，必须为LUEmployee表中的每一行评估MONTH(LUEmployee.joiningDate)表达式.

This is unlikely to be much more performant though, because in order to join to LUMonthIncrement the MONTH(LUEmployee.joiningDate) expression must be evaluated for each row in the LUEmployee table.

这篇关于在tsql中使用布尔代数以避免CASE语句或处理复杂的WHERE条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！