sql语句 IF函数的用法

最新推荐文章于 2025-06-18 18:05:39 发布

原创最新推荐文章于 2025-06-18 18:05:39 发布 · 4.1k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#sql #if

本文介绍了一种使用SQL进行复杂数据汇总的方法，通过不同方式联接多个表来获取所需数据，并利用IF语句简化查询过程。

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

一般的写法

a.date,a.total,b.manual,c.follow,d.robability（需要查询的字段）

SELECT a.date,a.total,b.manual,c.follow,d.robability FROM

(SELECT SUBSTRING(submit_time,1,7) date,SUM(spread_profits) total FROM t_table
WHERE account_id='2' GROUP BY SUBSTRING(submit_time,1,7)) a
LEFT JOIN
(SELECT SUBSTRING(submit_time,1,7) date,account_id,SUM(spread_profits) manual FROM t_table
WHERE account_id='2' AND follow_account_id IS NULL GROUP BY SUBSTRING(submit_time,1,7)) b
ON a.date = b.date
LEFT JOIN
(SELECT SUBSTRING(submit_time,1,7) date,SUM(spread_profits) follow FROM t_table
WHERE account_id='2' AND follow_account_id >0 AND order_number NOT LIKE 'g%' GROUP BY SUBSTRING(submit_time,1,7)) c
ON b.date = c.date
LEFT JOIN
(SELECT SUBSTRING(submit_time,1,7) date,SUM(spread_profits) robability FROM t_table
WHERE account_id='2' AND follow_account_id >0 AND order_number LIKE 'g%' GROUP BY SUBSTRING(submit_time,1,7)) d
ON c.date = d.date;

if 的写法

if(查询所需的条件)

select SUBSTRING(submit_time,1,7) as date ,sum(spread_profits) total ,
if(follow_account_id IS NULL,SUM(spread_profits),0) as manual,
if(follow_account_id >0 AND order_number NOT LIKE 'g%',SUM(spread_profits),0) as follow,
if(follow_account_id >0 AND order_number LIKE 'g%',SUM(spread_profits),0) as robability from t_table
WHERE account_id='2' AND submit_time BETWEEN begin AND end GROUP BY SUBSTRING(submit_time,1,7));