MySQL 行变列，去空，插入

最新推荐文章于 2024-04-18 21:08:27 发布

MyFreeIT

最新推荐文章于 2024-04-18 21:08:27 发布

阅读量404

点赞数 10

分类专栏：数据库文章标签： java mysql 去空

创作源自于MyFreeIT

本文链接：https://blog.csdn.net/MyFreeIT/article/details/135900107

版权

数据库专栏收录该内容

54 篇文章 0 订阅

订阅专栏

本文介绍了如何在MySQL中通过UNION操作，结合COALESCE函数处理行变列，移除空值，实现数据合并，以满足特定的业务需求，如biz_email表的插入操作。

摘要由CSDN通过智能技术生成

MySQL 行变列，去空，插入

INSERT INTO biz_email(be_company_id,be_email_address,be_scope,be_carbon_action,be_in_active)

SELECT custno,To1,2,1,1 FROM `temp_biz_email3`  WHERE COALESCE(To1,"abc") !='abc'

UNION

SELECT custno,To2,2,1,1 FROM `temp_biz_email3`  WHERE COALESCE(To2,"abc") !='abc'

UNION

SELECT custno,To3,2,1,1 FROM `temp_biz_email3`  WHERE COALESCE(To3,"abc") !='abc'

UNION

SELECT custno,To4,2,1,1 FROM `temp_biz_email3`  WHERE COALESCE(To4,"abc") !='abc'

UNION

SELECT custno,To5,2,1,1 FROM `temp_biz_email3`  WHERE COALESCE(To5,"abc") !='abc'

UNION

SELECT custno,To6,2,1,1 FROM `temp_biz_email3`  WHERE COALESCE(To6,"abc") !='abc'

UNION

SELECT custno,To7,2,1,1 FROM `temp_biz_email3`  WHERE COALESCE(To7,"abc") !='abc'

UNION

SELECT custno,To8,2,1,1 FROM `temp_biz_email3`  WHERE COALESCE(To8,"abc") !='abc'

UNION

SELECT custno,To9,2,1,1 FROM `temp_biz_email3`  WHERE COALESCE(To9,"abc") !='abc'

UNION

SELECT custno,To10,2,1,1 FROM `temp_biz_email3`  WHERE COALESCE(To10,"abc") !='abc'