时间戳

首页 > 解决方案 > 需要解决多个条件的复杂 where 子句的问题

sql-server - 需要解决多个条件的复杂 where 子句的问题

问题描述

我有一个我正在处理的项目，我将几个表组合成一个左连接，以便 dbo.spi 是主要的。我的问题来自 where 子句并使用 null 提取任何内容。

有几个标准需要满足才能显示在结果中。我的问题是，无论我如何编写 where 子句，某些标准似乎会破坏其他标准。我知道这与我的订单有关，但我不知道它是什么。我的标准如下。

我尝试了许多不同的命令和 () 但是无论我在某些时候做什么，where 子句中的某些标准似乎开始被忽略。对不起，这太长了，提前谢谢大家。

Part 1:
M.knum must be Null
D.Knum must be null
O.knum must be null
E.Knum must be null
st.customer_id must be Null

Part 2:
IF c.pbs#freq is A and c.pbs#days#delq <= 716 - Show in results
IF c.pbs#freq is E and c.pbs#days#delq <= 1446 - Show in results
IF c.pbs#freq is O and c.pbs#days#delq <= 1081 - Show in results
IF c.pbs#freq is null - Show in results

Part 3: 
i.date >= 5/1/2019 - Show in results 
i.date is Null - Show in results

Part 4:
p.d30 is <= 6 or null - Show in results
p.d60 is <= 1 or null - Show in results
p.d90 is <= 0 or null - Show in results

Part 5:
p.pmts_Made >= 12 or p.pmts_made is null - Show in results

    select s.knum,m.knum as M_knum, d.knum as D_knum, o.knum as O_knum, e.knum as E_knum, s.customer_id, st.customer_id as ST_Customer_id, s.fdd, s.status, s.state, 
        (select max(HighTrw) from (values (trw1), (trw2), (trw3)) as Value(HighTrw)) as high_trw, i.knum as Issued, isnull (i.date, '1980-01-01') as Issued_Date, c.PBS#DISP as MF_Disp, c.LN#DISP as LN_Disp,
        c.PBS#DAYS#DELQ as MF_Delq, c.PBS#FREQ as MF_Freq, c.LN#CURR#BAL as LN_Bal, c.LN#NPDD as NPDD, p.PMTS_Made as Pmts_Made, p.D30, p.D60, p.D90

    From dbo.SPI S

    left outer join dbo.combined C
        on s.knum = c.k
    Left outer join dbo.payments P
        on s.knum = p.knum 
    Left join dbo.dvn D
        on s.knum = d.knum
    Left join dbo.exclusion E
        on s.knum = e.knum
    left join dbo.issued I 
        on s.knum = i.knum
    left join dbo.outfield O
        on s.knum = o.knum 
    left join dbo.spi_tours ST
        on s.customer_id = st.customer_id
    left join dbo.magna M
        on s.knum = m.knum



        where

                              (c.pbs#freq = 'A' and c.pbs#days#delq <= 716  
                           or c.pbs#freq = 'E' and c.pbs#days#delq <= 1446 
                           or c.pbs#freq = 'O' and c.pbs#days#delq <= 1081 ) and

        (
       (i.date >= '2019-05-01' or i.date is null) 

        and

       (st.customer_id is null) 

       and    

       (p.d30 <= '6' or p.d30 is null) and (p.d60 <= '1' or p.d60 is null) and (p.d90 <= '0' or p.d90 is null)

                       )
       and       
               (

                     m.knum is null
                  and o.knum is null 
                  and e.knum is null 
                  and d.knum is null
                )
        and

                     (p.PMTS_Made >= '12' or p.pmts_made is null)

        and

        (
            (
                select max(HighTrw)
                from (values (trw1), (trw2), (TRW3)) As updatedate (HighTrw)
            )  between 625 and 900  
            and c.LN#CURR#BAL between '7500' and '15000' ) 

        or 

        (
            (
                select max(HighTrw)
                from (values (trw1), (trw2), (TRW3)) As updatedate (HighTrw)
            )  between 600 and 624  
            and c.LN#CURR#BAL between '5000' and '7499' ) 

        or

        (
            (
                select max(HighTrw)
                from (values (trw1), (trw2), (TRW3)) As updatedate (HighTrw)
            )  between 600 and 624  
            and c.LN#CURR#BAL between '3000' and '499' ) 
     ```     
    ```
    knum    M_knum  D_knum  O_knum  E_knum  customer_id ST_Customer_id  fdd status  state   high_trw    Issued  Issued_Date MF_Disp LN_Disp MF_Delq MF_Freq LN_Bal  NPDD    Pmts_Made   D30 D60 D90
    109924  NULL    NULL    NULL    NULL    102014  NULL    2018-07-20  Full    PA  618 109924  2019-11-18  A   T   0   O   5584.18 2019-12-05  14  0   0   0
    107238  NULL    NULL    NULL    NULL    97287   NULL    2016-07-21  Full    NY  609 107238  2019-04-15  A   A   0   O   6998.61 2019-11-20  39  0   0   0
    95516   NULL    NULL    NULL    NULL    73190   NULL    2009-11-15  Full    MD  618 95516   2019-11-11  A   T   372 A   7202.02 2019-11-20  67  0   0   0
    109927  NULL    NULL    NULL    NULL    93592   NULL    2018-06-16  Full    NJ  671 109927  2019-11-11  A   A   0   O   7580.33 2019-12-05  16  0   0   0
    105681  NULL    NULL    NULL    NULL    79131   NULL    2015-05-02  Full    NY  662 105681  2019-11-11  A   A   7   A   7787.22 2019-12-05  54  0   0   0
    109928  NULL    NULL    NULL    NULL    96359   NULL    2018-06-16  Full    NY  696 NULL    1980-01-01  A   T   7   A   9837.91 2019-12-05  16  0   0   0
    109928  NULL    NULL    NULL    NULL    96359   NULL    2018-06-16  Full    NY  724 NULL    1980-01-01  A   T   7   A   9837.91 2019-12-05  16  0   0   0
    109931  NULL    NULL    NULL    NULL    106351  NULL    2018-07-16  Full    NY  618 NULL    1980-01-01  A   A   0   O   5158.56 2019-09-05  12  5   3   0
    109934  NULL    NULL    NULL    NULL    102087  NULL    2018-06-26  Full    PA  656 109934  2019-11-18  A   A   7   A   10730.94    2019-11-20  15  0   0   0
    109935  NULL    NULL    NULL    NULL    108582  NULL    2018-07-15  Full    NY  642 109935  2019-10-14  A   A   372 A   11024.11    2019-12-05  15  0   0   0
    104320  NULL    NULL    NULL    NULL    81542   NULL    2014-06-10  Full    NY  688 104320  2019-09-01  A   A   7   A   8171.64 2019-12-05  65  0   0   0
    107249  NULL    NULL    NULL    NULL    102757  NULL    2016-07-22  Full    NJ  617 107249  2019-07-29  A   A   7   A   6156.54 2019-12-05  39  0   0   0
    109940  NULL    NULL    NULL    NULL    108507  NULL    2018-06-17  Full    NJ  774 NULL    1980-01-01  A   A   7   A   9678.42 2019-12-05  16  0   0   0
    107254  NULL    NULL    NULL    NULL    100012  NULL    2016-07-23  Full    DE  700 107254  2019-09-01  A   A   7   A   8457.08 2019-11-20  38  0   0   0
    99906   NULL    NULL    NULL    NULL    82770   NULL    2012-06-16  Full    NJ  707 99906   2019-09-01  A   T   7   A   8777.3  2019-11-20  88  0   0   0
    108521  NULL    NULL    NULL    NULL    97201   NULL    2017-08-18  Full    NY  615 108521  2019-03-31  A   A   7   E   6346.63 2019-12-05  27  0   0   0

标签： sql-serverwhere-clausessms-2017

解决方案

我尝试对您的查询进行排序，但仍然有很多令人困惑的事情，因为我不知道数据库的结构。

我认为您的主要查询应如下所示：

SELECT  s.knum,m.knum as M_knum, 
        d.knum as D_knum, 
        o.knum as O_knum, 
        e.knum as E_knum, 
        s.customer_id, 
        st.customer_id as ST_Customer_id, 
        s.fdd, s.[status], s.[state], 
    (
        SELECT max(HighTrw) 
        FROM (
                VALUES (trw1), (trw2), (trw3)) as Value(HighTrw)
    ) as high_trw, 
    i.knum as Issued, 
    ISNULL(i.date, '1980-01-01') as Issued_Date, 
    c.PBS#DISP as MF_Disp, 
    c.LN#DISP as LN_Disp,
    c.PBS#DAYS#DELQ as MF_Delq, 
    c.PBS#FREQ as MF_Freq, 
    c.LN#CURR#BAL as LN_Bal, 
    c.LN#NPDD as NPDD, 
    p.PMTS_Made as Pmts_Made, 
    p.D30, p.D60, p.D90
From dbo.SPI S 
LEFT OUTER JOIN dbo.combined C on s.knum = c.knum
Left outer join dbo.payments P on s.knum = p.knum 
Left join dbo.dvn D on s.knum = d.knum AND d.knum is null
Left join dbo.exclusion E on s.knum = e.knum AND e.knum IS null
left join dbo.issued I  on s.knum = i.knum
left join dbo.outfield O on s.knum = o.knum AND o.knum is null
 left join dbo.spi_tours ST on s.customer_id = st.customer_id AND st.customer_id IS Null
 left join dbo.magna M on s.knum = m.knum AND m.knum is null
 WHERE
    (   (c.pbs#freq = 'A' and c.pbs#days#delq <= 716 ) or (c.pbs#freq = 'E' and c.pbs#days#delq <= 1446) or (c.pbs#freq = 'O' and c.pbs#days#delq <= 1081) OR c.pbs#freq is null ) 
AND ( (CONVERT(DATE,i.[date]) >= CONVERT(DATE,'2019-05-01') or i.[date] is null) )
AND ( (CAST(p.d30 AS INT) <= 6 or p.d30 is null) AND (CAST(p.d60 AS INT) <= 1 or p.d60 is null) AND (CAST(p.d90 AS INT) <= 0 or p.d90 is null) )

AND (CAST(p.PMTS_Made AS INT) >= 12 or p.pmts_made is null)

我不确定您的以下查询部分，如果您能解释我会帮助您（我仍然尝试格式化此查询）：

AND (
        (
            SELECT max(HighTrw)
            from (values (trw1), (trw2), (TRW3)) As updatedate (HighTrw)
        )  between 625 and 900  and c.LN#CURR#BAL between '7500' and '15000' 
        or
        (
            (
            select max(HighTrw)
            from (values (trw1), (trw2), (TRW3)) As updatedate (HighTrw)
            )  BETWEEN 600 and 624  
            and c.LN#CURR#BAL between '5000' and '7499'
        )
        or
        (
            (
                select max(HighTrw)
                from (values (trw1), (trw2), (TRW3)) As updatedate (HighTrw)
            )  between 600 and 624  
            and c.LN#CURR#BAL between '3000' and '499' 
        ) 
    )

我建议首先运行上述部分并查看结果，希望您的所有过滤器都能正常工作，因为我在加入时很少有它们，其中很少有我只是通过适当的转换格式化，很少我只是尝试放入正确的大括号。

推荐阅读