TSQL 从 2 个条件中按一个选择行

本文介绍了TSQL 从 2 个条件中按一个选择行的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一张这样的桌子:

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