首先说明，字符串肯定没有过长，最多估计都不到100，但递归查询时就是提示错误，见图，代码如下：with jichu as (select shfzhm,yyxmmc,tcje,fyfssj,row_number() over(partition by shfzhm order by fyfssj) as rn from tanghua where ybxmmc like '%糖化血红蛋白%') ,
digui (shfzhm,yyxmmc,tcje,fyfssj,rn,所有日期) as (
select jichu.*,to_char(jichu.fyfssj,'yyyy-mm-dd') as 所有日期 from jichu where rn=1
union all
select jichu.shfzhm,jichu.yyxmmc,jichu.tcje,jichu.fyfssj,jichu.rn, (to_char(jichu.fyfssj,'yyyy-mm-dd') || '>>' ||所有日期) as 所有日期
from jichu join digui on jichu.shfzhm=digui.shfzhm and jichu.rn=digui.rn+1
)
select * from digui
解决代码:with jichu as (select shfzhm,yyxmmc,tcje,fyfssj,row_number() over(partition by shfzhm order by fyfssj) as rn from tanghua where ybxmmc like '%糖化血红蛋白%') ,
digui (shfzhm,yyxmmc,tcje,fyfssj,rn,所有日期) as (
select jichu.*,cast(to_char(jichu.fyfssj,'yyyy-mm-dd') as varchar2(4000)) as 所有日期 from jichu where rn=1
union all
select jichu.shfzhm,jichu.yyxmmc,jichu.tcje,jichu.fyfssj,jichu.rn, (to_char(jichu.fyfssj,'yyyy-mm-dd') || '>>' ||所有日期) as 所有日期
from jichu join digui on jichu.shfzhm=digui.shfzhm and jichu.rn=digui.rn+1
)
select * from digui order by shfzhm,fyfssj
