sql文搞不定了.好象是group by的问题.大家帮我看看.

Question

SELECT SUB_RESULT.KJ_KATUDKOM , SUB_RESULT.KB_JITASYA   , SUB_RESULT. NUM ,SUM(SUB_RESULT.CD_OKYAKU)    
FROM 
(
SELECT
A.KJ_KATUDKOM KJ_KATUDKOM ,C.KB_JITASYA KB_JITASYA          ,A.CD_OKYAKU CD_OKYAKU
,MIN(A.CD_ACTITEM)　CD_ACTITEM_MIN ,D.SU_HYOJIJUN SU_HYOJIJUN
,COUNT(1)  NUM
FROM TBTA020G A                  ,TBTA021G B        
        ,TBTW001G C　
        ,TBTA001M D        　
WHERE A.CD_KAISYA = B.CD_KAISYA　
AND  A.CD_KAISYA = C.CD_KAISYA　
AND  A.CD_KAISYA = D.CD_KAISYA　
AND A.CD_BDHANTEN = B.CD_BDHANTEN AND  A.CD_BDHANTEN = D.CD_BDHANTEN AND  A.CD_OKYAKU = C.CD_OKYAKU           AND  A.CD_ACTITEM = B.CD_ACTITEM
AND  A.CD_KIKKAKE = D.CD_KIKKAKE
AND  A.CD_KAISYA = :cdKaisya
AND  A.MJ_SAKUJYO = :mjSakujyo
AND  A.CD_BDHANTEN = :cdBdhanten 
AND  B.DD_KATUDOYM = :ddKatudoym  AND B.CD_KATUDSYI = :cdKatudsyi AND  B.MJ_KURIK = :mjKurik AND  B.MJ_LTERMFOL = :mjLtermfol          AND  B.MJ_SYONIN = :mjSyonin AND  B.MJ_SAKUJYO = :mjSakujyo AND  C.MJ_SAKUJYO = :mjSakujyo
AND  D.MJ_SAKUJYO = :mjSakujyo
GROUP BY A.KJ_KATUDKOM 
,A.CD_OKYAKU
,C.KB_JITASYA          ,D.SU_HYOJIJUN
)　SUB_RESULT
GROUP BY SUB_RESULT.KJ_KATUDKOM 
, SUB_RESULT.KB_JITASYA          ORDER BY SUB_RESULT.SU_HYOJIJUN
,SUB_RESULT.CD_ACTITEM_MIN
上面的sql问总报ORA-00979错误.不知道如何解决.不知道我的sql文错在什么地方?GROUP BY 可以在子查询中用一次后,外部父查询再用一次吗?
请大家帮忙看看,谢谢.

Answer 1

group by 的内容必须是表的列名,不能是别名.

Answer 2

SELECT   sub_result.kj_katudkom, sub_result.kb_jitasya, sub_result.num,
         SUM (sub_result.cd_okyaku)
    FROM (SELECT   a.kj_katudkom kj_katudkom, c.kb_jitasya kb_jitasya,
                   a.cd_okyaku cd_okyaku, MIN (a.cd_actitem) cd_actitem_min,
                   d.su_hyojijun su_hyojijun, COUNT (1) num
              FROM tbta020g a, tbta021g b, tbtw001g c, tbta001m d
             WHERE a.cd_kaisya = b.cd_kaisya
               AND a.cd_kaisya = c.cd_kaisya
               AND a.cd_kaisya = d.cd_kaisya
               AND a.cd_bdhanten = b.cd_bdhanten
               AND a.cd_bdhanten = d.cd_bdhanten
               AND a.cd_okyaku = c.cd_okyaku
               AND a.cd_actitem = b.cd_actitem
               AND a.cd_kikkake = d.cd_kikkake
               AND a.cd_kaisya = :cdkaisya
               AND a.mj_sakujyo = :mjsakujyo
               AND a.cd_bdhanten = :cdbdhanten
               AND b.dd_katudoym = :ddkatudoym
               AND b.cd_katudsyi = :cdkatudsyi
               AND b.mj_kurik = :mjkurik
               AND b.mj_ltermfol = :mjltermfol
               AND b.mj_syonin = :mjsyonin
               AND b.mj_sakujyo = :mjsakujyo
               AND c.mj_sakujyo = :mjsakujyo
               AND d.mj_sakujyo = :mjsakujyo
          GROUP BY a.kj_katudkom, a.cd_okyaku, c.kb_jitasya, d.su_hyojijun) sub_result
GROUP BY sub_result.kj_katudkom, sub_result.kb_jitasya, sub_result.num
ORDER BY sub_result.su_hyojijun, sub_result.cd_actitem_min

Answer 3

分析长SQL语句，一定要先整好格式！

Answer 4

最后ORDER BY 的项目不在GROUP BY的项目之内。
GROUP BY sub_result.kj_katudkom, sub_result.kb_jitasya, sub_result.num
ORDER BY sub_result.su_hyojijun, sub_result.cd_actitem_min