数据集取数，列名错误

下面这段SQL,with a as() 里面的是没有错误的，查询显示列名错误

with a as (..............)

select a.GroupID,

a.GroupName,a.groupseq,

a.AreaID,

a.AreaName,a.areaseq,

a.CompanyID,

a.CompanyName,a.companyseq,

a.ProjectID,

a.ProjectName,a.projectseq,

a.BeginDate XMQDSJ,

a.JHZJPHSJ,

a.SJZJPHSJ,

sum(a.DYKY) as DYKY,

sum(a.DYBKY) as DYBKY,

sum(a.SJJTZB) as SJJTZB,

sum(a.NBGSJC) as NBGSJC,

sum(a.QTJC) as QTJC,

sum(a.LZCB) as LZCB,

sum(a.LZFY) as LZFY,

sum(a.LZLX) as LZLX,

sum(a.QT) as QT,

sum(a.JTZBTR) as JTZBZR,

sum(a.NBGSJR) as NBGSJR,

sum(a.RZLR) as RZLR,

sum(a.QTJR) as QTJR,

a.Remark,

a.TDBZJSJ

FROM a

left join Authority_MY c on a.Groupid=c.authority_id

where 1=1

${if(fr_username='root',"and 1=1","and c.UserName = '" + fr_username + "'")}

and xlxm IN ('${xlxm}')

${if(len(jt)=0,"and 1=1","and a.groupid in ('"+jt+"')")}

and a.ProjectID <> '0C3891D4-1439-E711-80B9-6C0B84B83F14'

group by GroupID,CompanyID,ProjectID

order by groupseq,xlxm,areaseq,companyseq,projectseq

FineReport Darius 发布于 2019-8-8 14:25

1min目标场景问卷

立即参与

回答问题关注问题

悬赏：4 F币 + 添加悬赏

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

共2回答

Lz爱学习Lv6见习互助
发布于2019-8-8 14:30

图片.png

这个fr_username是fr内置的用户名参数吗，如果是的话，应该是$fine_username

举报收起评论(1)

Darius(提问者) 错误不在这里，FR9.0是fr_username。错误在那些SUM()和group by 那里

2019-08-08 14:33

zsh331Lv8专家互助
发布于2019-8-8 14:39

楼主：SQL不规范，黄色区域字段既然不在group by 后面，那select 字段里为什么不用“函数”处理？

with a as (..............)
SELECT
	a.GroupID,
	a.GroupName,
	a.groupseq,
	max(a.AreaID),
	max(a.AreaName),
	max(a.areaseq),
	max(a.CompanyID),
	max(a.CompanyName),
	max(a.companyseq),
	max(a.ProjectID),
	max(a.ProjectName),
	max(a.projectseq),
	max(a.BeginDate)  XMQDSJ,
	max(a.JHZJPHSJ),
	max(a.SJZJPHSJ),
	sum(a.DYKY) AS DYKY,
	sum(a.DYBKY) AS DYBKY,
	sum(a.SJJTZB) AS SJJTZB,
	sum(a.NBGSJC) AS NBGSJC,
	sum(a.QTJC) AS QTJC,
	sum(a.LZCB) AS LZCB,
	sum(a.LZFY) AS LZFY,
	sum(a.LZLX) AS LZLX,
	sum(a.QT) AS QT,
	sum(a.JTZBTR) AS JTZBZR,
	sum(a.NBGSJR) AS NBGSJR,
	sum(a.RZLR) AS RZLR,
	sum(a.QTJR) AS QTJR,
	a.Remark,
	a.TDBZJSJ
FROM
	a
LEFT JOIN Authority_MY c ON a.Groupid = c.authority_id
WHERE
	1 = 1 $ {
IF (
	fr_username = 'root',
	"and 1=1",
	"and c.UserName = '" + fr_username + "'"
) }
AND xlxm IN ('${xlxm}') $ {
IF (
	len (jt) = 0,
	"and 1=1",
	"and a.groupid in ('" + jt + "')"
) }
AND a.ProjectID <> '0C3891D4-1439-E711-80B9-6C0B84B83F14'
GROUP BY
	GroupID,
	CompanyID,
	ProjectID
ORDER BY
	groupseq,
	xlxm,
	areaseq,
	companyseq,
	projectseq