连接-表2中的字段将覆盖表1中的字段 [英] Join - fields in table 2 override those in table 1

问题描述

我有一个产品表，其中存储有主"定价:

I have a products table that stores "master" pricing:

产品
==========.
id
partnum
说明
价格
安装时间

products
========== .
id
partnum
description
price
installtime

经销商可以覆盖标价，设置不同的价格，安装时间等.我的想法是将经销商特定的差异存储在另一个表中:

Dealers can override the list price, setting a different price, install time, etc. My thought was to store dealer-specific differences in another table:

替代
==========.
DealerID
partnum
价格
安装时间

overrides
========== .
dealerID
partnum
price
installtime

当我在数据库中查询经销商的价格时，我需要加入这些表.我需要覆盖表中的值来覆盖产品表中的值.

When I query the db for a dealer's prices, I need to join these tables. I need the values in the overrides table to override those in the products table.

SELECT partnum, price, installtime FROM products  
JOIN overrides ON products.partnum = overrides.partnum  
WHERE dealerID = 123

如所写，这当然会带来错误.关键是我需要覆盖表中的价格(如果存在)而不是产品表中的价格(与安装时相同).我可以使用不同的字段名称，并将逻辑移到PHP层.但是SQL应该能够处理它，对吧?

As written, this would give an error of course. The point is I need the price from the overrides table if one exists instead of the price in the products table (same for instaltime). I could use different field names and move the logic to the PHP layer. But SQL should be able to handle it, right?

推荐答案

使用 与 IFNULL 首先检查替代，如果没有替代，则回退.

Use a LEFT JOIN together with IFNULL to check first the overrides, and fallback if no override exists.

SELECT p.partnum, 
    IFNULL(d.price, p.price) AS price,
    IFNULL(d.installtime, p.installtime) AS installtime
FROM products p 
  LEFT JOIN overrides d ON d.dealerID = 123
      AND p.partnum = d.partnum  

注意:我将WHERE dealerID = 123移到了连接谓词，以检索所有产品以及特定经销商的替代商品.

Note: I moved the WHERE dealerID = 123 to the join predicate, to retrieve all products, and the overrides for a specific dealer.

这篇关于连接-表2中的字段将覆盖表1中的字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！