求一个单表统计查询的sql

Question

试试这个函数，但是有字符长度的限制
wm_concat 

Answer 1

SELECT A.sjly, listagg(A.SJYSYL_AVG, ' , ') WITHIN GROUP(ORDER BY A.sjly)  AS syl
  FROM (SELECT sjly, AVG(SJYSYL) AS SJYSYL_AVG, bcsj
          FROM FD_LOAN_INFO_DATA
         GROUP BY sjly, bcsj) A
 GROUP BY A.sjly

Answer 2

select sjly,avg(nvl(syl,0)) from FD_LOAN_INFO_DATA group by sjly

Answer 3

select sjly,sjysyl from 
(select sjly,to_char(wm_concat(sjysyl)over(partition by sjly order by sjly,bcsj))sjysyl,row_number()over(partition by sjly order by sjly,bcsj)rn from 
(
select t2.sjly, decode(t1.sjysyl, null, 0, t1.sjysyl) sjysyl, t2.bcsj
  from (SELECT sjly, to_char(avg(to_number(SJYSYL))) SJYSYL, bcsj
          FROM FD_LOAN_INFO_DATA T1
         group by bcsj, sjly) t1,
       (select *
          from (select distinct bcsj from FD_LOAN_INFO_DATA),
               (select distinct sjly from FD_LOAN_INFO_DATA)) t2
 where t1.bcsj(+) = t2.bcsj
   and t1.sjly(+) = t2.sjly
 order by t2.sjly, t2.bcsj
)) t4,
(select count(distinct bcsj)ct from FD_LOAN_INFO_DATA) t5
where ct=rn

Answer 4

select sjly,sjysyl from 
(select sjly,to_char(wm_concat(sjysyl)over(partition by sjly order by sjly,bcsj))sjysyl,row_number()over(partition by sjly order by sjly,bcsj)rn from 
(
select t2.sjly, decode(t1.sjysyl, null, 0, t1.sjysyl) sjysyl, t2.bcsj
  from (SELECT sjly, to_char(avg(to_number(SJYSYL))) SJYSYL, bcsj
          FROM FD_LOAN_INFO_DATA T1
         group by bcsj, sjly) t1,
       (select *
          from (select distinct bcsj from FD_LOAN_INFO_DATA),
               (select distinct sjly from FD_LOAN_INFO_DATA)) t2
 where t1.bcsj(+) = t2.bcsj
   and t1.sjly(+) = t2.sjly
 order by t2.sjly, t2.bcsj
)) t4,
(select count(distinct bcsj)ct from FD_LOAN_INFO_DATA) t5
where ct=rn在oracle运行   wm_concat  报无效字符

Answer 5

关注楼主  SJYSYL的表结构 与 新增方法中syl 不对应；

Answer 6

select sjly,sjysyl from 
(select sjly,to_char(wm_concat(sjysyl)over(partition by sjly order by sjly,bcsj))sjysyl,row_number()over(partition by sjly order by sjly,bcsj)rn from 
(
select t2.sjly, decode(t1.sjysyl, null, 0, t1.sjysyl) sjysyl, t2.bcsj
  from (SELECT sjly, to_char(avg(to_number(SJYSYL))) SJYSYL, bcsj
          FROM FD_LOAN_INFO_DATA T1
         group by bcsj, sjly) t1,
       (select *
          from (select distinct bcsj from FD_LOAN_INFO_DATA),
               (select distinct sjly from FD_LOAN_INFO_DATA)) t2
 where t1.bcsj(+) = t2.bcsj
   and t1.sjly(+) = t2.sjly
 order by t2.sjly, t2.bcsj
)) t4,
(select count(distinct bcsj)ct from FD_LOAN_INFO_DATA) t5
where ct=rn在oracle运行   wm_concat  报无效字符
select sjly,sjysyl
  from (select sjly, ltrim(SYS_CONNECT_BY_PATH(sjysyl, ','), ',')sjysyl, level rn
          from (select t2.sjly,
                       decode(t1.sjysyl, null, 0, t1.sjysyl) sjysyl,
                       t2.bcsj,
                       row_number() over(partition by t2.sjly order by t2.sjly, t2.bcsj) rn
                  from (SELECT sjly,
                               to_char(avg(to_number(SJYSYL))) SJYSYL,
                               bcsj
                          FROM FD_LOAN_INFO_DATA T1
                         group by bcsj, sjly) t1,
                       (select *
                          from (select distinct bcsj from FD_LOAN_INFO_DATA),
                               (select distinct sjly from FD_LOAN_INFO_DATA)) t2
                 where t1.bcsj(+) = t2.bcsj
                   and t1.sjly(+) = t2.sjly)
        connect by prior rn = rn - 1
               and prior sjly = sjly) t4,
       (select count(distinct bcsj) ct from FD_LOAN_INFO_DATA) t5
 where t4.rn = t5.ct;