卡在这个 union / except 上了

Question

我希望能找到最佳的方式来处理这个问题，但某些原因一直困扰着我。

我有如下数据：

transaction_id(pk)  decision_id(pk)   accepted_ind    
A                    1                NULL
A                    2                <blank>
A                    4                Y
B                    1                <blank>
B                    2                Y
C                    1                Y
D                    1                N
D                    2                O
D                    3                Y

1. 每个事务都保证有决策1
2. 可能会有多种决策可能性（例如"假设"的情况）
3. Accepted可以有多个值，也可以为空或为NULL，但只能有一个accepted_ind = Y

我正在尝试编写一个查询：

1. 为每个transaction_id返回一行
2. 如果accepted_ind = Y，则返回decision_id，如果事务没有行accepted_ind = Y，则返回decision_id = 1的行（不考虑accepted_ind中的值）

我已经尝试过： 1. 使用逻辑"or"来提取记录，但一直得到重复的结果。 2. 使用union和except，但无法正确理解逻辑。

非常感谢您的帮助。我真的不知道为什么这让我如此困扰！

Adam

- chris northwoods

Answer 1

试试这个。基本上WHERE子句的意思是：

当Accepted='Y'时
或者
对于此交易没有被接受的行，并且decision_id=1

SELECT Transaction_id, Decision_ID, Accepted_id
FROM MyTable t
WHERE Accepted_ind = 'Y'
OR (NOT EXISTS (SELECT 1 FROM MyTable t2
                WHERE Accepted_ind = 'Y'
                and t2.Transaction_id = t.transaction_id)
    AND Decision_id = 1)

- JNK

---

查询完全有意义，并给了我我所寻求的确切结果！我相信我看了这么久，以至于开始走错了路。 - chris northwoods

Answer 2

这种方法使用ROW_NUMBER()，因此仅适用于SQL Server 2005或更高版本

我已修改您的示例数据，因为目前所有的transaction_id都有一个Y指示器！

DECLARE @t TABLE ( 
    transaction_id NCHAR(1), 
    decision_id INT, 
    accepted_ind NCHAR(1) NULL 
)

INSERT @t VALUES
    ( 'A' , 1 , NULL ),
    ( 'A' , 2 , '' ),
    ( 'A' , 4 , 'Y' ),
    ( 'B' , 1 , '' ),
    ( 'B' , 2 , 'N' ),  -- change from your sample data
    ( 'C' , 1 , 'Y' ),
    ( 'D' , 1 , 'N' ),
    ( 'D' , 2 , 'O' ),
    ( 'D' , 3 , 'Y' )

以下是查询本身：

SELECT transaction_id, decision_id, accepted_ind FROM ( 
SELECT transaction_id, decision_id, accepted_ind, 
    ROW_NUMBER() OVER (
        PARTITION BY transaction_id 
        ORDER BY
            CASE
                WHEN accepted_ind = 'Y' THEN 1
                WHEN decision_id = 1 THEN 2
                ELSE 3
            END
    ) rn
FROM @t
) Raw
WHERE rn = 1

结果：

transaction_id decision_id accepted_ind
-------------- ----------- ------------
A              4           Y
B              1            
C              1           Y
D              3           Y

ROW_NUMBER()子句为提到的每个标准指定了“优先级”；然后我们使用ORDER BY选择最佳标准，并取第一行。

- AakashM

---

这是我开始构建的东西。它的好处在于您可以构建其他排名参数，例如已接受、空白，然后是第一。 - Conrad Frix

谢谢！这个方法也行，我从来没有考虑过这种方式，但我真的很喜欢它！ - chris northwoods

Answer 3

出于兴趣，以下使用了问题标题中指定的UNION、EXCEPT（加上一个JOIN）：

WITH T AS (SELECT * FROM (
              VALUES ('A', 1, NULL),
                     ('A', 2, ''),
                     ('A', 4, 'Y'),
                     ('B', 1, ''),
                     ('B', 2, 'Y'),
                     ('C', 1, 'Y'),
                     ('D', 1, 'N'),
                     ('D', 2, 'O'),
                     ('D', 3, 'Y'), 
                     ('E', 2, 'O'), -- smaple data extended
                     ('E', 1, 'N')  -- smaple data extended
             ) AS T (transaction_id, decision_id, accepted_ind)
     ) 
SELECT * 
  FROM T
 WHERE accepted_ind = 'Y'
UNION
SELECT T.* 
  FROM (
        SELECT transaction_id
          FROM T
         WHERE decision_id = 1
        EXCEPT 
        SELECT transaction_id
          FROM T
         WHERE accepted_ind = 'Y'
       ) D
       JOIN T 
          ON T.transaction_id = D.transaction_id
             AND T.decision_id = 1;

- onedaywhen

Answer 4

可能有更简洁/更高效的查询方法，但我认为这个可以完成任务。它假设表名为Decision：

  SELECT CASE 
         WHEN accepteddecision.transaction_id IS NOT NULL THEN 
         accepteddecision.transaction_id 
         ELSE firstdecision.transaction_id 
       END AS transaction_id, 
       CASE 
         WHEN accepteddecision.decision_id IS NOT NULL THEN 
         accepteddecision.decision_id 
         ELSE firstdecision.decision_id 
       END AS decision_id, 
       CASE 
         WHEN accepteddecision.accepted_ind IS NOT NULL THEN 
         accepteddecision.accepted_ind 
         ELSE firstdecision.accepted_ind 
       END AS accepted_ind 
FROM   decision 
       LEFT OUTER JOIN (SELECT * 
                        FROM   decision AS accepteddecision 
                        WHERE  accepteddecision.accepted_ind = 'Y') AS 
                       accepteddecision 
         ON accepteddecision.transaction_id = decision.transaction_id 
       LEFT OUTER JOIN (SELECT * 
                        FROM   decision AS firstdecision 
                        WHERE  firstdecision.decision_id = 1) AS firstdecision 
         ON firstdecision.transaction_id = decision.transaction_id 
GROUP  BY accepteddecision.transaction_id, 
          firstdecision.transaction_id, 
          accepteddecision.decision_id, 
          firstdecision.decision_id, 
          accepteddecision.accepted_ind, 
          firstdecision.accepted_ind 

- Paul Hunt

---

子查询不是必需的，只需在JOIN中使用And，例如JOIN Decision AcceptedDecision ON AcceptedDecision.transaction_id = Decision.transaction_id AND AcceptedDecision.accepted_ind = 'Y'。此外，您可以使用COALESCE代替CASE语句。 - Conrad Frix