Oracle 行转列并替换逗号字符

最新推荐文章于 2022-05-25 23:56:53 发布

KennySKwan

最新推荐文章于 2022-05-25 23:56:53 发布

阅读量1k

点赞数 3

分类专栏：数据库SQL

本文链接：https://blog.csdn.net/qq_42963855/article/details/88874077

版权

数据库SQL 专栏收录该内容

15 篇文章 0 订阅

订阅专栏

创建会话级临时表
CREATE GLOBAL TEMPORARY TABLE TABLE_test
ON COMMIT PRESERVE ROWS
AS
SELECT statis_date,phonenum,count() as count1 FROM table
where statis_date between ‘20190312’ and ‘20190327’
group by statis_date,phonenum
having count()>200
order by count(*) desc

展现数据
select * from TABLE_test order by statis_date desc

行转列并替换，为‘，’
select statis_date,replace(wm_concat(phonenum),’,’,’’|| chr(39) ||’,’|| chr(39) ||’’) from TABLE_test group by statis_date;

完成需求
SELECT * FROM table_3
where statis_date=‘20190312’
and phonenum in (‘18635690881’,‘02691197808’,‘18537377667’,‘13230875765’,‘15905837620’,‘13910907036’,‘13062624414’,‘13168485024’,‘15567694515’,‘30089214415’,‘18635150624’,‘15665723215’,‘13147352414’,‘15588155520’,‘18547100471’,‘13523219668’,‘13181295730’,‘13268418927’,‘13268416102’,‘13072132354’,‘18764774444’,‘13268482809’,‘13959212689’,‘15620739394’,‘18678008893’,‘15666944066’,‘13191955696’,‘13268405517’,‘18595208811’)
order by phonenum,startup_time