oracle字符串SQL拼接【用户昵称和统计拼接】

1.要求根据项目状态去统计不同省市区，对应人的待处理业务，只能将数据放到data里面，name只能是纯文字！

也就是string和number的oracle拼接！

select '待提交年度预算'                           name,
       a.CREATE_USER || '|' || count(*) as data,
       XZQ_DM                              x,
       EXTRACT(YEAR FROM SYSDATE)       AS YEAR,
       a.XZQ_DM                         as xzq_code
from T_NDTDCBJG a
where a.XM_ZT like '1%'
group by a.XZQ_DM, a.CREATE_USER;

2.效果如下

3.又上可知我们可以更加自由的定义数据DATA的结构，如：{"userId":"","value":{1}}

select '待提交年度预算'                                                              name,
       '{"userId":"' || a.CREATE_USER || '","value":{' || count(*) || '}}' as data,
       XZQ_DM                                                                 x,
       EXTRACT(YEAR FROM SYSDATE)                                          AS YEAR,
       a.XZQ_DM                                                            as xzq_code
from T_NDTDCBJG a
where a.XM_ZT like '1%'
group by a.XZQ_DM, a.CREATE_USER;

4.效果图