SQL如果有值，则从Table2中选择，否则从Table1中选择 [英] SQL select from Table2 if it has a value, else from Table1

问题描述

我有两个表，其中包含以下数据:

I have two tables with the following data:

Table1:

PRODUCT | PRICE | NEWPRICE

Table2:

PRODUCT | PRICE | NEWPRICE

Table1已完成.已创建Table2来更新Table1. Table1包含去年的数据，而Table2是最新的.但是Table2仅包含今年具有NEWPRICE的PRODUCT.

Table1 is complete. Table2 has been created to update Table1. Table1 contains data from last year, while Table2 is up to date. But Table2 only contains PRODUCT's which have a NEWPRICE this year.

现在我要做的是:

SELECT 
    Table1.PRODUCT, Table1.PRICE, 
    **(IF Table2.NEWPRICE -> select this, ELSE Table1.NEWPRICE)**

有没有办法做到这一点?也许使用某种JOIN?

Is there any way to do this? Maybe with some kind of JOIN?

PS:我知道表的创建不是很明智，但是我现在必须与它们一起工作，因为我必须继续其他人的工作.

PS: I know that the tables were not created wisely, but I have to work with them now, because I have to continue the work of someone else >.<

推荐答案

我相信这是您想要的:

SELECT 
  Table1.PRODUCT, Table1.PRICE, COALESCE(Table2.NEWPRICE, Table1.NEWPRICE) AS "New Price"
FROM Table1 LEFT JOIN Table2 ON Table1.Product = Table2.Product

left join将获取Table1中的所有行，并获取Table2中的匹配行，如果Table2中没有匹配的行，则Table2.NEWPRICE将为NULL，而COALESCE将获取Table1.NEWPRICE值.

The left join will take all rows from Table1 and the matching rows from Table2 and if there is no matching row from Table2 then the Table2.NEWPRICE will be NULL and the COALESCE will get the Table1.NEWPRICE value.

这假定表1中存在所有产品.如果不是这种情况，则可以使用两个表的并集构建所有产品的源，并将其用作两个左联接的左侧，如下所示:

This assumes that all products exists in Table1. If that isn't the case you can build a source of all products with a union of the two tables and use that as the left side for two left joins like this:

select 
  all_products.product, 
  Table1.PRICE, 
  coalesce(Table2.Newprice, Table1.Newprice) AS "New Price"
from (select product from table1 union all select product from table2) all_products
left join Table1 on all_products.product = Table1.product
left join Table2 on all_products.product = Table2.product

这篇关于SQL如果有值，则从Table2中选择，否则从Table1中选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！