时间戳

首页 > 解决方案 > SQL 根据多个条件过滤行并获取具有活动记录的匹配记录

sql-server - SQL 根据多个条件过滤行并获取具有活动记录的匹配记录

问题描述

从这里继续SQL 根据多个条件过滤行并获取匹配的记录

小提琴https://dbfiddle.uk/?rdbms=sqlserver_2017&fiddle=e20a7ec1ce1642565cf5b830d288e978

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,
    ISACTIVE BIT
)

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,1),(2,1,1),(3,1,1),(4,2,0),(5,2,1) -- 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
);

对于一个条目，如果它被删除，我们将其设置为 0，如果再次添加一个新条目，它将为 1。因此，如果特殊订单 ID 为 2 并且不活动，我也想显示这些记录。

编辑

这是我仅针对单个客户的数据

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,
    ISACTIVE BIT
)

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,100,0,1),
(5,100,1,1),
(6,100,1,4),
(7,100,1,5)

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

INSERT INTO @SPECIALORDERDATA VALUES(1,1,1),(2,1,1),(3,1,1),(4,2,1),(5,2,0) -- 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
);

如果您看到@SPECIALORDERDATA我的最后一个记录是不活动的，那么我想显示该记录。显然，我需要的是，如果@SPECIALORDERDATA匹配客户的最后一条记录处于活动状态，我需要隐藏其他显示。如果有条目(6,2,1)，那么在这种情况下我需要隐藏客户

标签： sql-server

解决方案

好的。对于这个附加条件，根据我对上一个问题的回答，您只需将该条件添加到 case 表达式中：

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 OR SOD.ISACTIVE = 0 THEN 1 END) = 0

推荐阅读