SQL分组函数

Question

我正在使用 odbc 连接到 as400，我知道连接字符串是正确的并且可以正常工作，因为我的所有其他查询都可以正常工作。

为什么这个sql语句有效：

select
  rdqty,
  alib.rcvshpdt.mbsnodesc,
  rdauthor,
  rdtitle,
  rdediton,
  ifnull(iecactno,actvty) as actlvl,
  class,
  ifnull(ieclp,lp) as cst
from
  alib.rcvshpdt
  left outer join
  alib.bkmaster on alib.rcvshpdt.mbsnodesc=alib.bkmaster.mbsno
  left outer join
  alib.iecostda on alib.rcvshpdt.rcvno=alib.iecostda.rcvno
  and alib.rcvshpdt.mbsnodesc = alib.iecostda.mbsnodesc
where
  alib.rcvshpdt.rcvno='3930697'
order by
  actlvl asc

但是一旦我尝试对数量求和并将结果分组如下，它就会失败。

select
  sum(rdqty) as quanity,
  alib.rcvshpdt.mbsnodesc,
  rdauthor,
  rdtitle,
  rdediton,
  ifnull(iecactno,actvty) as actlvl,
  class,
  ifnull(ieclp,lp) as cst
from
  alib.rcvshpdt
  left outer join
  alib.bkmaster on alib.rcvshpdt.mbsnodesc=alib.bkmaster.mbsno
  left outer join
  alib.iecostda on alib.rcvshpdt.rcvno=alib.iecostda.rcvno
  and alib.rcvshpdt.mbsnodesc = alib.iecostda.mbsnodesc
where
  alib.rcvshpdt.rcvno='3930697'
group by
  alib.rcvshpdt.mbsnodesc, rdauthor, rdtitle, rdediton, actlvl, class, cst
order by
  actlvl asc

Answer 1

据我所知，您不能按别名进行分组。尝试更改 Group 子句以直接使用计算，如下所示：

alib.rcvshpdt.mbsnodesc, rdauthor, rdtitle, rdediton, ifnull(iecactno,actvty), class, ifnull(ieclp,lp)