有一个sql语句需求帮助看一下

帮忙写一个sql 如何进行最后select 中最后一项的求和
SELECT bd_invbasdoc.Invcode,bd_invbasdoc.invname,bd_invbasdoc.invspec,bd_invbasdoc.invtype,bd_measdoc.MEASNAME,bd_produce.maxstornum,bd_produce.lowstocknum,bd_produce.ckcb,sum(ic_onhandnum.nonhandnum)
FROM bd_invbasdoc
LEFT JOIN bd_measdoc ON bd_measdoc.pk_measdoc=bd_invbasdoc.pk_measdoc
LEFT JOIN bd_produce ON bd_produce.pk_invbasdoc=bd_invbasdoc.pk_invbasdoc
LEFT JOIN ic_onhandnum ON ic_onhandnum.cinvbasid=bd_invbasdoc.pk_invbasdoc
LEFT JOIN bd_stordoc ON bd_stordoc.pk_stordoc=ic_onhandnum.cwarehouseid
WHERE bd_produce.sealflag='N' AND bd_invbasdoc.Invcode like 'Y%' AND bd_stordoc.sealflag='N' AND bd_stordoc.pk_corp='1002' AND sum(ic_onhandnum.nonhandnum)>bd_produce.maxstornum

你这个加了
sum这个函数 要加分组的吧 不然会报错的。

SELECT bd_invbasdoc.Invcode,bd_invbasdoc.invname,bd_invbasdoc.invspec,bd_invbasdoc.invtype,bd_measdoc.MEASNAME,bd_produce.maxstornum,bd_produce.lowstocknum,bd_produce.ckcb,sum(ic_onhandnum.nonhandnum)
FROM bd_invbasdoc
LEFT JOIN bd_measdoc ON bd_measdoc.pk_measdoc=bd_invbasdoc.pk_measdoc
LEFT JOIN bd_produce ON bd_produce.pk_invbasdoc=bd_invbasdoc.pk_invbasdoc
LEFT JOIN (SELECT cinvbasid,cwarehouseid,SUM(nonhandnum) AS nonhandnum FROM ic_onhandnum GROUP BY cinvbasid,cwarehouseid ) ic_onhandnum
ON ic_onhandnum.cinvbasid=bd_invbasdoc.pk_invbasdoc
LEFT JOIN bd_stordoc ON bd_stordoc.pk_stordoc=ic_onhandnum.cwarehouseid
WHERE bd_produce.sealflag='N'
AND bd_invbasdoc.Invcode like 'Y%'
AND bd_stordoc.sealflag='N'
AND bd_stordoc.pk_corp='1002'
AND ic_onhandnum.nonhandnum>bd_produce.maxstornum

如果没有用开窗函数就加group by，要对求和后的结果进行判断需要在group后用having

SELECT      bd_invbasdoc.Invcode,
            bd_invbasdoc.invname,
            bd_invbasdoc.invspec,
            bd_invbasdoc.invtype,
            bd_measdoc.MEASNAME,
            bd_produce.maxstornum,
            bd_produce.lowstocknum,
            bd_produce.ckcb,
            SUM(ic_onhandnum.nonhandnum)
FROM        bd_invbasdoc
            LEFT JOIN bd_measdoc ON bd_measdoc.pk_measdoc = bd_invbasdoc.pk_measdoc
            LEFT JOIN bd_produce ON bd_produce.pk_invbasdoc = bd_invbasdoc.pk_invbasdoc
            LEFT JOIN ic_onhandnum ON ic_onhandnum.cinvbasid = bd_invbasdoc.pk_invbasdoc
            LEFT JOIN bd_stordoc ON bd_stordoc.pk_stordoc = ic_onhandnum.cwarehouseid
WHERE       bd_produce.sealflag = 'N'
            AND   bd_invbasdoc.Invcode LIKE 'Y%'
            AND  bd_stordoc.sealflag = 'N'
            AND   bd_stordoc.pk_corp = '1002'
GROUP BY    bd_invbasdoc.Invcode,
            bd_invbasdoc.invname,
            bd_invbasdoc.invspec,
            bd_invbasdoc.invtype,
            bd_measdoc.MEASNAME,
            bd_produce.maxstornum,
            bd_produce.lowstocknum,
            bd_produce.ckcb
HAVING      SUM(ic_onhandnum.nonhandnum) > bd_produce.maxstornum;

使用group by 先分组，分组之后才能sum

聚合函数除了在开窗函数和返回字段中仅有聚合列的情况下，都要加上group by子句来分组。比如
select avg(col1),sm(col2) from table --仅返回聚合列，不用加group by子句，结果只返回一行
select col1,col2,sum(col3) over (order by col4) from table --开窗函数不加group by子句，一般用于计算累计，有多少行数据返回多少行
select col1,col2,sum(col3) from table group col1,col2 having sum(col3)>val --分组聚合必须加group by子句，返回数据按分组字段去重的数量

窗口函数会用吗 sum() over() 这个不影响前面的字段 ，还有 sum() 是分组后才能求和，你对sum操作在分组之后，用 having，where是在分组之前会报错的

SELECT t.*,sum(t.nonhandnum) from
(SELECT bd_invbasdoc.Invcode,bd_invbasdoc.invname,bd_invbasdoc.invspec,bd_invbasdoc.invtype,bd_measdoc.MEASNAME,bd_produce.maxstornum,bd_produce.lowstocknum,bd_produce.ckcb,ic_onhandnum.nonhandnum
FROM bd_invbasdoc
LEFT JOIN bd_measdoc ON bd_measdoc.pk_measdoc=bd_invbasdoc.pk_measdoc
LEFT JOIN bd_produce ON bd_produce.pk_invbasdoc=bd_invbasdoc.pk_invbasdoc
LEFT JOIN ic_onhandnum ON ic_onhandnum.cinvbasid=bd_invbasdoc.pk_invbasdoc
LEFT JOIN bd_stordoc ON bd_stordoc.pk_stordoc=ic_onhandnum.cwarehouseid
WHERE bd_produce.sealflag='N' AND bd_invbasdoc.Invcode like 'Y%' AND bd_stordoc.sealflag='N' AND bd_stordoc.pk_corp='1002' AND sum(ic_onhandnum.nonhandnum)>bd_produce.maxstornum ) t
这样就是所有的和了