代码之家 › 专栏 › 技术社区 › Developer

SQL根据多个条件过滤行,得到匹配的记录

sql-server

Developer · 技术社区 · 6 年前

DECLARE @CUSTOMER TABLE
(
    CUSTOMERID INT,
    CUSTOMERNAME NVARCHAR(100)
)

DECLARE @ORDER TABLE
(
    ORDERID INT,
    CUSTOMERID INT,
    ISSPECIALORDER INT,
    SPECIALORDERID INT
)

DECLARE @SPECIALORDERDTL TABLE
(
    SPECIALORDERID INT,
    SPECIALORDERDATAID INT
)

DECLARE @SPECIALORDERDATA TABLE
(
    SPECIALORDERDATAID INT,
    SPECIALORDERMASTERID INT
)

INSERT INTO @CUSTOMER VALUES (100,'CUSTOMER1'),(200,'CUSTOMER2'),(300,'CUSTOMER3'),(400,'CUSTOMER4`enter code here`')

INSERT INTO @ORDER VALUES (1,100,0,1),(2,100,1,1),(3,100,1,2),(4,200,0,1),(5,200,1,1),(6,200,1,4),(7,300,1,5),(8,400,1,6)

INSERT INTO @SPECIALORDERDTL VALUES(1,1),(2,1),(3,2),(4,4)

INSERT INTO @SPECIALORDERDATA VALUES(1,1),(2,1),(3,1),(4,2),(5,2) -- 2 a special order

SELECT  C.CUSTOMERID,C.CUSTOMERNAME
FROM    @ORDER O
INNER JOIN @CUSTOMER C ON C.CUSTOMERID=O.CUSTOMERID

INNER JOIN @SPECIALORDERDTL SO ON SO.SPECIALORDERID = O.SPECIALORDERID
INNER JOIN @SPECIALORDERDATA SOD ON SO.SPECIALORDERDATAID = SOD.SPECIALORDERDATAID
WHERE SOD.SPECIALORDERID <> 2 AND O.ISSPECIALORDER =0
    GROUP BY C.CUSTOMERID,C.CUSTOMERNAME
    ORDER BY C.CUSTOMERNAME

当我进入 @SPECIALORDERDT 我和 SPECIALORDERMASTERID

3 回复 | 直到 6 年前

Prashant Pimpale Dila Gurung 6 年前

从您的描述或SQL中不清楚您到底想要什么。据我所知:

DECLARE @CUSTOMER TABLE
(
    CUSTOMERID INT,
    CUSTOMERNAME NVARCHAR(100)
)

DECLARE @ORDER TABLE
(
    ORDERID INT,
    CUSTOMERID INT,
    ISSPECIALORDER INT,
    SPECIALORDERID INT
)

DECLARE @SPECIALORDERDTL TABLE
(
    SPECIALORDERID INT,
    SPECIALORDERDATAID INT
)

DECLARE @SPECIALORDERDATA TABLE
(
    SPECIALORDERDATAID INT,
    SPECIALORDERMASTERID INT
)

INSERT INTO @CUSTOMER VALUES 
(100,'CUSTOMER1'),
(200,'CUSTOMER2'),
(300,'CUSTOMER3'),
(400,'CUSTOMER4')

INSERT INTO @ORDER VALUES 
(1,100,0,1),
(2,100,1,1),
(3,100,1,2),
(4,200,0,1),
(5,200,1,1),
(6,200,1,4),
(7,300,1,5),
(8,400,1,6)

INSERT INTO @SPECIALORDERDTL VALUES(1,1),(2,1),(3,2),(4,4)

INSERT INTO @SPECIALORDERDATA VALUES(1,1),(2,1),(3,1),(4,2),(5,2) -- 2 a special order

SELECT  C.CUSTOMERID,C.CUSTOMERNAME
from @Customer c
where exists (select * from @ORDER o where o.CustomerId = c.CustomerId)
and not exists (
select * 
from @ORDER O
LEFT JOIN @SPECIALORDERDTL SO ON SO.SPECIALORDERID = O.SPECIALORDERID
LEFT JOIN @SPECIALORDERDATA SOD ON SO.SPECIALORDERDATAID = SOD.SPECIALORDERDATAID
WHERE (SO.SPECIALORDERID IS NULL 
or SOD.SPECIALORDERMASTERID = 2 --AND O.ISSPECIALORDER =0
) AND O.CustomerId = c.CustomerId
);
GO

CUSTOMERID | CUSTOMERNAME
---------: | :-----------
       100 | CUSTOMER1

here

Zohar Peled 6 年前

假设我理解这个问题,我认为having子句中的条件聚合可能是获得所需结果的最简单方法:

SELECT C.CUSTOMERID, C.CUSTOMERNAME
FROM @CUSTOMER As C
JOIN @ORDER O 
    ON C.CUSTOMERID = O.CUSTOMERID
JOIN @SPECIALORDERDTL SO 
    ON O.SPECIALORDERID = SO.SPECIALORDERID
JOIN @SPECIALORDERDATA SOD
    ON SO.SPECIALORDERDATAID = SOD.SPECIALORDERDATAID
GROUP BY C.CUSTOMERID, C.CUSTOMERNAME
HAVING COUNT(CASE WHEN SOD.SPECIALORDERMASTERID = 2 THEN 1 END) = 0

Lord Peter 6 年前

根据您的描述,听起来并不是每个客户都会在speciallorderdtl或speciallorderdta中有一个条目,所以您不希望内部联接到这些表。

您需要的是一个“notexists”相关子查询,以检查客户在这些表中是否没有匹配的行。

因此,您可以删除特殊*表的内部联接并添加一些内容like:-

 where not exists (select null from SPECIALORDERDTL SO where
 SO.SPECIALORDERID = O.SPECIALORDERID and SO.SPECIALORDERMASTERID = 2)

根据您的描述,我不太确定“SOD.speciallorderid<&燃气轮机;2和O.ISSPECIALORDER=0“匹配,因此如果无法使用子查询解析,请提供输出的详细信息。

在你澄清之后,请尝试this:-

SELECT  distinct C.CUSTOMERID,C.CUSTOMERNAME
FROM    @ORDER O
INNER JOIN @CUSTOMER C ON C.CUSTOMERID=O.CUSTOMERID
where not exists 
(select null from @SPECIALORDERDTL SO 
INNER JOIN @SPECIALORDERDATA SOD ON SO.SPECIALORDERDATAID = SOD.SPECIALORDERDATAID 
where SO.SPECIALORDERID = O.SPECIALORDERID and 
SOD.SPECIALORDERMASTERID = 2
)
order by C.CUSTOMERNAME