嗨，我有一个缺少标准的代码 [英] Hi, I have this code with missing Criteria

问题描述

[！[第一个输出][1]][1]

[！[第二个输出][2]][2]

[！[组合输出][3]][3]

[！[组合脚本][4]][4]

我有此代码，但"；R"；(LN-5360)中的条件不存在。但是，"；S"；中的条件确实存在总和值为‘30’的情况。但是，当我连接这两个查询时，它给我的"；MonthlyPremium"；为‘0’，而不是‘30’。有人能告诉我我做错了什么吗？我无法删除"；R"；，因为我有更多现有条件。

Select S.Newf, zeroifnull(Round(PR + PRE)) AS MonthlyPremium  

From
(  
SELECT TARGET_QUIKPLANSurv.FORM, Ifnull(TARGET_NEWQUIKVALFSurDec.MVALDATE, '2020-12-31') AS ReportDate ,(SUM(TARGET_NEWQUIKVALFSurDec.MANNLZD)/12) AS PR
from TARGET_QUIKPLANSurv Left JOIN TARGET_NEWQUIKVALFSurDec ON TARGET_QUIKPLANSurv.PLAN=TARGET_NEWQUIKVALFSurDec.NPLAN
GROUP BY TARGET_QUIKPLANSurv.FORM, TARGET_NEWQUIKVALFSurDec.MVALDATE
HAVING ((TARGET_QUIKPLANSurv.FORM)='LN-5360')
                
) as R

Right Join

( 
SELECT TARGET_QUIKPLAN.FORMS, Ifnull(TARGET_NEWQUIKVALF1.MVALDATE, '2020-12-31') AS ReportDate ,(SUM(TARGET_NEWQUIKVALF1.MANNLZD)/12) AS PRE,
     iff(TARGET_QUIKPLAN.FORMS='L-5360','LN-5360', TARGET_QUIKPLAN.FORMS ) As Newf  
   
from TARGET_QUIKPLAN Left JOIN TARGET_NEWQUIKVALF1 ON TARGET_QUIKPLAN.PLAN=TARGET_NEWQUIKVALF1.NPLAN
GROUP BY TARGET_QUIKPLAN.FORMS, TARGET_NEWQUIKVALF1.MVALDATE
HAVING ((TARGET_QUIKPLAN.FORMS)='L-5360')  
         
) as S on R.FORM = S.Newf 


  [1]: https://i.stack.imgur.com/2L0MO.png
  [2]: https://i.stack.imgur.com/VkPca.png
  [3]: https://i.stack.imgur.com/MO1uX.png
  [4]: https://i.stack.imgur.com/LFJYS.png

推荐答案

假设您在S侧有一个值(如您所注意的，值为30)，并且您正在进行右联接，则您有一行。花时间了解您正在使用的不同操作是如何工作的，因此0是从哪里来的：

SELECT pr
    ,pre
    ,pr + pre AS a
    ,round(a) AS ra
    ,ZEROIFNULL(ra) as monthlypremium
FROM VALUES 
    (0,30),   
    (null,30),
    (0,null),
    (null,null)
    v(pr,pre);
    

因此这是4行数据，具有不同的结果排列，实际上最后一个不会发生。

PR      PRE     A       RA      MONTHLYPREMIUM
0       30      30      30      30
null    30      null    null    0
0       null    null    null    0
null    null    null    null    0

所以这告诉我们null为空的加法，因此您需要将ZEROIFNULL移到加法之前。因此，您需要：

ROUND(ZEROIFNULL(pr) + ZEROIFNULL(pre)) AS monthlypremium

作为附注，将SQL SELECT放在新行上可以更容易在StackOverflow上读取，并且对表使用别名可以更快地读取，并且给定列的大小写并不重要，保留字和列的大小写不同也有助于理解。我还倾向于缩进和跟随WHERE子句，这样您就可以将内容换到新的行上，以保持阅读宽度不太宽，并且遵循这更多的是逻辑的最后挡路。

因此，我将为Style编写您的SQL：

select s.newf
    ,ROUND(ZEROIFNULL(pr) + ZEROIFNULL(pre)) AS monthlypremium
FROM (  
    SELECT q.form
        ,IFNULL(n.mvaldate, '2020-12-31') AS reportdate 
        ,SUM(n.mannlzd)/12 AS pr
    FROM target_quikplansurv AS q
    LEFT JOIN target_newquikvalfsurdec AS n
        ON q.plan = n.nplan
    GROUP BY q.form, n.mvaldate
    HAVING q.form='LN-5360'              
) AS r
RIGHT JOIN ( 
    SELECT q.forms
        ,ifnull(n.mvaldate, '2020-12-31') AS reportdate
        ,SUM(n.mannlzd)/12 AS pre
        ,iff(q.forms = 'L-5360', 'LN-5360', q.forms ) AS newf   
    FROM target_quikplan AS q
    LEFT JOIN target_newquikvalf1 AS n
        ON q.plan = n.nplan
    GROUP BY q.forms, n.mvaldate
    HAVING q.forms = 'L-5360'    
) AS s 
    ON r.form = s.newf 

最后SQL在做什么

    SELECT q.form
        ,IFNULL(n.mvaldate, '2020-12-31') AS reportdate 
        ,SUM(n.mannlzd)/12 AS pr
    FROM target_quikplansurv AS q
    LEFT JOIN target_newquikvalfsurdec AS n
        ON q.plan = n.nplan
    GROUP BY q.form, n.mvaldate
    HAVING q.form='LN-5360'

此处您按原始源表mvaldate分组，但您在SELECT中转换为默认值(‘2020-12-31’)，因此您指出mvaldate可以为NULL，但在原始表上按组输入

FORM,       mvaldate,     mannlzd
'LN-5360',  '2020-11-30', 120
'LN-5360',  '2020-12-31', 240
'LN-5360',  null,         360

将给出输出：

'LN-5360',  '2020-11-30', 10
'LN-5360',  '2020-12-31', 20
'LN-5360',  '2020-12-31', 30

因为第三行NULL不同于第二行‘2020-12-31’。也许你想要那样。但我怀疑这会让人困惑

还可以通过使用HAVING子句进行所有聚合。如果您有1000万行，并且form不同于‘LN-3560’，那么这些值都将被计算并丢弃，这意味着您应该切换到WHERE子句，因为您可能能够避免数据读取(由于微分区)和聚合。

这让我认为您真的希望您的SQL是这样的：

SELECT s.newf
    ,ROUND(ZEROIFNULL(pr) + ZEROIFNULL(pre)) AS monthlypremium
FROM (  
    SELECT q.form
        ,IFNULL(n.mvaldate, '2020-12-31') AS reportdate 
        ,SUM(n.mannlzd)/12 AS pr
    FROM target_quikplansurv AS q
    LEFT JOIN target_newquikvalfsurdec AS n
        ON q.plan = n.nplan
    WHERE q.form='LN-5360'   
    GROUP BY 1,2   
) AS r
RIGHT JOIN ( 
    SELECT q.forms
        ,ifnull(n.mvaldate, '2020-12-31') AS reportdate
        ,SUM(n.mannlzd)/12 AS pre
        ,iff(q.forms = 'L-5360', 'LN-5360', q.forms ) AS newf   
    FROM target_quikplan AS q
    LEFT JOIN target_newquikvalf1 AS n
        ON q.plan = n.nplan
    WHERE q.forms = 'L-5360'
    GROUP BY 1,2
) AS s 
    ON r.form = s.newf 

但根据您编写的代码，它也可能是：

SELECT s.newf
    ,ROUND(ZEROIFNULL(pr) + ZEROIFNULL(pre)) AS monthlypremium
FROM (  
    SELECT q.form
        ,IFNULL(n.mvaldate, '2020-12-31') AS reportdate 
        ,SUM(n.mannlzd)/12 AS pr
    FROM target_quikplansurv AS q
    LEFT JOIN target_newquikvalfsurdec AS n
        ON q.plan = n.nplan
    WHERE q.form='LN-5360'   
    GROUP BY 1,2          
) AS r
RIGHT JOIN ( 
    SELECT q.forms
        ,IFNULL(n.mvaldate, '2020-12-31') AS reportdate
        ,SUM(n.mannlzd)/12 AS pre
        ,'LN-5360' AS newf   
    FROM target_quikplan AS q
    LEFT JOIN target_newquikvalf1 AS n
        ON q.plan = n.nplan
    WHERE q.forms = 'L-5360'
    GROUP BY 1,2     
) AS s 
    ON r.form = s.newf 

这篇关于嗨，我有一个缺少标准的代码的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！