存在不匹配的记录时联接两个表

问题描述

我正在尝试使用1条sql语句来产生所需的结果.我有两个名为，订单和客户的表，并尝试使用这样的查询

I am trying to use 1 sql statement to produce the result I want.I got 2 tables named , order and customers, and tried to use a query like this

select a.*, b.customers_name
from order a, customers b
where a.customers_id=b.customers_id
order by b.customers_name;

我的问题是订单表中有一个伪造的customer_id，如果customers_id = 0，那么customer_name =内部"，该用户在cumstomers表中不存在.在加入这家公司之前，这种方式已经被使用过，所以我根本无法修改表格.

My problem is there is a fake customers_id in order table, if customers_id=0 thencustomers_name='In House' which does not exist in cumstomers table.It's been used like this way before I joined this company so I can not modify the table at all.

是否可以显示结果?所有来自customers_name的订单表中的订单，如果customers_id = 0(< = customers表中没有匹配记录)，则customers_name =内部"，则输出应该由customers_name进行订购.

Is there way to display the result?All order from order table with customers_name and if customers_id=0 (<= no match record in customers table) then customers_name='In House') and output should be ordered by customers_name.

推荐答案

select a.*,
        COALESCE(b.customers_name, 'In House') as customers_name
from
    order a LEFT JOIN customers b ON a.customers_id=b.customers_id
order by
    customers_name;

或

select a.*,
         CASE
           WHEN a.customers_id = 0 THEN 'In House'
           WHEN b.customers_name IS NULL THEN 'Unknown'
           ELSE b.customers_name
         END as customers_name
from
    order a LEFT JOIN customers b ON a.customers_id=b.customers_id
order by
    customers_name;

无论哪种方式，为了清楚起见，都使用显式的JOIN.

Either way, use an explicit JOIN for clarity.

第一个为任何丢失的客户添加内部"，第二个通过在customerid不为0时添加Unknown处理丢失的客户

The first one adds "in house" for any missing customers, the second one deals with missing customers by adding Unknown if customerid is not 0

这篇关于存在不匹配的记录时联接两个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！