sql文搞不定了.好象是group by的问题.大家帮我看看.
SELECTSUB_RESULT.KJ_KATUDKOM, SUB_RESULT.KB_JITASYA , SUB_RESULT. NUM,SUM(SUB_RESULT.CD_OKYAKU)
FROM
(
SELECT
A.KJ_KATUDKOMKJ_KATUDKOM,C.KB_JITASYAKB_JITASYA ,A.CD_OKYAKUCD_OKYAKU
,MIN(A.CD_ACTITEM) CD_ACTITEM_MIN,D.SU_HYOJIJUNSU_HYOJIJUN
,COUNT(1) NUM
FROMTBTA020GA ,TBTA021G B
,TBTW001GC
,TBTA001MD
WHEREA.CD_KAISYA = B.CD_KAISYA
AND A.CD_KAISYA = C.CD_KAISYA
AND A.CD_KAISYA = D.CD_KAISYA
ANDA.CD_BDHANTEN = B.CD_BDHANTENAND A.CD_BDHANTEN = D.CD_BDHANTENAND 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 ANDB.CD_KATUDSYI = :cdKatudsyiAND B.MJ_KURIK = :mjKurikAND B.MJ_LTERMFOL = :mjLtermfol AND B.MJ_SYONIN = :mjSyoninAND B.MJ_SAKUJYO = :mjSakujyoAND C.MJ_SAKUJYO = :mjSakujyo
AND D.MJ_SAKUJYO = :mjSakujyo
GROUP BYA.KJ_KATUDKOM
,A.CD_OKYAKU
,C.KB_JITASYA ,D.SU_HYOJIJUN
) SUB_RESULT
GROUP BYSUB_RESULT.KJ_KATUDKOM
, SUB_RESULT.KB_JITASYA ORDER BYSUB_RESULT.SU_HYOJIJUN
,SUB_RESULT.CD_ACTITEM_MIN
上面的sql问总报ORA-00979错误.不知道如何解决.不知道我的sql文错在什么地方?GROUP BY可以在子查询中用一次后,外部父查询再用一次吗?
请大家帮忙看看,谢谢.
[解决办法]
group by 的内容必须是表的列名,不能是别名.
[解决办法]
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
[解决办法]
最后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
