SQL 比较同一表的行和列

Compare rows and columns of same table

提问by Pirate X

+--------------+--------------+------------+
| 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.

这似乎是一个相当简单的查询，但我不只是能够得到它。

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

我的尝试 - 我尝试从 Billing 中“减去”所有送货地址，但没有输出。Distinct 也无济于事

Query:

询问：

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');

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

输出应该是B 公司（因为它的帐单和送货地址不同）

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

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

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 

回答by Gordon Linoff

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

使用一个join怎么样？下面显示了所有不同的对：

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.

注意：这还会检查是否只有一个不同的帐单和送货地址。

回答by David Isla

In this SQL Fiddle example, you can find other approach with one more company:

在这个SQL Fiddle 示例中，您可以找到另外一家公司的其他方法：

+--------------+--------------+------------+
| 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 |
| Company C    | Shipping     | 555 street |
| Company C    | Shipping     | 666 street |
| Company C    | Billing      | 555 street |
| Company C    | Billing      | 666 street |
+--------------+--------------+------------+

POSTDATA: SQL Fiddle works fine for me. This is all the code:

POSTDATA：SQL Fiddle 对我来说很好用。这是所有的代码：

Schema:

架构：

CREATE TABLE companies
( 
  company_name  VARCHAR2(40),
  address_type  VARCHAR2(40),
  address    VARCHAR2(40)
);

insert into companies values ('Company A','Billing','111 Street' );
insert into companies values ('Company A','Shipping','111 Street' );
insert into companies values ('Company B','Billing','222 Street' );
insert into companies values ('Company B','Shipping','333 street' );
insert into companies values ('Company B','Shipping','444 street');
insert into companies values ('Company C','Billing','555 Street' );
insert into companies values ('Company C','Billing','666 Street' );
insert into companies values ('Company C','Shipping','555 Street' );
insert into companies values ('Company C','Shipping','666 Street' );

Sentece:

句子：

SELECT DISTINCT(comp.company_name) FROM
    (select company_name, address, count(*) regs
    from companies
    group by company_name, address) comp
WHERE MOD(comp.regs,2) = 1;

Result:

结果：

Company B

回答by Indra Prakash Tiwari

Use Below Query

使用下面的查询

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