MySQL:如何使用COALESCE [英] MySQL: how to use COALESCE

问题描述

说我有下表:

TABLE: product
===============================================================================
| product_id | language_id | name           | description                     |
===============================================================================
| 1          | 1           | Widget 1       | Really nice widget. Buy it now! |
-------------------------------------------------------------------------------
| 1          | 2           | Lorem  1       |                                 |
-------------------------------------------------------------------------------

我该如何查询，以便尝试给我name和description，其中language_id = 2，但是如果列包含NULL则退回到language_id = 1?

How do I query this such that it tries to give me the name and description where language_id = 2, but fall back to language_id = 1 if the column contains a NULL?

在上面的示例中，我应该为name获取Lorem 1，为description获取Really nice widget. Buy it now!.

In the above example, I should get Lorem 1 for name and Really nice widget. Buy it now! for description.

推荐答案

如何?

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;

其中:

• @pid:当前产品ID
• @lid:当前语言ID
• name和/或description的值可以为空
• language_id = 2个项目不存在

• @pid: current product id
• @lid: current language id
• Values for name and/or description could be null
• language_id = 2 item could not exist

这篇关于MySQL:如何使用COALESCE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！