TSQL 从 2 个条件中按一个选择行 [英] TSQL select rows by one from 2 conditions

问题描述

我有一张这样的桌子:

ORDER_ID   CODE1   CODE2   CODE3   STATUS
1          '001'   'BIGP'  NULL    4
2          '002'   'BIGP'  NULL    1
3          '001'    NULL   NULL    6
4          '002'    NULL   'L'     1

第二个表如下:

ADDRESS_ID   ORDER_ID  TYPE   ADD_DATE       CATEGORY
1            1         'K1'   '2010-01-01'   'CLIENT'
2            1         'D1'   '2010-01-02'   'SYSTEM'
3            2         'D2'   '2010-01-02'   'SYSTEM'
4            2         'D2'   '2010-02-01'   'CLIENT'

如果要为每个具有以下条件的订单我必须做什么:

What I must do if to for every order that has:

• 状态不在 (4,6) 中
• code1='002'
• (code2=null and code3=null) or (code2 in ('BIGA', 'BIGP') and code3=null) or (code2=NULL and code3 = 'L')

我必须选择一个类型为D2"或K1"的地址(D2 具有更高的优先级，因此如果有 2 个地址，一个是 K1，另一个是 D2，我必须选择 D2).
如果没有任何类型为 D2 或 K1 的地址，我必须为该订单选择类别为客户"的最旧地址.

I must choose a single address that has type 'D2' or 'K1' (D2 has higher priority, so if there will be 2 addresses one K1 and second D2 I must choose D2).
If there aren't any addresses with type D2 or K1 I must choose the oldest address with category 'CLIENT' for that order.

这是我创建的:

SELECT TOP 1000 o.order_Id
              , a.Address_Id
              , a.Zip
            --, *
FROM orders o
 address a
    ON a.order_Id = o.order_Id
WHERE
 (a.Type='D2' OR a.Type='K1')
 AND o.Status NOT IN (4, 6)
 AND code1='002'
 AND ((code2 IS NULL AND code3 IS NULL) OR (code2 IN ('BIGA', 'BIGP') AND code3 IS NULL) OR (code2 IS NULL AND code3 = 'L'))

推荐答案

您可以使用 CROSS APPLY 用于任务，因为它允许在 派生表 中使用 TOP 1/ORDER BY 语句.

You might employ CROSS APPLY for the task as it allows use of TOP 1 / ORDER BY statements in derived table.

SELECT TOP 1000 o.order_Id
              , a.Address_Id
              , a.Zip
            --, *
FROM orders o
CROSS APPLY
(
 select TOP 1
        a.Address_Id,
        a.Zip
   from address a
  WHERE a.order_Id = o.order_Id
  ORDER BY case a.Type 
                when 'D2' then 1 
                when 'K1' then 2 
                else 3 
            end,
        a.ADD_DATE
) a
WHERE
 o.Status NOT IN (4, 6)
 AND code1='002'
 AND ((code2 IS NULL AND code3 IS NULL) OR (code2 IN ('BIGA', 'BIGP') AND code3 IS NULL) OR (code2 IS NULL AND code3 = 'L'))

除此之外，您可能会加入每个订单类型最少的地址派生表:

Other than that, you might join in derived table of addresses with minimal Type per order:

from orders o
inner join address a
  ON a.order_Id = o.order_Id
inner join
(
  select a.order_id, a.Type,
         row_number () over (partition by a.order_id
                             order by case a.Type when 'D2' then 1 
                                                  when 'K1' then 2 
                                                  else 3 
                                       end, 
                                      a.ADD_DATE) rn
    from Address a
   group by a.order_id, a.Type
) onlyOneAddress
  on a.order_id = onlyOneAddress.order_id
 and a.type = onlyOneAddress.type
 and onlyOneAddress.rn = 1

这篇关于TSQL 从 2 个条件中按一个选择行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！