MySQL：如何使用 COALESCE
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5608740/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL: how to use COALESCE
提问by StackOverflowNewbie
Say I have the following table:
假设我有下表：
TABLE: product
===============================================================================
| product_id | language_id | name | description |
===============================================================================
| 1 | 1 | Widget 1 | Really nice widget. Buy it now! |
-------------------------------------------------------------------------------
| 1 | 2 | Lorem 1 | |
-------------------------------------------------------------------------------
How do I query this such that it tries to give me the nameand descriptionwhere language_id= 2, but fall back to language_id= 1 if the column contains a NULL?
我如何查询它以便它尝试给我name和descriptionwhere language_id= 2，但language_id如果该列包含 NULL，则回退到= 1？
In the above example, I should get Lorem 1for nameand Really nice widget. Buy it now!for description.
在上面的例子中，我应该得到Lorem 1forname和Really nice widget. Buy it now!for description。
采纳答案by Francisco Alvarado
How about this?
这个怎么样？
SET @pid := 1, @lid := 2;
SELECT
COALESCE(name,(
SELECT name
FROM product
WHERE product_id = @pid AND description IS NOT NULL
LIMIT 1
)) name,
COALESCE(description,(
SELECT description
FROM product
WHERE product_id = @pid AND description IS NOT NULL
LIMIT 1
)) description
FROM product
WHERE product_id = @pid
AND (language_id = @lid
OR language_id = 1)
ORDER BY language_id DESC
LIMIT 1;
where:
在哪里：
@pid: current product id@lid: current language id- Values for
nameand/ordescriptioncould be null language_id= 2 item could not exist
@pid: 当前产品编号@lid: 当前语言 IDname和/或的值description可以为空language_id= 2 项不存在
回答by Richard Schneider
select name, description from product
where product_id = @pid
and name is not null
and description is not null
and (language_id = @lang or language_id = 1)
order by language_id desc
where @pidis the current product id and @langis the current language id.
其中@pid是当前产品 ID，@lang是当前语言 ID。
The first row returned will contain the current name and description.
返回的第一行将包含当前名称和描述。
This assumes that the row language_id = 1will NOT contain NULLin name or description.
这假设该行language_id = 1不包含NULL名称或描述。
回答by Ronnis
select p2.product_id
,coalesce(p2.name, p1.name, 'No name') as name
,coalesce(p2.description, p1.description, 'No description') as description
from product p2
left join product p1 on(
p1.product_id = p2.product_id
and p1.language_id = 1
)
where p2.product_id = 1
and p2.language_id = 2;
Edit1:
The above query assumes that the language=2 row exist but that the name/descr may be null.
Edit1：
上面的查询假设 language=2 行存在，但 name/descr 可能为 null。
Edit 2.
I just remembered someone asked a similar question recently. And then I discovered it was you. You need to separate the products from the translations. That is what is making this query hard to write. Thomas answermakes it easy to do what you want.
编辑 2.
我只记得最近有人问过类似的问题。然后我发现是你。您需要将产品与翻译分开。这就是使这个查询难以编写的原因。托马斯的回答让你可以轻松地做你想做的事。
回答by user2034889
Assumption: There is an entry for each product with language = 1The code below is just simple sql to retrieve what you want.
假设：每个产品都有一个条目，language = 1下面的代码只是简单的 sql 来检索您想要的内容。
Another topic is if you want the behaviour you requested.. because you can have mixed languages between nameand description. I would design it differently, if one of the two fields is empty I would default back to the main language (1).
另一个主题是如果你想要你请求的行为......因为你可以在name和之间使用混合语言description。我会以不同的方式设计它，如果两个字段之一为空，我将默认返回主语言 (1)。
select p.product_id,
coalesce(pl.name, p.name, 'No name') as name,
coalesce(pl.description, p.description, 'No description') as description
from product p
left join product pl on (pl.product_id = p.product_id and pl.language_id = :language_id)
where p.product_id = :product_id and p.language_id = 1
回答by Chand Priyankara
Here is an example of using with SQL Update:
以下是与 SQL 更新一起使用的示例：
Its like equivalent to Oracle's NVL. You can use it like below in a prepared statement using parameters
它相当于 Oracle 的 NVL。您可以使用参数在准备好的语句中像下面一样使用它
UPDATE
tbl_cccustomerinfo
SET
customerAddress = COALESCE(?,customerAddress),
customerName = COALESCE(?,customerName),
description = COALESCE(?,description)
WHERE
contactNumber=?
