英文:
return rows with unique values in two columns, not just unique rows
问题
我有一个类似于订单表和发票表的情况。它们之间的关联应该是1:1的，然而，由于系统错误，一些发票的订单号丢失了。这让我感到痛苦和烦恼。
我需要尽量猜测哪些订单号属于这些孤立的发票。
大多数匹配方法都很直接，可以显著减少问题，从而使计算成本更高的方法能够处理剩余的部分。
每种方法都会排除一小部分重复项，因为该方法无法明确生成匹配项。
我需要构建一个包含两列唯一值的列表，这些值在每列的上下文中都是唯一的 - 不仅仅是唯一的行 - 出现在任何一列中的重复值都需要处理。
| 订单 | 发票 |
|---|---|
| 222 | 333 |
| 222 | 444 |
| 444 | 555 |
| 444 | 333 |
| 111 | 333 |
| 888 | 777 |
只有
| 订单 | 发票 |
|---|---|
| 888 | 777 |
应该被返回。
我尝试了几种计数方法，但都没有成功。
英文:
I have a situation akin to an order table and an invoice table. The link between them should be 1:1 however, due to systemic error, the order numbers for some invoices get lost. This causes me pain and aguish.
I need to make best guesses as to which order numbers belong to the orphaned invoices.
Most of the matching methods are straightforward and reduce the problem significantly allowing more computationally expensive methods to work on the remainder.
Each method will throw out a small number of duplicates because the method was not unambiguously able to generate a match
I need to build a list of keys in two columns that are unique in the context of each column - not just unique rows - instances where a value duplicates in any column are the ones that need acting on.
| ORD | INV |
|---|---|
| 222 | 333 |
| 222 | 444 |
| 444 | 555 |
| 444 | 333 |
| 111 | 333 |
| 888 | 777 |
only
| ORD | INV |
|---|---|
| 888 | 777 |
should be returned.
I've tried a few ways of counting but I've not had any success.
答案1
得分: 2
也许可以创建一个你想要的值列表（以列B的值为例）：
select B
from SourceTable
group by B
having count(*) = 1
然后只需与源表进行内连接：
select T.A, T.B
from SourceTable T
join (
select A
from SourceTable
group by A
having count(*) = 1
) ADist ON T.A = ADist.A
join (
select B
from SourceTable
group by B
having count(*) = 1
) BDist ON T.B = BDist.B
这是fiddle上的示例。
英文:
Maybe it's possible to create list of values you want (example for column B values):
select B
from SourceTable
group by B
having count(*) = 1
and just use inner join with source table
select T.A, T.B
from SourceTable T
join (
select A
from SourceTable
group by A
having count(*) = 1
) ADist ON T.A = ADist.A
join (
select B
from SourceTable
group by B
having count(*) = 1
) BDist ON T.B = BDist.B
Here is example on fiddle
答案2
得分: 2
使用一个（未选择的）聚合，带有having条件，限制每列的计数为1。
select A, B
from mytable
where A in (select A from mytable group by A having count(*) = 1)
and B in (select B from mytable group by B having count(*) = 1)
英文:
Use a (non-selected) aggregation with a having condition limiting the count to 1 for each column
select A, B
from mytable
where A in (select A from mytable group by A having count(*) = 1)
and B in (select B from mytable group by B having count(*) = 1)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论