Question

我正在尝试为以下搜索情况建立一个SQL语句：

我一直在尝试根据表B中状态列的值，返回表A中个人记录的所有列。表A中的每个记录都可能在表B中有多个行，这使得它成为一对多关系。状态列是可为空的，具有整数数据类型。

以下是表B中状态的可能取值：

NULL = Pending,
1 = Approved,
2 = Denied,
6 = Forced Approval,
7 = Forced Denial

最终用户可以在以下场景中进行搜索：

Approved - All table B records must have a value of 1 or 6 for status.
Denied - One table B record must have a value of 2 or 5. Any other records can have 1,6, or null.
Pending - All table B records can have a value of 1,6 or null. One record must be null because it is not considered completed.

UPDATE
I consulted with one of our DBAs and he developed the following solution:

批准。

SELECT a.* FROM TableA a INNER JOIN TableB ON b.id = a.id
WHERE
(b.status in (1,6) and b.status IS NOT NULL) AND
b.id NOT IN (SELECT id from TableB  WHERE status IS NULL)
AND b.id NOT IN (SELECT id from TableB WHERE status in (2,7))

被拒绝：

SELECT a.* FROM TableA a INNER JOIN TableB ON b.id = a.id
WHERE
(b.status in (2,7))

待定

SELECT a.* FROM TableA a INNER JOIN TableB ON b.id = a.id
WHERE
(b.status IN (1,6) OR b.status IS NULL)
AND b.id NOT IN (SELECT b.id FROM TableA a INNER JOIN TableB b ON b.id = a.id WHERE (b.status IN (1,6) AND b.status IS NOT NULL) AND b.id NOT IN (SELECT id from TableB WHERE status IS NULL))
AND b.id NOT IN (SELECT id FROM TableB WHERE status IN (2,7))

UPDATE 2:
@Micth Wheat - How would I refactor the following solution using the EXIST/NOT EXIST t-sql keyword?

Answer 1

作为批准的例子：

select 
    * 
from 
    A 
where
    (select count(*) from B where B.parent_id = A.id and B.status in (1,6)) > 0
and (select count(*) from B where B.parent_id = A.id and B.status not in (1,6)) = 0

重构，使用exists和not exists：

select 
    * 
from 
    A 
where
    exists (select * from B where B.parent_id = A.id and B.status in (1,6)) 
and not exists (select * from B where B.parent_id = A.id and B.status not in (1,6))

如果您已经通过了某个标准，您可以将其全部打包成一个查询，像这样，如果更方便的话：

select 
    * 
from 
    A 
where     
    (@Criteria =  Approved 
and (select count(*) from B where B.parent_id = A.id and B.status in (1,6)) > 0
and (select count(*) from B where B.parent_id = A.id and B.status not in (1,6)) = 0
    )
or  (@Criteria =  Denied 
and (select count(*) from B where B.parent_id = A.id and B.status in (2,7)) > 0
    )
or  (@Criteria =  Pending 
and (select count(*) from B where B.parent_id = A.id and B.status not in (2,7)) = 0
and (select count(*) from B where B.parent_id = A.id and B.status is null) > 0
    )

请注意，根据你的样本数据，我已将Denied示例更改为值为2和7，而不是2和5。

编辑：您也可以像乔建议的那样使用存在和不存在。

编辑：使用max(case ...)方法（通常也可看作sum(case ...)用于计算值），在某些情况下可以表现更好（这在很大程度上取决于您的数据量，性能提升是否显着-有时可能差别很大）。我个人认为子查询更易读，因此我会从它们开始，并且如果需要更好的性能，我会对两种方法进行基准测试，如果max(case ...) 的效果更好，我会切换。

Answer 2

From what I read Chris Teixeira and hova use basically the same logic, but;
- Hova parses the tables only once
- Chris Teixeira parses the tables multiple times
=>Hova s technique is preferred (in my opinion)

然而，Hova稍微做错了...

逻辑应该是：

- If Any 2 or 7 records   => DENIED
- ElseIf Any NULL records => PENDING
- Else                    => ACCEPTED

这给出了以下代码...

SELECT
    [main].id,
    CASE WHEN MAX(CASE WHEN [status].value IN (2,7) THEN 1 ELSE 0 END) = 1 THEN  DENIED 
         WHEN MAX(CASE WHEN [status].value IS NULL  THEN 1 ELSE 0 END) = 1 THEN  PENDING 
         ELSE  ACCEPTED  END
FROM
    [main]
INNER JOIN
    [status]
        ON [main].id = [status].main_id
GROUP BY
    [main].id

此外，使用MAX而不是SUM（Hova使用的方法）意味着查询引擎只需要找到一个匹配项，而不是多个。此外，优化器更容易在[状态]表上使用适当的索引。（在这种情况下，索引将按顺序为[状态]表上的（main_id，value））。

民主党。

修改：

类似的内容可能是这样的。因为我这里没有 SQL 实例进行测试，所以我无法告诉你它是否更快，但我想它可能会…

SELECT
    [main].id,
    MIN(CASE WHEN [status].value IN (2,7) THEN -1        -- Denied
             WHEN [status].value IS NULL  THEN  0        -- Pending
             ELSE                               1  END)  -- Accepted
FROM
    [main]
INNER JOIN
    [status]
        ON [main].id = [status].main_id
GROUP BY
    [main].id

修改：

另一种选择是只需加入映射表，而不是使用CASE语句。

DECLARE @map TABLE (
   status_value   INT,
   status_result  INT
   )

INSERT INTO @map VALUES (1,    1)
INSERT INTO @map VALUES (2,   -1)
INSERT INTO @map VALUES (6,    1)
INSERT INTO @map VALUES (7,   -1)
INSERT INTO @map VALUES (NULL, 0)

SELECT
    [main].id,
    MIN([map].status_result)
FROM
    [main]
INNER JOIN
    [status]
        ON [main].id = [status].main_id
INNER JOIN
    @map AS [map]
        ON [status].value = [map].status_value
        OR ([status].value IS NULL AND [map].status_value IS NULL)
        -- # This has been faster than using ISNULLs in my experience...
GROUP BY
    [main].id

Answer 3

也许像这样的东西

select CASE WHEN SUM(CASE WHEN ISNULL(b.status, 0) IN (1,6) THEN 1 ELSE 0 END) = COUNT(*) THEN  Approved 
            WHEN SUM(CASE WHEN ISNULL(b.status, 0) IN (2,5) THEN 1 ELSE 0 END) > 0 THEN  Denied 
            WHEN SUM(CASE WHEN ISNULL(b.status, 0) IN (1,0,6) THEN 1 ELSE 0 END) = COUNT(*)
                AND SUM(CASE WHEN b.status IS NULL THEN 1 else 0 END) > 0  THEN  Pending 
            ELSE  ???  END as Status, <a column list>
FROM A 
INNER JOIN b ON a.id = b.id
group by <a column list>

Answer 4

看起来你想使用一些存在语句。

对于那些所有值都必须为某些值的情况，您需要添加一个不存在以表示其他可能的值。

批准的

exists(select 1 from B where A.id = B.id and status in (1,6))
and not exists(select 1 from B where A.id = B.id and (status is null or status not in (1,6)))

Answer 5

您还询问了如何使用EXISTS解决查询问题。我仍然没有测试用例，但我会尝试以下内容...

批准的。

SELECT
    a.*
FROM
    TableA a
WHERE
    NOT EXISTS (SELECT * FROM TableB b WHERE b.id = a.id AND b.status IN (NULL,1,6))

拒绝了

SELECT
    a.*
FROM
    TableA a
WHERE
    EXISTS (SELECT * FROM TableB b WHERE b.id = a.id AND b.status IN (1,6))

待定。

SELECT
    a.*
FROM
    TableA a
WHERE
    EXISTS (SELECT * FROM TableB b WHERE b.id = a.id AND b.status IS NULL)
    AND NOT EXISTS (SELECT * FROM TableB b WHERE b.id = a.id AND b.status IN (1,6))

友情链接