Sqlite - 常规函数 - COALESCE

技术教程gslnedu2025-03-16 14:22:0516A⁺A^-

在 SQLite 中，COALESCE 函数是一个非常实用的函数，用于返回参数列表中第一个非 NULL 的值。这个函数在处理可能包含 NULL 值的数据时非常有用，能确保我们得到一个有效的结果。以下是对其使用方法的详细解释及示例。

基本语法

COALESCE(value1, value2, ..., valueN)

value1, value2, ..., valueN：这是一系列的值或表达式，可以是常量、列名、函数调用等。COALESCE 函数会按顺序检查这些参数，返回第一个不为 NULL 的值。如果所有参数都是 NULL，则返回 NULL。

使用示例

1. 处理常量值

-- 示例 1：参数中第一个非 NULL 值为 'Hello'
SELECT COALESCE(NULL, 'Hello', 'World');
-- 输出结果为 'Hello'，因为函数按顺序检查，'Hello' 是第一个非 NULL 值

-- 示例 2：所有参数都是 NULL
SELECT COALESCE(NULL, NULL, NULL);
-- 输出结果为 NULL，因为所有参数都是 NULL

2. 在表查询中使用COALESCE函数

假设我们有一个名为 employees 的表，包含 employee_id、name 和 nickname 三列，其中 nickname 列可能存在 NULL 值。我们希望在查询时，如果 nickname 为 NULL，则显示 name 作为替代。

-- 创建示例表
CREATE TABLE employees (
    employee_id INTEGER PRIMARY KEY,
    name TEXT,
    nickname TEXT
);

-- 插入示例数据
INSERT INTO employees (name, nickname) VALUES ('Alice', 'Ali');
INSERT INTO employees (name, nickname) VALUES ('Bob', NULL);

-- 使用 COALESCE 函数查询数据
SELECT 
    employee_id,
    name,
    nickname,
    COALESCE(nickname, name) AS display_name
FROM 
    employees;

在上述查询中，对于 nickname 不为 NULL 的员工（如 'Alice'），display_name 列将显示 nickname（即 'Ali'）；对于 nickname 为 NULL 的员工（如 'Bob'），display_name 列将显示 name（即 'Bob'）。

3. 结合其他函数使用COALESCE函数

COALESCE 函数可以与其他 SQL 函数结合使用。例如，我们可以结合 LENGTH 函数计算第一个非 NULL 值的长度。

SELECT LENGTH(COALESCE(NULL, 'SQLite', 'Database'));

首先，COALESCE 函数返回第一个非 NULL 值 'SQLite'，然后 LENGTH 函数计算 'SQLite' 的长度，结果为 6。

4. 处理多列数据

假设我们有一个表 products，包含 price、discounted_price 和 final_price 三列，其中 discounted_price 可能为 NULL。我们希望 final_price 列显示 discounted_price（如果不为 NULL），否则显示 price。

-- 创建示例表
CREATE TABLE products (
    product_id INTEGER PRIMARY KEY,
    price REAL,
    discounted_price REAL
);

-- 插入示例数据
INSERT INTO products (price, discounted_price) VALUES (100, 80);
INSERT INTO products (price, discounted_price) VALUES (200, NULL);

-- 使用 COALESCE 函数计算 final_price
SELECT 
    product_id,
    price,
    discounted_price,
    COALESCE(discounted_price, price) AS final_price
FROM 
    products;

在这个查询中，对于 discounted_price 不为 NULL 的产品，final_price 列将显示 discounted_price；对于 discounted_price 为 NULL 的产品，final_price 列将显示 price。