如何改进SQL代码以获得正确的结果?

Well, I am not sure what is the best way to this problem. But let me give you an example. What I am trying to achieve here is get the P2 value which is equal to carry when trying to sum P2 values in the descending order.. 10 to 1.

我不知道解决这个问题最好的办法是什么。让我给你们举个例子。这里我要做的是得到P2的值，当我按降序对P2的值求和时，P2的值等于。10到1。

I have a huge table:

我有一张大桌子:

Category_Id Brand_Id Carry P2_0  P2_1  P2_3 ... P2_10
9           54       59    12    3     17       .
7           6        102   4     0     3        .
9           71       54    20    1     0        .
9           75       98    34    4     0        .
7           10       140   59    5     4        .

This is main logic of my code:

这是我的代码的主要逻辑:

SELECT CategoryCode,Brand_Id, (CASE
    WHEN P2_10 > =  Carry Then 'Error' 
    WHEN P2_10 + P2_9 > =  Carry Then '10' 
    WHEN P2_10 + P2_9 + P2_8  > =  Carry Then '9' 
    WHEN P2_10 + P2_9 + P2_8 + P2_7  >=  Carry Then '8' 
    WHEN P2_10 + P2_9 + P2_8 + P2_7 + P2_6  >=  Carry Then '7'
    WHEN P2_10 + P2_9 + P2_8 + P2_7 + P2_6 + P2_5 > =  Carry Then '6' 
    WHEN P2_10 + P2_9 + P2_8 + P2_7 + P2_6 + P2_5 + P2_4 > =  Carry Then '5'
    WHEN P2_10 + P2_9 + P2_8 + P2_7 + P2_6 + P2_5 + P2_4 + P2_3 > =  Carry Then '4' 
    WHEN P2_10 + P2_9 + P2_8 + P2_7 + P2_6 + P2_5 + P2_4 + P2_3 + P2_2 > =  Carry Then '3'
    WHEN P2_10 + P2_9 + P2_8 + P2_7 + P2_6 + P2_5 + P2_4 + P2_3 + P2_2 + P2_1 > =  Carry Then '2'
    WHEN P2_10 + P2_9 + P2_8 + P2_7 + P2_6 + P2_5 + P2_4 + P2_3 + P2_2 + P2_1 + P2_0 > =  Carry Then '1'
    ELSE NULL END) As Threshold  from BQ_15

Now the issue here is say for a brand_id 6 if the carry is 106 then

这里的问题是，对于brand_id 6，如果进位是106。

 P2_10(50) + P2_9(50) + P2_8(3) + P2_7(3) = Carry (106) gives the right result 

 but if  P2_10 + P2_9 + P2_8 + P2_7 > Carry it has to go back to previous result, if in the previous result the new P2 was '0' it has to back further. 

 so if P2_10(50) + P2_9(50) + P2_8(2) + P2_7(0) + P2_6(30) > Carry (106) then it should skip P2_7 (because it is zero) and go to P2_8 (desired result) but for my code it goes to P2_7.

I know I haven't included anything that skips '0' that is where my entire issue lies is how do I iterate in SQL that my code will work for both the cases and get the desired result.

我知道我没有包含任何跳过0的内容，这就是我的全部问题所在，就是我如何在SQL中迭代我的代码将对两个案例都起作用并得到想要的结果。

Thanks in advance

谢谢提前

2 个解决方案

#1

Use cross apply to make row scope calculations. I took 4 p2_xx columns, extend it as needed.

使用交叉应用进行行范围计算。我取了4个p2_xx列，根据需要进行扩展。

from (
     -- sample data
     values (9,54,106,  50,50,2,0,30)     
     ) hugeTable (Category_Id, Brand_Id, Carry, P2_10, P2_9, P2_8, P2_7, P2_6) 
cross apply (
    select Threshold = min(p2n)
    from (
        select p2n,
             s = sum(p2val) over(order by p2n desc)
        from (
             values
             (10, P2_10), (9, P2_9), (8, P2_8), (7, P2_7), (6, P2_6) 
        ) t(p2n, p2val)
        where p2val>0
    ) t
    where s <= Carry
) t

I use sum() over(), if you are on 2008 or earlier version then

我使用sum() over()，如果您在2008年或更早的版本中，那么

from (
     -- sample data
     values (9,54,106,  50,50,2,0,30)     
     ) hugeTable (Category_Id, Brand_Id, Carry, P2_10, P2_9, P2_8, P2_7, P2_6) 
cross apply (
    select Threshold = min(p2n)
    from (
        select p2n,
             s = (select sum(p2val)
                 from (
                      values
                      (10, P2_10), (9, P2_9), (8, P2_8), (7, P2_7), (6, P2_6)  
                 ) t2(p2n, p2val)
                 where t2.p2n>=t.p2n )
        from (
             values
             (10, P2_10), (9, P2_9), (8, P2_8), (7, P2_7), (6, P2_6)  
        ) t(p2n, p2val)
        where p2val>0
    ) t
    where s <= Carry
) t

#2

I doubt you'll have any issues with the previous answer using cross appy. If you do have complications or if you wanted to stick with your original case expression it isn't too difficult to find an expression that works--it's just a little messy. Here's what one of the cases could look like:

我怀疑你会对之前的答案有任何疑问。如果你确实有并发症，或者你想坚持原来的情况表达式，找到一个有效的表达式并不难——只是有点混乱。以下是其中一个案例:

WHEN P2_10 + P2_9 + P2_8 + P2_7 >= Carry
THEN coalesce(
    nullif(sgn(P2_8)  *  8, 0),
    nullif(sgn(P2_9)  *  9, 0),
    nullif(sgn(P2_10) * 10, 0),
    -1
)

And actually that's unnecessarily complicated when you really just need to nest case expressions. Perhaps that's something you didn't realize was possible:

实际上，当你需要嵌套case表达式时，这就不必要地复杂了。也许你没有意识到这是可能的:

WHEN P2_10 + P2_9 + P2_8 + P2_7 >= Carry
THEN case when P2_8 > 0 then 8 when P2_9 > 0 then 9 when P2_10 > 0 then 10 else -1 end

Do you ever have sequences that start out with all zeroes that then jump over the carry threshhold: (0, 0, 0, 200)? And what about sums that never exceed the carry? I don't believe the other answer covers that scenario.

你是否曾经有一个序列，它从所有的0开始，然后跳过带阈值:(0,0,0,200)?那么那些永远不会超过进位的和呢?我不相信另一个答案能解释这种情况。

#1