紧急!!!高手请进!关于一个SQL语句!
select DISTINCT
ZBTSI_D809.WF_TEIGI_ID,
ZBTSI_D809.HAN_SU,
ZBTSI_D809.TEKIYO_KAISHI_YMD,
ZBTSI_D809.TEKIYO_SHURYO_YMD,
ZBTSI_D809.SHINSEISHO_SHUBETSU,
ZBTSI_D809.SHINSEISHO_FORMAT_ID,
ZBTSI_D809.WF_NM_N,
ZBTSI_D809.WF_SETSUMEI,
ZBTSI_D809.SAKUJO_SGN
from ZBTSI_D809,ZBTSI_D810,ZBTSI_D814,ZBTSI_D825,ZBTSI_D826
where
ZBTSI_D809.KAISHA_CD = '000'
and ZBTSI_D809.SHINSEISHO_SHUBETSU = 'S4'
and ZBTSI_D810.KAISHA_CD = ZBTSI_D809.KAISHA_CD
and ZBTSI_D810.WF_TEIGI_ID = ZBTSI_D809.WF_TEIGI_ID
and ZBTSI_D810.HAN_SU = ZBTSI_D809.HAN_SU
and To_Date(ZBTSI_D809.TEKIYO_KAISHI_YMD, 'YYYY-MM-DD') <= SYSDATE
and To_Date(ZBTSI_D809.TEKIYO_SHURYO_YMD, 'YYYY-MM-DD') >= SYSDATE
and ZBTSI_D814.KAISHA_CD = ZBTSI_D809.KAISHA_CD
and ZBTSI_D814.WF_SHAIN_ID = '0000010494344'
and ZBTSI_D825.KAISHA_CD = ZBTSI_D809.KAISHA_CD
and ZBTSI_D825.KAISHA_CD = ZBTSI_D814.KAISHA_CD
and ZBTSI_D826.BUMON_CD = ZBTSI_D825.BUMON_CD
and ZBTSI_D826.WF_SHAIN_ID = ZBTSI_D814.WF_SHAIN_ID
and ZBTSI_D826.KAISHA_CD = ZBTSI_D809.KAISHA_CD
and ZBTSI_D810.BUMON_CD = substrb(ZBTSI_D825.SOSHIKI_CD,22,6)
上面的SQL语句可以正确运行。可后来要求当ZBTSI_D809.WF_TEIGI_ID相同时只取得ZBTSI_D809.HAN_SU最大值所对应的纪录。所以我采用了将纪录按ZBTSI_D809.KAISHA_CD和ZBTSI_D809.WF_TEIGI_ID分组然后取ZBTSI_D809.HAN_SU最大值所对应的纪录的思路。写出如下SQL语句:
select DISTINCT
ZBTSI_D809.WF_TEIGI_ID,
MAX(ZBTSI_D809.HAN_SU),
ZBTSI_D809.TEKIYO_KAISHI_YMD,
ZBTSI_D809.TEKIYO_SHURYO_YMD,
ZBTSI_D809.SHINSEISHO_SHUBETSU,
ZBTSI_D809.SHINSEISHO_FORMAT_ID,
ZBTSI_D809.WF_NM_N,
ZBTSI_D809.WF_SETSUMEI,
ZBTSI_D809.SAKUJO_SGN
from ZBTSI_D809,ZBTSI_D810,ZBTSI_D814,ZBTSI_D825,ZBTSI_D826
where
ZBTSI_D809.KAISHA_CD = '000'
and ZBTSI_D809.SHINSEISHO_SHUBETSU = 'S4'
and ZBTSI_D810.KAISHA_CD = ZBTSI_D809.KAISHA_CD
and ZBTSI_D810.WF_TEIGI_ID = ZBTSI_D809.WF_TEIGI_ID
and ZBTSI_D810.HAN_SU = ZBTSI_D809.HAN_SU
and To_Date(ZBTSI_D809.TEKIYO_KAISHI_YMD, 'YYYY-MM-DD') <= SYSDATE
and To_Date(ZBTSI_D809.TEKIYO_SHURYO_YMD, 'YYYY-MM-DD') >= SYSDATE
and ZBTSI_D814.KAISHA_CD = ZBTSI_D809.KAISHA_CD
and ZBTSI_D814.WF_SHAIN_ID = '0000010494344'
and ZBTSI_D825.KAISHA_CD = ZBTSI_D809.KAISHA_CD
and ZBTSI_D825.KAISHA_CD = ZBTSI_D814.KAISHA_CD
and ZBTSI_D826.BUMON_CD = ZBTSI_D825.BUMON_CD
and ZBTSI_D826.WF_SHAIN_ID = ZBTSI_D814.WF_SHAIN_ID
and ZBTSI_D826.KAISHA_CD = ZBTSI_D809.KAISHA_CD
and ZBTSI_D810.BUMON_CD = substrb(ZBTSI_D825.SOSHIKI_CD,22,6)
GROUP BY ZBTSI_D809.KAISHA_CD, ZBTSI_D809.WF_TEIGI_ID
运行后报ORA_00979:not a GROUP BY expression错误。
请问谁有正确的解决办法啊。不会的兄弟请帮忙顶!谢谢啦!