mysql - 如何避免查询中的条件多重 SELECT

Question

我有这个简单的 SQL

SELECT 
(CASE
    WHEN
        (SELECT 
                Name
            FROM
                Customers
            WHERE
                Customers_id = Customer) IS NOT NULL
    THEN
        (SELECT 
                Name
            FROM
                Customers
            WHERE
                Customers_id = Customer)
    ELSE Customer
END) AS Customer,
DateStart,
ProgramsRun.Programs_id,
RunInSeconds,
SwSQL,
SwProgram,
Name AS ProgramName
FROM
ProgramsRun
    LEFT JOIN
Programs ON ProgramsRun.Programs_id = Programs.Programs_id;

我的问题是如何避免创建两个相同的 SELECT 语句:一个作为条件，另一个作为值选择。我知道我可以创建临时变量，但这迫使我使用存储过程或临时表。有没有简单的方法可以避免这种情况？

Answer 1

您可以像这样使用IFNULL:

IFNULL((SELECT 
                Name
            FROM
                Customers
            WHERE
                Customers_id = Customer),Customer)

或者像这样COALESCE:

COALESCE((SELECT 
                Name
            FROM
                Customers
            WHERE
                Customers_id = Customer),Customer)

引用:

• COALESCE(value,...)
• IFNULL(expr1,expr2)