困扰一天了...在线等ORA-00918的问题未明确定义列问题

Question

select  r1.city_code,r1.city_name city_name,r3.sumall sum,r2.* from(SELECT f.district_code CITY_CODE,f.district_name CITY_NAME FROM tb_discode f where f.region_code=01 ) r1,( select c.district_code city_code  ,  sum(decode(c.brand_code,10001,1,0)*decode(c.style_code,10018,1,0)) a10001_10018  ,  sum(decode(c.brand_code,10001,1,0)*decode(c.style_code,10014,1,0)) a10001_10014  ,  sum(decode(c.brand_code,10002,1,0)*decode(c.style_code,10041,1,0)) a10002_10041  ,  sum(decode(c.brand_code,10000,1,0)*decode(c.style_code,10032,1,0)) a10000_10032  ,  sum(decode(c.brand_code,10002,1,0)*decode(c.style_code,10041,1,0)) a10002_10041  ,  sum(decode(c.brand_code,10001,1,0)*decode(c.style_code,10057,1,0)) a10001_10057  ,  sum(decode(c.brand_code,10001,1,0)*decode(c.style_code,10011,1,0)) a10001_10011  ,  sum(decode(c.brand_code,10001,1,0)*decode(c.style_code,10011,1,0)) a10001_10011  ,  sum(decode(c.brand_code,10001,1,0)*decode(c.style_code,10018,1,0)) a10001_10018  ,  sum(decode(c.brand_code,10003,1,0)*decode(c.style_code,10046,1,0)) a10003_10046  ,  sum(decode(c.brand_code,10001,1,0)*decode(c.style_code,10014,1,0)) a10001_10014  ,  sum(decode(c.brand_code,10000,1,0)*decode(c.style_code,10022,1,0)) a10000_10022  ,  sum(decode(c.brand_code,10000,1,0)*decode(c.style_code,10032,1,0)) a10000_10032  ,  sum(decode(c.brand_code,10002,1,0)*decode(c.style_code,10041,1,0)) a10002_10041  ,  sum(decode(c.brand_code,10000,1,0)*decode(c.style_code,10032,1,0)) a10000_10032  ,  sum(decode(c.brand_code,10001,1,0)*decode(c.style_code,10018,1,0)) a10001_10018  ,  sum(decode((c.brand_code,10002,1,0)*decode(c.style_code,10043,1,0)) a10002_10043  from tb_mobile_stock c,sphonesalcfg b,tb_sal_imei a   where a.imei=c.imei and b.sale_code = a.sale_code  and c.region_code=01 group by  c.district_code) r2,(select  c.district_code city_code,count(*) sumall from tb_mobile_stock c,sphonesalcfg b,tb_sal_imei a    where a.imei=c.imei and b.sale_code = a.sale_code  and c.region_code=01 group by c.district_code ) r3  where r1.city_code=r2.city_code(+) and r1.city_code=r3.city_code(+) and r2.city_code=r3.city_code 
//是不是我指定的重复列别名相同出错？？请大侠指点...着急中

Answer 1

你的r2子查询的最后一个sum多了一个左括号。另外  a10001_10011 多写了一行!!sum(decode((c.brand_code,10002,1,0)*decode(c.style_code,10043,1,0)) a10002_10043像这么长的语句最好用格式化工具格式化一下!!/* Formatted on 2006/05/07 09:55 (Formatter Plus v4.8.0) */SELECT r1.city_code, r1.city_name city_name, r3.sumall SUM, r2.*
  FROM (SELECT f.district_code city_code, f.district_name city_name
          FROM tb_discode f
         WHERE f.region_code = 01) r1,
       (SELECT   c.district_code city_code,
                 SUM (  DECODE (c.brand_code, 10001, 1, 0)
                      * DECODE (c.style_code, 10018, 1, 0)
                     ) a10001_10018,
                 SUM (  DECODE (c.brand_code, 10001, 1, 0)
                      * DECODE (c.style_code, 10014, 1, 0)
                     ) a10001_10014,
                 SUM (  DECODE (c.brand_code, 10002, 1, 0)
                      * DECODE (c.style_code, 10041, 1, 0)
                     ) a10002_10041,
                 SUM (  DECODE (c.brand_code, 10000, 1, 0)
                      * DECODE (c.style_code, 10032, 1, 0)
                     ) a10000_10032,
                 SUM (  DECODE (c.brand_code, 10002, 1, 0)
                      * DECODE (c.style_code, 10041, 1, 0)
                     ) a10002_10041,
                 SUM (  DECODE (c.brand_code, 10001, 1, 0)
                      * DECODE (c.style_code, 10057, 1, 0)
                     ) a10001_10057,
                 SUM (  DECODE (c.brand_code, 10001, 1, 0)
                      * DECODE (c.style_code, 10011, 1, 0)
                     ) a10001_10011,
                 SUM (  DECODE (c.brand_code, 10001, 1, 0)
                      * DECODE (c.style_code, 10018, 1, 0)
                     ) a10001_10018,
                 SUM (  DECODE (c.brand_code, 10003, 1, 0)
                      * DECODE (c.style_code, 10046, 1, 0)
                     ) a10003_10046,
                 SUM (  DECODE (c.brand_code, 10001, 1, 0)
                      * DECODE (c.style_code, 10014, 1, 0)
                     ) a10001_10014,
                 SUM (  DECODE (c.brand_code, 10000, 1, 0)
                      * DECODE (c.style_code, 10022, 1, 0)
                     ) a10000_10022,
                 SUM (  DECODE (c.brand_code, 10000, 1, 0)
                      * DECODE (c.style_code, 10032, 1, 0)
                     ) a10000_10032,
                 SUM (  DECODE (c.brand_code, 10002, 1, 0)
                      * DECODE (c.style_code, 10041, 1, 0)
                     ) a10002_10041,
                 SUM (  DECODE (c.brand_code, 10000, 1, 0)
                      * DECODE (c.style_code, 10032, 1, 0)
                     ) a10000_10032,
                 SUM (  DECODE (c.brand_code, 10001, 1, 0)
                      * DECODE (c.style_code, 10018, 1, 0)
                     ) a10001_10018,
                 SUM (  DECODE (c.brand_code, 10002, 1, 0)
                      * DECODE (c.style_code, 10043, 1, 0)
                     ) a10002_10043
            FROM tb_mobile_stock c, sphonesalcfg b, tb_sal_imei a
           WHERE a.imei = c.imei
             AND b.sale_code = a.sale_code
             AND c.region_code = 01
        GROUP BY c.district_code) r2,
       (SELECT   c.district_code city_code, COUNT (*) sumall
            FROM tb_mobile_stock c, sphonesalcfg b, tb_sal_imei a
           WHERE a.imei = c.imei
             AND b.sale_code = a.sale_code
             AND c.region_code = 01
        GROUP BY c.district_code) r3
 WHERE r1.city_code = r2.city_code(+) AND r1.city_code = r3.city_code(+)
       AND r2.city_code = r3.city_code