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

目录

 一、函数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

文章到此已结束，下面是案例测试需要用到的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|