烦人的标量加rownum = 1

根据sql构建表如下

DROP TABLE r;
CREATE TABLE r AS SELECT 1 AS singerid,1 AS songid FROM dual;
DROP TABLE s1;
CREATE TABLE s1 AS SELECT 1 AS areaid,'1' AS  singername,1 AS singerid,1 AS songid FROM dual;
DROP TABLE area;
CREATE TABLE area AS SELECT '1' AS  area,1 AS ID FROM dual;
DROP TABLE b;
CREATE TABLE b AS SELECT 1 AS price,1 AS distributionarea, 1 AS songid, '1' AS  showstatus,1 AS contentid,'6' AS subservicetype FROM dual;
DROP TABLE t;
CREATE TABLE t AS SELECT 1 AS downtime,1 AS sttime,1 AS songid FROM dual;
DROP TABLE s;
CREATE TABLE s AS
SELECT 1 AS songid,
       '1' AS songname,
       '1' AS LANGUAGE,
       1 AS stylelable,
       1 AS publishtime,
       1 AS basicsort,
       0 AS masked
  FROM dual;

原语句如下，标量一大堆，而且居然还有rownum<=1

SELECT s.songid songid,
       s.songname songname,
       s.language,
       s.publishtime songpublishtime,
       s.stylelable songlabel,
       s.basicsort songbasicsort,
       (SELECT to_char(wm_concat(s1.singername))
          FROM r r, s1 s1
         WHERE r.singerid = s1.singerid
           AND r.songid = s.songid
           AND rownum = 1) singername,
       (SELECT to_char(wm_concat(a.area))
          FROM r r, s1 s1, area a
         WHERE r.singerid = s1.singerid
           AND s1.areaid = a.id(+)
           AND r.songid = s.songid
           AND rownum = 1) singerarea,
       (SELECT contentid
          FROM b b
         WHERE b.songid = s.songid
           AND b.showstatus IN ('1', '3')
           AND b.subservicetype = '6'
           AND rownum = 1) contentid,
       (SELECT contentid
          FROM b b
         WHERE b.songid = s.songid
           AND b.showstatus IN ('1', '3')
           AND b.subservicetype = '8'
           AND rownum = 1) ringcontentid,
       (SELECT contentid
          FROM b b
         WHERE b.songid = s.songid
           AND b.showstatus IN ('1', '3')
           AND b.subservicetype = '1'
           AND rownum = 1) mvcontentid,
       nvl((SELECT SUM(t.downtime)
             FROM t t
            WHERE t.songid = s.songid),
           0) downtime,
       nvl((SELECT SUM(t.sttime)
             FROM t t
            WHERE t.songid = s.songid),
           0) listentime,
       0 datasource
  FROM s s
 WHERE 1 = 1
   AND EXISTS (SELECT 1
          FROM b c
         WHERE c.songid = s.songid
           AND showstatus IN ('1', '3')
           AND rownum = 1)
   AND NOT EXISTS (SELECT 1
          FROM b c
         WHERE c.subservicetype = '8'
           AND c.price = '0'
           AND c.distributionarea != '99'
           AND c.distributionarea IS NOT NULL
           AND s.songid = c.songid)
   AND s.masked = 0   
/

几乎都要看晕了。去掉标量后如下

SELECT s.songid songid,
       s.songname songname,
       s.language,
       s.publishtime songpublishtime,
       s.stylelable songlabel,
       s.basicsort songbasicsort,
       r.singername,
       r.singerarea,
       c.contentid,
       c.ringcontentid AS ringcontentid,
       c.mvcontentid AS mvcontentid,
       nvl(t.downtime, 0) downtime,
       nvl(t.listentime, 0) listentime,
       0 datasource
  FROM s s
 INNER JOIN (SELECT c.songid,
                    MAX(CASE
                          WHEN c.subservicetype = '1' THEN
                           contentid
                        END) AS mvcontentid,
                    MAX(CASE
                          WHEN c.subservicetype = '8' THEN
                           contentid
                        END) AS ringcontentid,
                    MAX(CASE
                          WHEN c.subservicetype = '6' THEN
                           contentid
                        END) AS contentid
               FROM b c
              WHERE showstatus IN ('1', '3')
              GROUP BY c.songid) c
    ON (c.songid = s.songid)
  LEFT JOIN (SELECT t.songid,
                    SUM(t.downtime) AS downtime,
                    SUM(t.sttime) AS listentime
               FROM t t
              GROUP BY t.songid) t
    ON (t.songid = s.songid)
  LEFT JOIN (SELECT r.songid,
                    to_char(MAX(a.area)) AS singerarea,
                    to_char(MAX(s1.singername)) AS singername
               FROM r r, s1 s1, area a
              WHERE r.singerid = s1.singerid
                AND s1.areaid = a.id(+)
              GROUP BY r.songid) r
    ON (r.songid = s.songid)
 WHERE 1 = 1
   AND NOT EXISTS (SELECT 1
          FROM b c
         WHERE c.subservicetype = '8'
           AND c.price = '0'
           AND c.distributionarea != '99'
           AND c.distributionarea IS NOT NULL
           AND s.songid = c.songid)
   AND s.masked = 0