sql,用COALESCE，NULLIF对空值和null值进行处理

最新推荐文章于 2025-04-02 17:05:26 发布

原创最新推荐文章于 2025-04-02 17:05:26 发布 · 2.5k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#sql #COALESCE #NULLIF

Mysql 同时被 2 个专栏收录

20 篇文章

订阅专栏

sql

8 篇文章

订阅专栏

本文展示了从欧洲销售数据库中筛选价格高于50.00的产品记录，包括产品ID、名称（如果未提供则显示'[productnamenotfound]'）、价格、信用卡名称（如果未提供则显示'[cardnamenotfound]'）、信用卡号及交易日期。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

eusales table schema

id
name
price
card_name
card_number
transaction_date

resultant table schema

id
name
price (greater than 50.00)
card_name
card_number
transaction_date

SQL CODE:

select 
  id,
  COALESCE(NULLIF(name, ''),'[product name not found]') as name,
  price,
  COALESCE(NULLIF(card_name, ''),'[card name not found]') as card_name,
  card_number,
  transaction_date
from 
  eusales
where 
  price > 50

select result:

id  name    price   card_name   card_number transaction_date
2   Intelligent Aluminum Keyboard   88.51   Emmitt Durgan   1234-2121-1221-1211 2011-12-18
9   Aerodynamic Aluminum Shoes  62.82   Rosalinda Von   1212-1221-1121-1234 2012-08-14
10  Rustic Marble Watch 72.35   Owen Jerde PhD  1234-2121-1221-1211 2015-10-09
12  [product name not found]    97.89   Kali Mante  1211-1221-1234-2201 2010-07-19
14  Sleek Rubber Lamp   94.13   Lauryn McClure  1211-1221-1234-2201 2012-12-12
15  Gorgeous Wooden Clock   84.73   Niko Cartwright 1234-2121-1221-1211 2016-01-17
17  Durable Linen Knife 73.95   Robb Ruecker Sr.    1234-2121-1221-1211 2011-02-20