//本学年应缴金额
string select = "SELECT PBMDM.BMMC as fw,sum(case when XS_SFZZ.XH = PXSDM.XH and XS_SFZZ.SFXMDM = '01' and PXSDM.BMDM in (select BMBH from PYHBMQX where YHBH = '" + Session["yhbh"] + "') and PXSDM.BMDM = PBMDM.BMDM and XS_SFZZ.SFQJDM = '08' then XS_SFZZ.YJJE else 0 end) AS BNYJJE,";
//本学年减免金额
select = select + "sum(case when XS_SFZZ.XH = PXSDM.XH and XS_SFZZ.SFXMDM = '01' and PXSDM.BMDM in ('13','90') and PXSDM.BMDM = PBMDM.BMDM and XS_SFZZ.SFQJDM = '08' then XS_SFZZ.JMJE else 0 end) AS BNJMJE,";
//本学年实缴金额
select = select + "sum(case when XS_SFZZ.XH = PXSDM.XH and XS_SFZZ.SFXMDM = '01' and PXSDM.BMDM in ('13','90') and PXSDM.BMDM = PBMDM.BMDM and XS_SFZZ.SFQJDM = '08' then XS_SFZZ.SJJE else 0 end) AS BNSJJE,";
//本学年退费金额
select = select + "sum(case when XS_SFZZ.XH = PXSDM.XH and XS_SFZZ.SFXMDM = '01' and PXSDM.BMDM in ('13','90') and PXSDM.BMDM = PBMDM.BMDM and XS_SFZZ.SFQJDM = '08' then XS_SFZZ.TFJE else 0 end) AS BNTFJE,";
//本学年缴费率
select = select + "sum(case when XS_SFZZ.XH = PXSDM.XH and XS_SFZZ.SFXMDM = '01' and PXSDM.BMDM in ('13','90') and PXSDM.BMDM = PBMDM.BMDM and XS_SFZZ.SFQJDM = '08' then XS_SFZZ.SJJE else 0 end) / sum(case when XS_SFZZ.XH = PXSDM.XH and XS_SFZZ.SFXMDM = '01' and PXSDM.BMDM = PBMDM.BMDM and XS_SFZZ.SFQJDM = '08' then XS_SFZZ.YJJE else 0.1 end) *100 AS BNJFL,";
//累计情况
select = select + "sum(XS_SFZZ.YJJE) AS LJYJJE, sum(XS_SFZZ.JMJE) AS LJJMJE, sum(XS_SFZZ.SJJE) AS LJSJJE ,sum(XS_SFZZ.TFJE) AS LJTFJE, (sum(XS_SFZZ.SJJE)/sum(XS_SFZZ.YJJE)*100) AS LJJFL FROM XS_SFZZ, PXSDM, PBMDM where XS_SFZZ.XH = PXSDM.XH and XS_SFZZ.SFXMDM = '01' and PXSDM.BMDM in (select BMBH from PYHBMQX where YHBH = '" + Session["yhbh"] + "') and PXSDM.BMDM = PBMDM.BMDM and (XS_SFZZ.SFQJDM = '05' or XS_SFZZ.SFQJDM = '06' or XS_SFZZ.SFQJDM = '07' or XS_SFZZ.SFQJDM = '08') GROUP BY PBMDM.BMMC";
出错提示为:不能对包含聚合或子查询的表达式执行聚合函数。
在关键字 'and' 附近有语法错误。 我知道出错的地方在红色字的地方,但不知道怎么改,请高手指教,谢谢!
但不知道怎么修改才能得到我要的结果!
许多条件在where中已经指定,
不需要在case when里重申。
参照下面的句子改下试试:
SELECT PBMDM.BMMC as fw,
sum(case when XS_SFZZ.SFQJDM = '08' then XS_SFZZ.YJJE else 0 end) AS BNYJJE,
sum(case when PXSDM.BMDM in ('13','90') and XS_SFZZ.SFQJDM = '08' then XS_SFZZ.JMJE else 0 end) AS BNJMJE,
sum(case when PXSDM.BMDM in ('13','90') and XS_SFZZ.SFQJDM = '08' then XS_SFZZ.SJJE else 0 end) AS BNSJJE,
sum(case when PXSDM.BMDM in ('13','90') and XS_SFZZ.SFQJDM = '08' then XS_SFZZ.TFJE else 0 end) AS BNTFJE,
sum(case when PXSDM.BMDM in ('13','90') and XS_SFZZ.SFQJDM = '08' then XS_SFZZ.SJJE else 0 end) / sum(case when XS_SFZZ.SFQJDM = '08' then XS_SFZZ.YJJE else 0.1 end) *100 AS BNJFL,
sum(XS_SFZZ.YJJE) AS LJYJJE, sum(XS_SFZZ.JMJE) AS LJJMJE, sum(XS_SFZZ.SJJE) AS LJSJJE ,sum(XS_SFZZ.TFJE) AS LJTFJE, (sum(XS_SFZZ.SJJE)/sum(XS_SFZZ.YJJE)*100) AS LJJFL
FROM XS_SFZZ, PXSDM, PBMDM
where XS_SFZZ.XH = PXSDM.XH and XS_SFZZ.SFXMDM = '01' and PXSDM.BMDM in (select BMBH from PYHBMQX where YHBH = '" + Session["yhbh"] + "')
and PXSDM.BMDM = PBMDM.BMDM and (XS_SFZZ.SFQJDM = '05' or XS_SFZZ.SFQJDM = '06' or XS_SFZZ.SFQJDM = '07' or XS_SFZZ.SFQJDM = '08')
GROUP BY PBMDM.BMMC