ORACLE SQL 无聚合函数 和 有聚合函数 左外连 报不是 GROUP BY 表达式的错误

Question

SELECT
*
FROM
(
SELECT
t11.tokuisaki_cd AS tokuisakiCd-- A.得意先
, '1' AS jizenShikyuuHandan -- '1'(事前) AS 事前支給判断
, t11.jizen_shinsei_taishou_fg AS jizenShinseiTaishouFg -- A.事前申請対象フラグ
, t11.jigo_shinsei_taishou_fg AS jigoShinseiTaishouFg -- A.事後申請対象フラグ
, '事前' AS shinsei -- '事前' AS 申請
, t11.kaigo_seikyuu_dv AS kaigoSeikyuuDv -- A.介護請求区分
, t11.uriku_cd AS urikuCd -- A.売区コード
FROM
m_yakusho_shinsei t11 -- 役所申請 A
WHERE
t11.jizen_shinsei_taishou_fg = '1' -- WHERE A.事前申請対象フラグ = '1'(事前申請対象)
AND t11.uriku_cd = 'HB02' -- AND A.売上区分 = 'HB02'(特定福祉)
AND t11.delete_fg = '0' -- AND A.削除フラグ = '0'(通常)
AND t11.kaigo_seikyuu_dv IN ('SHO', 'INI', 'SHH') -- AND A.介護請求区分 IN ('SHO'(償還),'INI'(受領委任),'SHH'(生保（国保90/公費10）)
UNION
SELECT
t11.tokuisaki_cd AS tokuisakiCd -- A.得意先
, '0' AS jizenShikyuuHandan -- '0'(支給) AS 事前支給判断
, t11.jizen_shinsei_taishou_fg AS jizenShinseiTaishouFg -- A.事前申請対象フラグ
, t11.jigo_shinsei_taishou_fg AS jigoShinseiTaishouFg -- A.事後申請対象フラグ
, '支給' AS shinsei -- '支給' AS 申請
, t11.kaigo_seikyuu_dv AS kaigoSeikyuuDv -- A.介護請求区分
, t11.uriku_cd AS urikuCd-- A.売区コード
FROM
m_yakusho_shinsei t11 -- 役所申請 A
WHERE
t11.jigo_shinsei_taishou_fg = '1' -- WHERE A.事後申請対象フラグ = '1'(事後申請対象)
AND t11.uriku_cd = 'HB02' -- AND A.売上区分 = 'HB02'(特定福祉)
AND t11.delete_fg = '0' -- AND A.削除フラグ = '0'(通常)
AND t11.kaigo_seikyuu_dv IN ('SHO', 'INI', 'SHH') -- AND A.介護請求区分 IN ('SHO'(償還),'INI'(受領委任),'SHH'(生保（国保90/公費10）)
) t1 -- 役所申請情報サブクエリ A
INNER JOIN
(
SELECT
t22.moto_uriage_denpyou_no AS denpyouNo -- B.元売上伝票番号 AS 伝票番号
, t21.seikyuu_tokuisaki_cd AS tokuisakiCd -- A.請求得意先コード AS 得意先コード
, t22.kokyaku_cd AS kokyakuCd -- B.顧客コード
, t21.konkai_seikyuu_ac AS seikyuuAc -- A.今回請求額 AS 請求額
, TO_CHAR(t22.uriage_dt, 'yyyy/MM/dd') AS uriageDt -- B.売上日
, '1' AS uriage -- '1' AS 売上
, t21.tantousha_cd AS tantoushaCd -- A.担当者コード
, t25.naibusashizu_tx AS naibusashizuTx -- E.内部指図
, t23.juchuu_no AS juchuuNo -- C.受注番号 AS 受注番号
, t22.uriage_denpyou_no AS uriageDenpyouNo -- A.売上伝票番号
, TO_CHAR(t21.shuturyokuzumi_fg) AS shuturyokuzumiFg -- A.出力済フラグ
, t25.jichitai_futan_rt AS jichitaiFutanRt -- E.自治体負担率
, t21.busho_cd AS uriageBushoCd -- A.部署コード AS 売上部署コード
, t21.seikyuusho_no AS seikyuushoNo -- A.請求書番号
, t28.uriageShuseiFg AS uriageShuseiFg -- H.売上修正フラグ
, t21.version_no AS versionNo -- バージョン番号
FROM
t_tokuisaki_seikyuu t21 -- 得意先請求 A
INNER JOIN (
SELECT
t281.seikyuusho_no -- t281.請求書番号
, MAX(t281.uriage_denpyou_no) AS uriageDenpyouNo -- MAX(t281.売上伝票番号) AS 売上伝票番号
, CASE WHEN COUNT(t281.uriage_denpyou_no) >= 2 -- CASE WHEN COUNT(t281.売上伝票番号) >= 2
THEN '1' -- THEN '1'
ELSE '0' -- ELSE '0'
END AS uriageShuseiFg -- END AS 売上修正フラグ
FROM
t_tokuisaki_seikyuu_uriage t281
WHERE
t281.delete_fg = '0'
GROUP BY
t281.seikyuusho_no
) t28 -- INNER JOIN 得意先請求売上サブクエリ H

ON t21.seikyuusho_no = t28.seikyuusho_no -- ON A.請求書番号 = H.請求書番号
INNER JOIN
t_tokuisaki_seikyuu_uriage t22 -- INNER JOIN 得意先請求売上 B
ON
t28.uriageDenpyouNo = t22.uriage_denpyou_no -- ON H.売上伝票番号 = B.売上伝票番号
AND
t22.delete_fg = '0' -- AND B.削除フラグ = '0'(通常)
INNER JOIN
t_uriage t23 -- INNER JOIN 売上 C
ON
t22.uriage_denpyou_no = t23.uriage_denpyou_no -- ON B.売上伝票番号 = C.売上伝票番号
AND
t23.delete_fg = '0' -- AND C.削除フラグ = '0'(通常)
INNER JOIN
t_juchuu_shukka t25 -- INNER JOIN 受注出荷 E
ON
t23.juchuu_no = t25.juchuu_no -- ON C.受注番号 = E.受注番号
AND
t25.delete_fg = '0' -- AND E.削除フラグ = '0'(通常)
UNION
SELECT
t21.juchuu_no AS denpyouNo -- A.受注番号 AS 伝票番号
, t21.tokuisaki_cd AS tokuisakiCd -- A.得意先
, t21.kokyaku_cd AS kokyakuCd -- A.顧客コード
, t21.kouhi_tougetu_rental_ac + t21.jikofutan_tougetu_rental_ac + t21.jichitai_tougetu_rental_ac AS seikyuuAc -- A.公費当月レンタル料 + A.自己負担当月レンタル料 + A.自治体当月レンタル料 AS 請求額
, TO_CHAR(t22.katudou_yotei_dt, 'yyyy/MM/dd') AS uriageDt -- B.活動予定日 AS 売上日
, '0' AS uriage -- '0' AS 売上
, t21.uriage_tantousha_cd AS tantoushaCd -- A.売上担当者コード AS 担当者コード
, t21.naibusashizu_tx AS naibusashizuTx -- A.内部指図
, t21.juchuu_no AS juchuuNo -- C.受注番号 AS 受注番号
, '' AS uriageDenpyouNo -- '' AS 売上伝票番号
, '' AS shuturyokuzumiFg -- '' AS 出力済フラグ
, t21.jichitai_futan_rt AS jichitaiFutanRt -- A.自治体負担率
, t21.uriage_busho_cd AS uriageBushoCd -- A.売上部署コード
, NULL AS seikyuushoNo
, '0' AS uriageShuseiFg -- H.売上修正フラグ
, NULL AS versionNo -- バージョン番号
FROM
t_juchuu_shukka t21 -- 受注出荷 A
LEFT OUTER JOIN
t_haisou_haibun t22 -- LEFT OUTER JOIN 配送配分 B
ON
t21.juchuu_no = t22.haisou_katudou_denpyou_no -- ON A.受注番号 = B.伝票番号
AND
t22.haisha_gyoumu_dv = '001' -- AND B.配車業務区分 = '001'(受注)
AND
t22.delete_fg = '0' -- AND B.削除フラグ = '0'(通常)
WHERE
SUBSTR(t21.uriage_dv_cd,1 , 4) = 'HB02' -- WHERE SUBSTR(A.売上区分コード,1,4) = 'HB02'(特定福祉)
AND t21.uriage_keijouzumi_fg = '1' -- AND A.売上計上済フラグ = '1'(計上済)
AND t21.delete_fg = '0' -- AND A.削除フラグ = '0'(通常)
AND NOT EXISTS (
SELECT
1
FROM
t_uriage t23 -- 売上 C
INNER JOIN
t_tokuisaki_seikyuu_uriage t24 -- 得意先請求売上 D
ON
t23.uriage_denpyou_no = t24.uriage_denpyou_no
WHERE
t21.juchuu_no = t23.juchuu_no
AND t23.delete_fg = '0'
)
) t2 -- INNER JOIN 売上情報サブクエリ B
ON
t1.tokuisakiCd = t2.tokuisakiCd -- ON A.得意先 = B.得意先
AND
(
(t1.kaigoSeikyuuDv = 'SHO' AND t2.jichitaiFutanRt = 0) -- (A.介護請求区分 = 'SHO'(償還) AND B.自治体負担率 = 0)
OR
(t1.kaigoSeikyuuDv IN ('INI','SHH') AND t2.jichitaiFutanRt <> 0)-- (A.介護請求区分 IN ('INI','SHH') AND B.自治体負担率 <> 0)
)
LEFT OUTER JOIN
(
SELECT
t53.moto_uriage_denpyou_no -- , C.元売上伝票番号
, t53.juchuu_no -- , C.受注番号
, MAX(t51.seikyuusho_insatu_no) AS seikyuushoInsatuNo -- , MAX(請求書印刷番号) AS 請求書印刷番号
, MAX(t52.uriage_denpyou_no) AS uriageDenpyouNo -- , MAX(売上伝票番号) AS 売上伝票番号
, t54.chouhyou_template_dv -- , D.帳票テンプレート区分
FROM
t_hokenja_shinseisho t51 -- 保険者申請書 A

LEFT OUTER JOIN
t_tokuisaki_seikyuu_uriage t52 -- 得意先請求売上 B

ON
t51.seikyuusho_insatu_no = t52.seikyuusho_no -- A.請求書番号 = B.請求書番号 QA....... A.請求書番号が保険者申請書 A中でないです。ご確認してお願いします。
AND
t52.delete_fg = '0' -- B.削除フラグ = '0'(通常)
LEFT OUTER JOIN
t_uriage t53 -- 売上 C
ON
t52.uriage_denpyou_no = t53.uriage_denpyou_no -- B.売上伝票番号 = C.売上伝票番号
AND
t53.delete_fg = '0' -- C.削除フラグ = '0'(通常)

LEFT OUTER JOIN
(
SELECT
t541.file_id -- A.ファイルID
, t541.chouhyou_template_dv -- , A.テンプレート区分
FROM
m_tokuisaki_template t541 -- 得意先別帳票テンプレート(A)
WHERE
t541.delete_fg = '0' -- A.削除フラグ = '0'
UNION
SELECT
t541.file_id -- A.ファイルID
, t541.chouhyou_template_dv -- , A.テンプレート区分
FROM
m_busho_template t541 -- 部署別帳票テンプレート(A)
WHERE
t541.delete_fg = '0' -- A.削除フラグ = '0
) t54 -- 帳票テンプレートサブクエリ D
ON
t51.seikyuusho_template_file_id = t54.file_id -- A.請求書テンプレートID = D.ファイルID

WHERE
t51.delete_fg = '0' -- A.削除フラグ = '0'(通常)
GROUP BY
t53.moto_uriage_denpyou_no -- C.元売上伝票番号
,t53.juchuu_no -- ,C.受注番号
,t54.chouhyou_template_dv -- ,D.帳票テンプレート区分
) t5 -- 直近申請サブクエリ E

ON
t2.juchuuNo = t5.juchuu_no -- B.受注番号 = E.受注番号
AND
t2.denpyouNo = t5.uriageDenpyouNo--B.伝票番号 = E.売上伝票番号
AND
(
(t1.jizenShikyuuHandan = '1' AND t5.chouhyou_template_dv = 'TJS') OR (t1.jizenShikyuuHandan = '0' AND t5.chouhyou_template_dv = 'TSS')
)

Answer 1

max字段不是group by的字段。后面你用到的max的子句中还有类似情况。

 t281.seikyuusho_no -- t281.請求書番号
, MAX(t281.uriage_denpyou_no) AS uriageDenpyouNo -- MAX(t281.売上伝票番号) AS 売上伝票番号
, CASE WHEN COUNT(t281.uriage_denpyou_no) >= 2 -- CASE WHEN COUNT(t281.売上伝票番号) >= 2
THEN '1' -- THEN '1'
ELSE '0' -- ELSE '0'
END AS uriageShuseiFg -- END AS 売上修正フラグ
FROM
t_tokuisaki_seikyuu_uriage t281
WHERE
t281.delete_fg = '0'
GROUP BY
t281.seikyuusho_no