主页 / coding / 问题 / 79201351
Accepted
Chris
Asked: 2024-11-19 04:29:27 +0800 CST

使用具有不同条件的多个 where 语句

  • 772

我有以下 SQL 逻辑，由三个 where 语句组成，每个语句都用“OR”分隔。我的目标是检索第一个 where 语句中的所有数据，但仅排除符合后两个 where 语句中的条件的记录。当我按原样运行它时，我会获得所有记录。我认为这是因为第一个 where 语句，但我不知道如何更改它，以便它仅排除符合后两个 where 语句中的条件的记录。如果您能提供任何帮助来重写此逻辑，我将不胜感激。

WHERE 
(
    (ord_billto = 'CATGRI'
    AND o.ord_status = 'CMP'
    AND o.ord_startdate >= @StartDate and o.ord_startdate < @EndDate+1
    AND o.ord_invoicestatus = 'XIN')
OR
    (o.ord_billto = 'CATGRI'
    AND o.ord_status = 'CMP'
    AND o.ord_startdate >= @StartDate and o.ord_startdate < @EndDate+1
    AND o.ord_invoicestatus = 'XIN'
    AND o.ord_revtype4 IN ('GRIFF3')
    AND s.cmp_id NOT IN ('CATLAF10','ORDBEL', 'SKYLIV','SKYPLY'))
OR
    (o.ord_billto = 'CATGRI'
    AND o.ord_status = 'CMP'
    AND o.ord_startdate >= @StartDate and o.ord_startdate < @EndDate+1
    AND o.ord_invoicestatus = 'XIN'
    AND o.ord_revtype4 IN ('GRIFF1')
    AND s.cmp_id NOT IN ('CATGRI02','CATWAC01'))
)
sql-server

2 个回答

  1. Best Answer

    要“检索第一个[条件]内的所有数据，但排除...符合后两个[条件]条件的记录”，请尝试：

    WHERE (
    ord_billto = 'CATGRI'
    AND o.ord_status = 'CMP'
    AND o.ord_startdate >= @StartDate and o.ord_startdate < @EndDate+1
    AND o.ord_invoicestatus = 'XIN'
)
AND NOT (
    o.ord_revtype4 IN ('GRIFF3')
    AND s.cmp_id NOT IN ('CATLAF10','ORDBEL', 'SKYLIV','SKYPLY')
)
AND NOT (
    o.ord_revtype4 IN ('GRIFF1')
    AND s.cmp_id NOT IN ('CATGRI02','CATWAC01')
)

    （冗余条件已被删去。）

    或者等价地：

    WHERE (
    ord_billto = 'CATGRI'
    AND o.ord_status = 'CMP'
    AND o.ord_startdate >= @StartDate and o.ord_startdate < @EndDate+1
    AND o.ord_invoicestatus = 'XIN'
)
AND NOT (
    (
        o.ord_revtype4 IN ('GRIFF3')
        AND s.cmp_id NOT IN ('CATLAF10','ORDBEL', 'SKYLIV','SKYPLY')
    )
    OR (
        o.ord_revtype4 IN ('GRIFF1')
        AND s.cmp_id NOT IN ('CATGRI02','CATWAC01')
    )
)
    • 2 
  2. SELECT * FROM [TABLE]

WHERE ord_billto = 'CATGRI'
    AND o.ord_status = 'CMP'
    AND o.ord_startdate >= @StartDate and o.ord_startdate < @EndDate+1
    AND o.ord_invoicestatus = 'XIN'

AND [PRIMARYKEY] NOT IN (SELECT [PRIMARYKEY] FROM [TABLE]
                        WHERE o.ord_billto = 'CATGRI'
                        AND o.ord_status = 'CMP'
                        AND o.ord_invoicestatus = 'XIN'
                        AND o.ord_startdate >= @StartDate and o.ord_startdate < @EndDate+1
                        AND (

                            (o.ord_revtype4 IN ('GRIFF3')
                            AND s.cmp_id NOT IN ('CATLAF10','ORDBEL', 'SKYLIV','SKYPLY'))
                        OR
                            (o.ord_revtype4 IN ('GRIFF1')
                            AND s.cmp_id NOT IN ('CATGRI02','CATWAC01'))
                            )
                        )
    • 0

相关问题