任何人都可以推荐一种（SQL？）算法来解决多个排除多个问题 [英] Can anyone recommend an (SQL? ) Algorithm to resolve multiple many-many exclusions
问题描述
首先，我是新来的 - 所以如果这是一个微不足道的问题，已经得到回答，或者我已经将它发布在完全错误的地方，我很高兴被打倒。
我是一个严重生锈的前开发者，我遇到了一个我被阻止的问题。对于经验丰富的人来说，这可能是显而易见的，但是经过头脑风暴，讨论和搜索，我现在都不是更聪明的。
我正在研究遗产系统具有非常糟糕的数据结构，我无法做任何事情。以下是问题的简化，但我认为这是解释它的最佳方式。
关于我正在尝试做什么：
在数据库中我有发货记录A - G
我想删除所有已完成的货件。发货A - E已完成，但不是F或G.
系统中还有几个发票。
规则：
1.如果已完成的货件是包含未完成的其他货件的发票的一部分，则无法将其删除/>
2.如果已完成的货件是包含其他无法删除的货件的发票的一部分，则无法将其删除
问题：
- 发票I1包含货件A，B和C.由于这些都已完成，我可以将它们全部删除
- 发票I2包含货件A，F和G.作为F& G没有完成，我不能删除它们中的任何一个
- 但是，因为A不能被规则1删除，因为它是Invoice I1的一部分，不能B或C
...我们需要检查任何其他发票B& C是...的一部分...
...等等到无限......
...而对于我的生活，我想不出一个简单，快速的SQL语句或算法来做除了蛮力的矿石递归 - 直到结束
感激地收到任何其他建议！
如果这很明显，也会道歉，但我只是过度思考/没有看到明显的自己..
我尝试了什么：
我试过加载所有已完成发货到临时表，并反复运行'是包含未完成货件的发票成员'和'是包含不可删除货件的发票成员'，直到我得到一个错误的结果..
..但感觉效率不高......
首先，不，这是正确的地方 - 并欢迎来到CP！
SQL有一个EXCEPT子句： EXCEPT和INTERSECT（Transact-SQL） [ ^ ]从一个查询中删除另一个查询中的条目。
所以我要做的是：
1）选择所有已完成的货物。
2）选择所有未完成项目的发票中的货件。
3）使用EXCEPT从1中删除2中的所有内容。
4）SELECT发票仅包含3中的项目。
我不知道这与你的系统有多复杂 - 我不知道数据结构 - 但是应该解决三个选择：
SELECT ... FROM
（ SELECT 已完成
EXCEPT SELECT uncom根据您的数据结构，外部的JOIN或WHERE都有。
一旦我仔细检查了它，它就会变成DELETE。
Firstly, I'm new on here - so I'm happy to be slapped down if this is a trivial question, has already been answered, or I've posted it in completely the wrong place.
I'm an ex-developer who's got seriously rusty, and I've come across a problem that I'm blocked on. It might be obvious to someone more experienced, but having brainstormed, discussed and searched for it, I'm currently none the wiser.
I'm working on a legacy system with a pretty awful data structure which I've not options to do anything about. The below's a simplification of the problem, but I think is the best way to explain it.
As regards what I'm trying to do :
In the database I have shipment records A - G
I want to delete all the completed shipments. Shipments A - E are completed, but not F or G.
There are also several Invoices in the system.
Rules :
1. If a completed Shipment is part an Invoice that contains other Shipments that aren't completed, it can't be deleted
2. If a completed Shipment is part an Invoice that contains other Shipments that can't be deleted, it can't be deleted
Problem :
- Invoice I1 contains Shipments A, B and C. As those are all Completed, I could delete them all
- Invoice I2 contains Shipments A, F and G. As F & G are not Completed, I can't delete any of them
- BUT, because A cannot be deleted, by Rule 1, because it is part of Invoice I1, neither can B or C
...we'd then need to check any other invoices B & C are part of...
...and so on to infinity...
...and for the life of me I can't think of a simple, quick SQL statement or algorithm to do this other than by brute force ore recursion-until-end
Any alternative suggestions gratefully received!
Apologies also if this is obvious, but I'm just over-thinking it / not seeing the obvious myself..
What I have tried:
I've tried loading all Completed Shipments into a temporary table, and repeatedly running 'Is member of Invoice containing non-Completed Shipments' and 'Is member of Invoice containing un-delete'able Shipments' repeatedly until I get a False result..
..but that doesn't feel hugely efficient...
First off, no, this is the right place - and welcome to CP!
SQL has an EXCEPT clause: EXCEPT and INTERSECT (Transact-SQL)[^] which removes entries from one query that are present in the other.
So what I'd do is:
1) SELECT all shipments that are completed.
2) SELECT all shipments that are in invoices with uncompleted items.
3) Use EXCEPT to remove everything in 2 from 1.
4) SELECT invoices which contain only items left in 3.
I don't know how complex this is to do with your system - I don't know the data structure - but that should resolve to three selects:
SELECT ... FROM (SELECT completed EXCEPT SELECT uncompleted)With either a JOIN or a WHERE on the outer one depending on your data structure.
Once I'd got it checked damn carefully, it could become a DELETE.
这篇关于任何人都可以推荐一种（SQL？）算法来解决多个排除多个问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
