比较同一表的行和列 [英] Compare rows and columns of same table

问题描述

+--------------+--------------+------------+
| company_name | address_type |  address   |
+--------------+--------------+------------+
| Company A    | Billing      | 111 Street |
| Company A    | Shipping     | 111 Street |
| Company B    | Billing      | 222 Street |
| Company B    | Shipping     | 333 street |
| Company B    | Shipping     | 444 street |
+--------------+--------------+------------+

我有一张类似的桌子.
我需要的是帐单地址和送货地址不同的所有公司.

I have a table similar to this.
What i need is All the companies whose billing address and shipping address are different.

注意 - 每家公司只有一个帐单邮寄地址.但它可以有多个送货地址

NOTE - Each company has only ONE billing address. But it can have multiple Shipping addresses

这似乎是一个相当简单的查询，但我无法理解.

This seems like a fairly simple query but I'm not just able to get it.

我的尝试 - 我尝试从 Billing 中减去"所有送货地址，但没有任何输出.不同也无济于事

My attempt - I tried 'subtracting' all the shipping address from Billing but there's just no output. Distinct doesn't help as well

查询:

select company_name 
from tableA 
where address_type='Billing' 
and company_name not in (select to_char(company_name) from tableA where address_type='Shipping');

输出应该是Company B(因为它的帐单和送货地址不同)

Output should be Company B (since it's billing and shipping address is different)

编辑 1:尝试了 Indra 的查询，但它永远运行.没有反应

EDIT 1 : Tried Indra's query but it runs forever. No response

select A.* from company A inner join company B on A.company_Name = B.company_Name
and (A.address_type = 'Billing' and B.address_type = 'Shipping') 
AND A.address <> B.address 

推荐答案

如何使用 join?下面显示了所有不同的对:

How about using a join? The following shows all pairs that are different:

select tb.*, ts.*
from company tb join
     company ts
     on tb.company_name = ts.company_name and
        ts.address_type = 'shipping' and
        tb.address_type = 'billing' and
        ts.address <> tb.address;

如果您只想要不同的公司:

If you just want the companies that are different:

select company_name
from company t
group by company_name
having count(distinct case when t.address_type = 'billing' then address end) = 1 and
       count(distinct case when t.address_type = 'shipping' then address end) = 1 and
       (max(case when t.address_type = 'billing' then address end) <>
        max(case when t.address_type = 'shipping' then address end)
       );

注意:这还会检查是否只有一个不同的帐单邮寄地址.

Note: this also checks that there is only one distinct billing and shipping address.

这篇关于比较同一表的行和列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！