sql coalesce函数 (全网最详细解读案例)

深藏阁楼爱情的钟 2024-03-17 12:32 31阅读 0赞

**目录**

一、函数COALESCE

二、解读

三、使用

1.0版本

2.0版本——行转列中会用到coalesce()

案例sql

--------------------

### 一、函数COALESCE ###

COALESCE(expression\_1, expression\_2, ...,expression\_n)

### 二、解读 ###

**coalesce( f1, f2, f3, ... fn)**

coalesce用于：f1有值，返回f1，若为null，则看f2，f2有值，则返回f2，若为null，继续往后。以此类推。

简单说就是：**f1若为null，则取f2，f2若为null，则取f3**，以此类推。

### 三、使用 ###

#### 1.0版本 ####

select coalesce(start_time,end_time,'2023-01-01') from t1

如果start\_time有值，则取的是start\_time的值，如果start\_time为null，则取end\_time，如果end\_time也为null，则返回2023-01-01

#### 2.0版本——行转列中会用到coalesce() ####

例如：一个单号下，有多条数据，状态值不一样。即 group by id,status;，一个id下有多种status。现在希望：这个id下，如果status的值既有 状态1 也有 状态2，则使用 状态1，如果没有 状态1 只有 状态2，就使用状态2。但由于是多行数据，行与行的对比很复杂。

先进行 **行转列**，将这个id下的多行数据转成一行。status的值作为不同的字段保存。

sql如下：

select id,
    max((case status when 'input' then data end )) as status_input,
    max((case status when 'output' then data end)) as status_output
    
    from t1
    group by id

结果大致如下：没有值的就为null

factory|start_date|input_value|auto_value|
    -------+----------+-----------+----------+
    t1     |2023-06-01|   15.23152|          |
    t1     |2023-06-02|   16.23152|  17.23152|
    t1     |2023-06-03|   19.23152|  18.23152|
    t1     |2023-06-04|           |  20.23152|
    t1     |2023-06-05|           |  43.46304|
    t1     |2023-06-06|           |  15.23152|

再对其使用 coalesce() ，如下

select id,
    coalesce(max((case status when 'input' then data else null end )),max((case status when 'output' then data else null end)),'') as status
    from t1
    group by id

意思是：如果 input 有值，就使用 input 那行的 data，如果 input 为 null，就使用 output 那行的data。

结果大致如下：

factory|start_date|value   |
    -------+----------+--------+
    t1     |2023-06-01|15.23152|
    t1     |2023-06-02|16.23152|
    t1     |2023-06-03|19.23152|
    t1     |2023-06-04|20.23152|
    t1     |2023-06-05|43.46304|
    t1     |2023-06-06|15.23152|

具体的案例请看这篇文章：

[sql 行转列 case when (简洁版) —— group by 行与行比较【mysql】][sql _ case when _ _ group by _mysql]

### 案例sql ###

文章到此已结束，下面是案例测试需要用到的sql。

需求大致为：订单状态流转表中，存了该订单的被操作过的多种状态，待付款，已付款，取消订单，确认收货，退款。

现在，要选出一个订单当前处于某重要节点的时间。例如：如果有确认收货，则使用确认收货的时间，如果没有，则使用取消订单的时间，如果没有，则使用待付款的时间。

建表

CREATE TABLE `order_status` (
      `order_no` varchar(64) NOT NULL COMMENT '订单号',
      `status` varchar(20) DEFAULT NULL COMMENT '订单状态',
      `update_date` datetime DEFAULT NULL COMMENT '修改时间'
    )comment '订单明细'

数据

INSERT INTO order_status(order_no, status, update_date)VALUES
    ('1673669924273856513', '待付款',    '2023-06-01 06:21:02'),
    ('1673669924273856513', '取消订单',  '2023-06-01 06:22:02'),
    ('1673669924319993859', '待付款',    '2023-06-01 06:23:02'),
    ('1673669924319993859', '已付款',    '2023-06-01 06:24:02'),
    ('1673669924319993859', '退款',      '2023-06-01 06:25:02'),
    ('1673669924319993859', '取消订单',  '2023-06-01 06:26:02'),
    ('1673669924319993863', '待付款',    '2023-06-01 06:27:02'),
    ('1673669924319993863', '已付款',    '2023-06-01 06:28:02'),
    ('1673669924319993863', '确认收货',  '2023-06-01 06:29:02');

#### 1.0版 ####

查询sql

先将行转列，将一个订单的多条数据转成一条。创建不同的字段来存储需要的值(这里需要的值是时间)，这些字段对应不同的status的值，存相应的时间。

select 
    order_no,
    max(case when status='确认收货' then update_date end) as received_time,
    max(case when status='取消订单' then update_date end) as cancel_time,
    max(case when status='待付款' then update_date end) as nonpay_time
    
    from order_status
    group by order_no

结果：没有值的为null

order_no           |received_time      |cancel_time        |nonpay_time        |
    -------------------+-------------------+-------------------+-------------------+
    1673669924273856513|                   |2023-06-01 06:22:02|2023-06-01 06:21:02|
    1673669924319993859|                   |2023-06-01 06:26:02|2023-06-01 06:23:02|
    1673669924319993863|2023-06-01 06:29:02|                   |2023-06-01 06:27:02|

#### 2.0版 ####

查询sql

在1.0基础上使用 coalesce，如果确认收货的时间为null，则使用取消订单的时间，如果取消订单的时间为null，则使用待付款，待付款也为null，则给个默认值1900-01-01

select 
    order_no,
    COALESCE (max(case when status='确认收货' then update_date end),
    		  max(case when status='取消订单' then update_date end),
    		  max(case when status='待付款' then update_date end),
    		  '1900-01-01'
    		  ) as update_date
    from order_status
    group by order_no

结果：

order_no           |update_date        |
    -------------------+-------------------+
    1673669924273856513|2023-06-01 06:22:02|
    1673669924319993859|2023-06-01 06:26:02|
    1673669924319993863|2023-06-01 06:29:02|

====================分界线=====================

以下为紫薯布丁

CREATE TABLE \`order\_status\` (  
\`order\_no\` varchar(64) NOT NULL COMMENT '订单号',  
\`status\` varchar(20) DEFAULT NULL COMMENT '订单状态',  
\`update\_date\` datetime DEFAULT NULL COMMENT '修改时间'  
)comment '订单明细'

INSERT INTO order\_status(order\_no, status, update\_date)VALUES  
('1673669924273856513', '待付款', '2023-06-01 06:21:02'),  
('1673669924273856513', '取消订单', '2023-06-01 06:22:02'),  
('1673669924319993859', '待付款', '2023-06-01 06:23:02'),  
('1673669924319993859', '已付款', '2023-06-01 06:24:02'),  
('1673669924319993859', '退款', '2023-06-01 06:25:02'),  
('1673669924319993859', '取消订单', '2023-06-01 06:26:02'),  
('1673669924319993863', '待付款', '2023-06-01 06:27:02'),  
('1673669924319993863', '已付款', '2023-06-01 06:28:02'),  
('1673669924319993863', '确认收货', '2023-06-01 06:29:02');

select  
order\_no,  
max(case when status='确认收货' then update\_date end) as received\_time,  
max(case when status='取消订单' then update\_date end) as cancel\_time,  
max(case when status='待付款' then update\_date end) as nonpay\_time

from order\_status  
group by order\_no

order\_no |received\_time |cancel\_time |nonpay\_time |  
\-------------------+-------------------+-------------------+-------------------+  
1673669924273856513| |2023-06-01 06:22:02|2023-06-01 06:21:02|  
1673669924319993859| |2023-06-01 06:26:02|2023-06-01 06:23:02|  
1673669924319993863|2023-06-01 06:29:02| |2023-06-01 06:27:02|

select  
order\_no,  
COALESCE (max(case when status='确认收货' then update\_date end),  
max(case when status='取消订单' then update\_date end),  
max(case when status='待付款' then update\_date end),  
'1900-01-01'  
) as update\_date  
from order\_status  
group by order\_no

order\_no |update\_date |  
\-------------------+-------------------+  
1673669924273856513|2023-06-01 06:22:02|  
1673669924319993859|2023-06-01 06:26:02|  
1673669924319993863|2023-06-01 06:29:02|

[sql _ case when _ _ group by _mysql]: https://globalcoding.blog.csdn.net/article/details/131442054