sql - 排除服务代码为“AAAA”的订单
问题描述
一个订单可以有多个服务。根据功能,我必须从具有服务代码的选择中删除那些订单'AAAA'
。我的意思是如果一个订单有两个服务代码'AAAA'
和'AAAB'
. 那么这不能包含在输出中,因为这个订单有一个服务代码'AAAA'
。请参阅下面的屏幕截图以获取更多信息。
询问:
Select BO.OrderID ,OrderDate ,BackgroundServiceID , BST.BST_Name ,BST_Code from
BackgroundOrder BO
JOIN BackgroundOrderService BOS ON BO.OrderID = BOS.OrderID
JOIN ams.lkpBkgSvcType BST ON BST.BST_ID = BOS.BackgroundServiceID
预期输出查询:
Select BO.OrderID ,OrderDate ,BackgroundServiceID , BST.BST_Name ,BST_Code from
BackgroundOrder BO
JOIN BackgroundOrderService BOS ON BO.OrderID = BOS.OrderID
JOIN ams.lkpBkgSvcType BST ON BST.BST_ID = BOS.BackgroundServiceID
Where BST.BST_Code = 'AAAB' AND BO.OrderID not in
(Select BO.OrderID from
BackgroundOrder BO
JOIN BackgroundOrderService BOS ON BO.OrderID = BOS.OrderID
JOIN ams.lkpBkgSvcType BST ON BST.BST_ID = BOS.BackgroundServiceID
Where BST.BST_Code = 'AAAA'
)
请建议一些其他方法来获得上述输出而不使用 not-in 语句。
解决方案
您可以将查询包装在 a 中cte
并计算每个 OrderId 出现的“AAAA”,然后过滤它为零的位置
with t as (
select BO.OrderID, OrderDate, BackgroundServiceID, BST.BST_Name, BST_Code,
Sum(case when bst_code='AAAA' then 1 else 0 end) over(partition by BO.OrderID) cnt=0
from BackgroundOrder BO
join BackgroundOrderService BOS on BOS.OrderID = BO.OrderID
join ams.lkpBkgSvcType BST on BST.BST_ID = BOS.BackgroundServiceID
)
select OrderID, OrderDate, BackgroundServiceID, BST_Name, BST_Code
from t
where cnt=0
推荐阅读
- javascript - 如何在平面列表中呈现 responsejson 数组?
- f# - F#:如何检查序列内容的序列?
- validation - 专门在 Mask-R-CNN 中的测试和验证有什么区别?
- google-chrome - 无法在 Google Chrome 中查看 FTP 目录
- angularjs - How to use momentJs in Karma/Jasmine unit tests for AngularJS?
- android - my app crashes because firebase admob dependency flutter
- python - Getting error : IndexError: list index out of range'
- bash - How to find precise numbers in line and print the line with them?
- android - Unable to open google play store from android webview
- php - 使用 composer 安装包时出错,但包似乎已安装