TSQL 选择一行或多行加入 [英] TSQL select one or many rows to join
问题描述
这个问题类似于:TSQL 从 2 中按一选择行条件，但我想要的结果不同
This is question similar to: TSQL select rows by one from 2 conditions, but it is different in results that I would like to have
我有一张这样的桌子:
ORDER_ID CODE1 CODE2 CODE3 STATUS TYPE SUM GROUP
1 '001' 'BIGP' NULL 4 'company' 120 48
2 '002' 'BIGP' NULL 1 'priv' 100 20
3 '001' NULL NULL 6 'priv' 50 49
4 '002' NULL 'L' 1 'company' 1253 22
第二个表如下:
ADDRESS_ID ORDER_ID ZIP TYPE ADD_DATE CATEGORY VERIFIED
1 1 '15-125' 'K1' '2010-01-01' 'CLIENT' 1
2 1 '22-022' 'D1' '2010-01-02' 'SYSTEM' 1
3 2 '16-159' 'D2' '2010-01-02' 'SYSTEM' 1
4 2 '15-125' 'D2' '2010-02-01' 'CLIENT' 0
第三个和第四个表包含邮政编码和城市名称，如下所示:
Third and fourth table contains zip codes and city names like so:
ZIP CITY
'15-125' 'Warszawa'
'22-022' 'Koszalin'
'16-159' 'Krakow'
'15-125' 'Lublin'
对于每个具有
- 状态不在 (4,6) 中
- '002' 和 '005' 之间的代码 1
- (code2=null and code3=null) or (code2 in ('BIGA', 'BIGP') and code3=null) or (code2=NULL and code3 = 'L')
如果 code1 ='002' AND group IN (48,59,60,87) 我必须选择一个地址
(非常感谢 Nikola Markovinović):
If code1 ='002' AND group IN (48,59,60,87) I must choose a single address
(big thanks to Nikola Markovinović):
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 group IN (48,59,60,87)
AND ((code2 IS NULL AND code3 IS NULL) OR (code2 IN ('BIGA', 'BIGP') AND code3 IS NULL) OR (code2 IS NULL AND code3 = 'L'))
对于所有其他符合最高标准并获得 code1 ='002' AND group NOT IN (48,59,60,87) 的订单，我必须为那些已验证的订单选择所有地址=1
For all other orders that meet top criteria and got code1 ='002' AND group NOT IN (48,59,60,87) I must select all addresses for those orders that have verified=1
收集这些地址后，我将能够检查特定的邮政公司是否可以将我的邮件投递到这些地址(我将检查另一个包含邮政编码的表格)
After collecting those addresses I will be able to check if a specific post company can deliver my mail to those addresses (I will check in another table containing zip codes)
我正在考虑联合所有，首先选择并与第二个联合，这将返回 code1 ='002' AND group NOT IN (48,59,60,87) 的所有地址.
I was thinking about making union all, taking first select and doing union with second that will return all addresses for code1 ='002' AND group NOT IN (48,59,60,87).
但是也许可以不使用 union all 来做到这一点?
But maybe it is possible to do it without union all?
这是我想要的最终结果:
This it the final result I would like to get:
CODE1 TYPE COUNT_OF_ORDERS COUNT_OF_ADDRESSES COMPANY1 OTHER
'001' 'NORMAL' 125 150 110 40
'002' 'NORMAL' 100 122 100 22
'003' 'NORMAL' 150 110 100 10
'004' 'NORMAL' 200 220 220 0
'005' 'NORMAL' 220 240 210 30
'005' 'PRIORITY' 100 110 110 0
'SX1' 'PRIORITY' 100 100 20 80
因此，如果我的类型是普通"，我必须检查订单地址是否存在于具有普通邮政编码的表格中，如果它的类型是优先"，我必须使用优先代码检查表格.
So if my type is 'normal' I must check if that address for order exists in table having normal zip codes, if it has type 'priority' I must check in table with priority codes.
如果代码存在于特定表中，我将 +1 添加到 COMPANY1 列，如果没有添加到 OTHER，那么这些列的总和必须是我的地址的总和.
If code exists in specific table I add +1 to COMPANY1 column, if not to OTHER, so that sum of those columns must be sum of my addresses.
这是我设法完成的查询(在@Nikola Markovinović 的帮助下)
This is query that I've managed to do (with help of @Nikola Markovinović)
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
AND code1='002'
AND o.[group] IN (48,59,60,87)
ORDER BY case a.Type
when 'D2' then 1
when 'K1' then 2
else 3
end,
a.ADD_DATE
UNION ALL
select
a.Address_Id,
a.Zip
from address a
WHERE a.order_Id = o.order_Id
AND ((code1='002' AND o.[group] NOT IN (48,59,60,87)) OR code1 IN ('001', '003', '004', '005'))
--I'm not shure of that top line, it work's but mayby it con de written better
AND Verified = 1
) a
WHERE
o.Status NOT IN (4, 6)
AND ((code2 IS NULL AND code3 IS NULL)
OR (code2 IN ('BIGA', 'BIGP') AND code3 IS NULL)
OR (code2 IS NULL AND code3 = 'L'))
推荐答案
您可以轻松过滤地址 ([group] IN (48,59,60,87) OR Verified = 1)，但是调整 TOP 1 会使事情变得荒谬(TOP(情况是 [group] IN (48,59,60,87) then 1 else (select count(*) from address where order_Id = o.order_Id) 结束).所以我建议你做 union all 但只针对地址:
You might filter addresses easily ([group] IN (48,59,60,87) OR Verified = 1), but tweaking TOP 1 would make things ridiculous (TOP (case when [group] IN (48,59,60,87) then 1 else (select count(*) from addresses where order_Id = o.order_Id) end). So I propose that you do union all but for adresses only:
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
AND o.[group] IN (48,59,60,87)
ORDER BY case a.Type
when 'D2' then 1
when 'K1' then 2
else 3
end,
a.ADD_DATE
UNION ALL
select
a.Address_Id,
a.Zip
from address a
WHERE a.order_Id = o.order_Id
AND o.[group] NOT IN (48,59,60,87)
AND Verified = 1
) 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'))
附言如果订单可能没有地址，请将 CROSS APPLY 替换为 OUTER APPLY.
P.S. If order might not have an address replace CROSS APPLY with OUTER APPLY.
这篇关于TSQL 选择一行或多行加入的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
