groupby报错：“民航快报统计口径”标识符无效，要怎么修改SQL？

SELECT 会计年度，会计期间,民航快报统计口径（科目),sum(借方本币金额) from(

SELECT *from (SELECT A.ACCOUNTCODE as 科目代码,A.PERIODV as 会计期间,A.NOV as 凭证号,A.YEARV as 会计年度,A.LOCALCREDITAMOUNT as 贷方本币金额,A.LOCALDEBITAMOUNT as 借方本币金额,A.EXPLANATION as 摘要,A.PK_VOUCHERTYPEV,A.ASSID,A.PK_DETAIL,A.PK_ACCOUNTINGBOOK,A.PK_ORG,A.PK_ACCASOA,A.PK_ACCCHART,N.DISPNAME as 科目名称,O.user_name AS 制单人,

B.NAME AS 财务组织,

C.NAME AS 凭证类别,

D.FILE_ID AS 数据交换平台传递编号,F.NAME as 收支项目,G.NAME as 部门辅助,H.NAME as 预算机构,I.NAME AS 机型辅助,M.name as 根会计科目,M.BEGINPERIOD as 开始时间,

case when I.NAME in ('A319-100','A320-200','A321-251NX') then '客机' when I.NAME='B737-300' then '货机' else '客机' end as 机型,

CASE WHEN D.FILE_ID IS NULL THEN 'NC模块来源'

when length(nvl(D.FILE_ID,''))=16 then '收入结算系统' else '成本结算系统' end as 单据来源系统,

CASE WHEN instr(D.FILE_ID,'CBYT')>0 then '计提' else '调整' end as "计提/调整",

case when D.FILE_ID in ('SY01_HXFJ20220400054','SY01_HXFJ20220100096','SY01_HXFJ20210700040','SY01_HXFJ20200700276') then '核销账单'

when instr(D.FILE_ID,'SY01_HX')>0 then '核销账单' else '结算账单' end as 账单类型,

case when instr(A.ACCOUNTCODE,'640108')>0 then '起降费' when instr(A.ACCOUNTCODE,'640137')>0 then '航务管理费'when instr(A.ACCOUNTCODE,'640102')>0 then '航油成本'when instr(A.ACCOUNTCODE,'640116')>0 then '餐食成本'when instr(A.ACCOUNTCODE,'640137')>0 then '航务管理费'when instr(A.ACCOUNTCODE,'640119')>0 then '不正常航班费用'when instr(A.ACCOUNTCODE,'640104')>0 then '民航发展基金' when instr(A.ACCOUNTCODE,'640135')>0 then '机组费用'when instr(A.ACCOUNTCODE,'640105')>0 then '非例行检查费'else '其他' end as "民航快报统计口径（科目)"

FROM GL_DETAIL A

LEFT JOIN (SELECT B.PK_CORP,B.NAME FROM ORG_CORP B)B ON B.PK_CORP=A.PK_ORG

LEFT JOIN (SELECT C.PK_VOUCHERTYPE,C.NAME from BD_VOUCHERTYPE C )C ON C.PK_VOUCHERTYPE=A.PK_VOUCHERTYPEV

LEFT JOIN (SELECT D.FILE_ID,D.PK_DETAIL from V_GL_DETAIL D)D ON D.PK_DETAIL= A.PK_DETAIL

LEFT JOIN GL_DOCFREE1 E on A.ASSID=E.ASSID

LEFT JOIN BD_INOUTBUSICLASS F ON E.F8=F.PK_INOUTBUSICLASS

LEFT JOIN ORG_DEPT G ON E.F1=G.PK_DEPT

LEFT JOIN ORG_DEPT H ON E.F26=H.PK_DEPT

LEFT JOIN BD_DEFDOC I ON E.F22=I.PK_DEFDOC

left join (SELECT M.BEGINPERIOD,M.PK_ACCCHART,M.name FROM BD_ACCCHART M )M ON A.PK_ACCCHART=M.PK_ACCCHART

LEFT JOIN (SELECT N.PK_ACCASOA,N.DISPNAME from BD_ACCASOA N)N ON N.PK_ACCASOA= A.PK_ACCASOA

LEFT JOIN (SELECT O.user_name,O.cuserid from sm_user O)O ON O.cuserid= A.PK_PREPAREDV)

WHERE PK_ORG='00016810000000001RZ7' and 根会计科目='长龙科目体系_根科目表' and 开始时间='2022-01-01' and 会计年度='2022' and 单据来源系统<>'收入结算系统' and 凭证号<>'0')

group by 会计年度，会计期间

SQL yzm742603 发布于 2022-10-27 09:03

1min目标场景问卷

立即参与

回答问题关注问题

悬赏：0 F币 + 添加悬赏

提示：增加悬赏、完善问题、追问等操作，可使您的问题被置顶，并向所有关注者发送通知

共3回答

最佳回答

RiveryLv5中级互助
发布于2022-10-27 09:24（编辑于 2022-10-27 09:25）

SELECT 会计年度,会计期间,"民航快报统计口径（科目)",sum(借方本币金额) from(

SELECT *from (SELECT A.ACCOUNTCODE as 科目代码,A.PERIODV as 会计期间,A.NOV as 凭证号,

A.YEARV as 会计年度,A.LOCALCREDITAMOUNT as 贷方本币金额,A.LOCALDEBITAMOUNT as 借方本币金额,

A.EXPLANATION as 摘要,A.PK_VOUCHERTYPEV,A.ASSID,A.PK_DETAIL,A.PK_ACCOUNTINGBOOK,

A.PK_ORG,A.PK_ACCASOA,A.PK_ACCCHART,N.DISPNAME as 科目名称,O.user_name AS 制单人,

B.NAME AS 财务组织,

C.NAME AS 凭证类别,

D.FILE_ID AS 数据交换平台传递编号,F.NAME as 收支项目,G.NAME as 部门辅助,H.NAME as 预算机构,I.NAME AS 机型辅助,M.name as 根会计科目,M.BEGINPERIOD as 开始时间,

case when I.NAME in ('A319-100','A320-200','A321-251NX') then '客机' when I.NAME='B737-300' then '货机' else '客机' end as 机型,

CASE WHEN D.FILE_ID IS NULL THEN 'NC模块来源'

when length(nvl(D.FILE_ID,''))=16 then '收入结算系统' else '成本结算系统' end as 单据来源系统,

CASE WHEN instr(D.FILE_ID,'CBYT')>0 then '计提' else '调整' end as "计提/调整",

case when D.FILE_ID in ('SY01_HXFJ20220400054','SY01_HXFJ20220100096','SY01_HXFJ20210700040','SY01_HXFJ20200700276') then '核销账单'

when instr(D.FILE_ID,'SY01_HX')>0 then '核销账单' else '结算账单' end as 账单类型,

FROM GL_DETAIL A

LEFT JOIN (SELECT B.PK_CORP,B.NAME FROM ORG_CORP B)B ON B.PK_CORP=A.PK_ORG

LEFT JOIN (SELECT C.PK_VOUCHERTYPE,C.NAME from BD_VOUCHERTYPE C )C ON C.PK_VOUCHERTYPE=A.PK_VOUCHERTYPEV

LEFT JOIN (SELECT D.FILE_ID,D.PK_DETAIL from V_GL_DETAIL D)D ON D.PK_DETAIL= A.PK_DETAIL

LEFT JOIN GL_DOCFREE1 E on A.ASSID=E.ASSID

LEFT JOIN BD_INOUTBUSICLASS F ON E.F8=F.PK_INOUTBUSICLASS

LEFT JOIN ORG_DEPT G ON E.F1=G.PK_DEPT

LEFT JOIN ORG_DEPT H ON E.F26=H.PK_DEPT

LEFT JOIN BD_DEFDOC I ON E.F22=I.PK_DEFDOC

left join (SELECT M.BEGINPERIOD,M.PK_ACCCHART,M.name FROM BD_ACCCHART M )M ON A.PK_ACCCHART=M.PK_ACCCHART

LEFT JOIN (SELECT N.PK_ACCASOA,N.DISPNAME from BD_ACCASOA N)N ON N.PK_ACCASOA= A.PK_ACCASOA

LEFT JOIN (SELECT O.user_name,O.cuserid from sm_user O)O ON O.cuserid= A.PK_PREPAREDV)

group by 会计年度,会计期间,"民航快报统计口径（科目)"

举报收起评论(4)

yzm742603(提问者) 可行，是什么原因呢？加了双引号就好了

2022-10-27 09:38

Rivery 回复 yzm742603(提问者) 感觉应该是你的字段名加了括号，所以要用双引号，还有就是你有两处地方的逗号是中文的，要改为英文的

2022-10-27 09:43

Rivery 回复 yzm742603(提问者) 还有你group by少了民航快报统计口径（科目)这个字段

2022-10-27 09:50

yzm742603(提问者) 回复 Rivery 好的

2022-10-27 09:56

Z4u3z1Lv6专家互助
发布于2022-10-27 09:06（编辑于 2022-10-27 09:08）

逗号错了吧

这儿再加个别名

举报收起评论(4)

yzm742603(提问者) 按照上述修改，报错为“缺失右括号” SELECT 会计年度,会计期间,民航快报统计口径（科目),sum(借方本币金额) from( SELECT *from (SELECT A.ACCOUNTCODE as 科目代码,A.PERIODV as 会计期间,A.NOV as 凭证号,A.YEARV as 会计年度,A.LOCALCREDITAMOUNT as 贷方本币金额,A.LOCALDEBITAMOUNT as 借方本币金额,A.EXPLANATION as 摘要,A.PK_VOUCHERTYPEV,A.ASSID,A.PK_DETAIL,A.PK_ACCOUNTINGBOOK,A.PK_ORG,A.PK_ACCASOA,A.PK_ACCCHART,N.DISPNAME as 科目名称,O.user_name AS 制单人, B.NAME AS 财务组织, C.NAME AS 凭证类别, D.FILE_ID AS 数据交换平台传递编号,F.NAME as 收支项目,G.NAME as 部门辅助,H.NAME as 预算机构,I.NAME AS 机型辅助,M.name as 根会计科目,M.BEGINPERIOD as 开始时间, case when I.NAME in (\'A319-100\',\'A320-200\',\'A321-251NX\') then \'客机\' when I.NAME=\'B737-300\' then \'货机\' else \'客机\' end as 机型, CASE WHEN D.FILE_ID IS NULL THEN \'NC模块来源\' when length(nvl(D.FILE_ID,\'\'))=16 then \'收入结算系统\' else \'成本结算系统\' end as 单据来源系统, CASE WHEN instr(D.FILE_ID,\'CBYT\')>0 then \'计提\' else \'调整\' end as \"计提/调整\", case when D.FILE_ID in (\'SY01_HXFJ20220400054\',\'SY01_HXFJ20220100096\',\'SY01_HXFJ20210700040\',\'SY01_HXFJ20200700276\') then \'核销账单\' when instr(D.FILE_ID,\'SY01_HX\')>0 then \'核销账单\' else \'结算账单\' end as 账单类型, case when instr(A.ACCOUNTCODE,\'640108\')>0 then \'起降费\' when instr(A.ACCOUNTCODE,\'640137\')>0 then \'航务管理费\'when instr(A.ACCOUNTCODE,\'640102\')>0 then \'航油成本\'when instr(A.ACCOUNTCODE,\'640116\')>0 then \'餐食成本\'when instr(A.ACCOUNTCODE,\'640137\')>0 then \'航务管理费\'when instr(A.ACCOUNTCODE,\'640119\')>0 then \'不正常航班费用\'when instr(A.ACCOUNTCODE,\'640104\')>0 then \'民航发展基金\' when instr(A.ACCOUNTCODE,\'640135\')>0 then \'机组费用\'when instr(A.ACCOUNTCODE,\'640105\')>0 then \'非例行检查费\'else \'其他\' end as \"民航快报统计口径（科目)\" FROM GL_DETAIL A LEFT JOIN (SELECT B.PK_CORP,B.NAME FROM ORG_CORP B)B ON B.PK_CORP=A.PK_ORG LEFT JOIN (SELECT C.PK_VOUCHERTYPE,C.NAME from BD_VOUCHERTYPE C )C ON C.PK_VOUCHERTYPE=A.PK_VOUCHERTYPEV LEFT JOIN (SELECT D.FILE_ID,D.PK_DETAIL from V_GL_DETAIL D)D ON D.PK_DETAIL= A.PK_DETAIL LEFT JOIN GL_DOCFREE1 E on A.ASSID=E.ASSID LEFT JOIN BD_INOUTBUSICLASS F ON E.F8=F.PK_INOUTBUSICLASS LEFT JOIN ORG_DEPT G ON E.F1=G.PK_DEPT LEFT JOIN ORG_DEPT H ON E.F26=H.PK_DEPT LEFT JOIN BD_DEFDOC I ON E.F22=I.PK_DEFDOC left join (SELECT M.BEGINPERIOD,M.PK_ACCCHART,M.name FROM BD_ACCCHART M )M ON A.PK_ACCCHART=M.PK_ACCCHART LEFT JOIN (SELECT N.PK_ACCASOA,N.DISPNAME from BD_ACCASOA N)N ON N.PK_ACCASOA= A.PK_ACCASOA LEFT JOIN (SELECT O.user_name,O.cuserid from sm_user O)O ON O.cuserid= A.PK_PREPAREDV) as s WHERE PK_ORG=\'00016810000000001RZ7\' and 根会计科目=\'长龙科目体系_根科目表\' and 开始时间=\'2022-01-01\' and 会计年度=\'2022\' and 单据来源系统<>\'收入结算系统\' and 凭证号<>\'0\') as t group by 会计年度,会计期间

2022-10-27 09:16

Z4u3z1 回复 yzm742603(提问者) SELECT 会计年度,会计期间,民航快报统计口径(科目),sum(借方本币金额) from( SELECT *from ( SELECT A.ACCOUNTCODE as 科目代码,A.PERIODV as 会计期间,A.NOV as 凭证号,A.YEARV as 会计年度,A.LOCALCREDITAMOUNT as 贷方本币金额,A.LOCALDEBITAMOUNT as 借方本币金额,A.EXPLANATION as 摘要,A.PK_VOUCHERTYPEV,A.ASSID,A.PK_DETAIL,A.PK_ACCOUNTINGBOOK,A.PK_ORG,A.PK_ACCASOA,A.PK_ACCCHART,N.DISPNAME as 科目名称,O.user_name AS 制单人, B.NAME AS 财务组织, C.NAME AS 凭证类别, D.FILE_ID AS 数据交换平台传递编号,F.NAME as 收支项目,G.NAME as 部门辅助,H.NAME as 预算机构,I.NAME AS 机型辅助,M.name as 根会计科目,M.BEGINPERIOD as 开始时间, case when I.NAME in (\'A319-100\',\'A320-200\',\'A321-251NX\') then \'客机\' when I.NAME=\'B737-300\' then \'货机\' else \'客机\' end as 机型, CASE WHEN D.FILE_ID IS NULL THEN \'NC模块来源\' when length(nvl(D.FILE_ID,\'\'))=16 then \'收入结算系统\' else \'成本结算系统\' end as 单据来源系统, CASE WHEN instr(D.FILE_ID,\'CBYT\')>0 then \'计提\' else \'调整\' end as \"计提/调整\", case when D.FILE_ID in (\'SY01_HXFJ20220400054\',\'SY01_HXFJ20220100096\',\'SY01_HXFJ20210700040\',\'SY01_HXFJ20200700276\') then \'核销账单\' when instr(D.FILE_ID,\'SY01_HX\')>0 then \'核销账单\' else \'结算账单\' end as 账单类型, case when instr(A.ACCOUNTCODE,\'640108\')>0 then \'起降费\' when instr(A.ACCOUNTCODE,\'640137\')>0 then \'航务管理费\'when instr(A.ACCOUNTCODE,\'640102\')>0 then \'航油成本\'when instr(A.ACCOUNTCODE,\'640116\')>0 then \'餐食成本\'when instr(A.ACCOUNTCODE,\'640137\')>0 then \'航务管理费\'when instr(A.ACCOUNTCODE,\'640119\')>0 then \'不正常航班费用\'when instr(A.ACCOUNTCODE,\'640104\')>0 then \'民航发展基金\' when instr(A.ACCOUNTCODE,\'640135\')>0 then \'机组费用\'when instr(A.ACCOUNTCODE,\'640105\')>0 then \'非例行检查费\'else \'其他\' end as \"民航快报统计口径(科目)\" FROM GL_DETAIL A LEFT JOIN ( SELECT B.PK_CORP,B.NAME FROM ORG_CORP B)B ON B.PK_CORP=A.PK_ORG LEFT JOIN ( SELECT C.PK_VOUCHERTYPE,C.NAME from BD_VOUCHERTYPE C )C ON C.PK_VOUCHERTYPE=A.PK_VOUCHERTYPEV LEFT JOIN ( SELECT D.FILE_ID,D.PK_DETAIL from V_GL_DETAIL D)D ON D.PK_DETAIL= A.PK_DETAIL LEFT JOIN GL_DOCFREE1 E on A.ASSID=E.ASSID LEFT JOIN BD_INOUTBUSICLASS F ON E.F8=F.PK_INOUTBUSICLASS LEFT JOIN ORG_DEPT G ON E.F1=G.PK_DEPT LEFT JOIN ORG_DEPT H ON E.F26=H.PK_DEPT LEFT JOIN BD_DEFDOC I ON E.F22=I.PK_DEFDOC left join ( SELECT M.BEGINPERIOD,M.PK_ACCCHART,M.name FROM BD_ACCCHART M )M ON A.PK_ACCCHART=M.PK_ACCCHART LEFT JOIN ( SELECT N.PK_ACCASOA,N.DISPNAME from BD_ACCASOA N)N ON N.PK_ACCASOA= A.PK_ACCASOA LEFT JOIN ( SELECT O.user_name,O.cuserid from sm_user O)O ON O.cuserid= A.PK_PREPAREDV ) as s WHERE PK_ORG=\'00016810000000001RZ7\' and 根会计科目=\'长龙科目体系_根科目表\' and 开始时间=\'2022-01-01\' and 会计年度=\'2022\' and 单据来源系统<>\'收入结算系统\' and 凭证号<>\'0\' ) as t group by 会计年度,会计期间,民航快报统计口径(科目)

2022-10-27 09:21

yzm742603(提问者) 回复 Z4u3z1 还是报错为“缺失右括号”

2022-10-27 09:31

Z4u3z1 回复 yzm742603(提问者) 如果是SqlServer 把民航快报统计口径(科目) 改成 [民航快报统计口径(科目)],其他数据库直接改成民航快报统计口径试试

2022-10-27 09:35

用户k6280494Lv6资深互助
发布于2022-10-27 09:18

SELECT 会计年度,会计期间,民航快报统计口径（科目),SUM (借方本币金额) 借方本币金额 FROM (

SELECT*FROM (

SELECT A.ACCOUNTCODE AS 科目代码,A.PERIODV AS 会计期间,A.NOV AS 凭证号,A.YEARV AS 会计年度,A.LOCALCREDITAMOUNT AS 贷方本币金额,A.LOCALDEBITAMOUNT AS 借方本币金额,A.EXPLANATION AS 摘要,A.PK_VOUCHERTYPEV,A.ASSID,A.PK_DETAIL,A.PK_ACCOUNTINGBOOK,A.PK_ORG,A.PK_ACCASOA,A.PK_ACCCHART,N.DISPNAME AS 科目名称,O.user_name AS 制单人,B.NAME AS 财务组织,C.NAME AS 凭证类别,D.FILE_ID AS 数据交换平台传递编号,F.NAME AS 收支项目,G.NAME AS 部门辅助,H.NAME AS 预算机构,I.NAME AS 机型辅助,M.name AS 根会计科目,M.BEGINPERIOD AS 开始时间,CASE WHEN I.NAME IN ('A319-100','A320-200','A321-251NX') THEN '客机' WHEN I.NAME='B737-300' THEN '货机' ELSE '客机' END AS 机型,CASE WHEN D.FILE_ID IS NULL THEN 'NC模块来源' WHEN length (nvl (D.FILE_ID,''))=16 THEN '收入结算系统' ELSE '成本结算系统' END AS 单据来源系统,CASE WHEN instr (D.FILE_ID,'CBYT')> 0 THEN '计提' ELSE '调整' END AS "计提/调整",CASE WHEN D.FILE_ID IN ('SY01_HXFJ20220400054','SY01_HXFJ20220100096','SY01_HXFJ20210700040','SY01_HXFJ20200700276') THEN '核销账单' WHEN instr (D.FILE_ID,'SY01_HX')> 0 THEN '核销账单' ELSE '结算账单' END AS 账单类型,CASE WHEN instr (A.ACCOUNTCODE,'640108')> 0 THEN '起降费' WHEN instr (A.ACCOUNTCODE,'640137')> 0 THEN '航务管理费' WHEN instr (A.ACCOUNTCODE,'640102')> 0 THEN '航油成本' WHEN instr (A.ACCOUNTCODE,'640116')> 0 THEN '餐食成本' WHEN instr (A.ACCOUNTCODE,'640137')> 0 THEN '航务管理费' WHEN instr (A.ACCOUNTCODE,'640119')> 0 THEN '不正常航班费用' WHEN instr (A.ACCOUNTCODE,'640104')> 0 THEN '民航发展基金' WHEN instr (A.ACCOUNTCODE,'640135')> 0 THEN '机组费用' WHEN instr (A.ACCOUNTCODE,'640105')> 0 THEN '非例行检查费' ELSE '其他' END AS "民航快报统计口径（科目)" FROM GL_DETAIL A LEFT JOIN (

SELECT B.PK_CORP,B.NAME FROM ORG_CORP B) B ON B.PK_CORP=A.PK_ORG LEFT JOIN (

SELECT C.PK_VOUCHERTYPE,C.NAME FROM BD_VOUCHERTYPE C) C ON C.PK_VOUCHERTYPE=A.PK_VOUCHERTYPEV LEFT JOIN (

SELECT D.FILE_ID,D.PK_DETAIL FROM V_GL_DETAIL D) D ON D.PK_DETAIL=A.PK_DETAIL LEFT JOIN GL_DOCFREE1 E ON A.ASSID=E.ASSID LEFT JOIN BD_INOUTBUSICLASS F ON E.F8=F.PK_INOUTBUSICLASS LEFT JOIN ORG_DEPT G ON E.F1=G.PK_DEPT LEFT JOIN ORG_DEPT H ON E.F26=H.PK_DEPT LEFT JOIN BD_DEFDOC I ON E.F22=I.PK_DEFDOC LEFT JOIN (

SELECT M.BEGINPERIOD,M.PK_ACCCHART,M.name FROM BD_ACCCHART M) M ON A.PK_ACCCHART=M.PK_ACCCHART LEFT JOIN (

SELECT N.PK_ACCASOA,N.DISPNAME FROM BD_ACCASOA N) N ON N.PK_ACCASOA=A.PK_ACCASOA LEFT JOIN (

SELECT O.user_name,O.cuserid FROM sm_user O) O ON O.cuserid=A.PK_PREPAREDV) WHERE PK_ORG='00016810000000001RZ7' AND 根会计科目='长龙科目体系_根科目表' AND 开始时间='2022-01-01' AND 会计年度='2022' AND 单据来源系统<> '收入结算系统' AND 凭证号<> '0') GROUP BY 会计年度,会计期间