/*
订单总利润统计
*/
CREATE PROCEDURE UP_DD_OrderInfo_TotalPrice11@WhereStr varchar(1000)
as
declare @WhereSqls varchar(2000) --where 子句
declare @Sqls nvarchar(4000) --最终组合成的Sqls语句if @WhereStr <> ''
set @WhereSqls = ' where ('+@WhereStr+')'
else
set @WhereSqls = ''
set @Sqls =' Select ((SELECT isnull(SUM(TRPrice) ,0) FROM DD_RecMoney b WHERE a.OrderID =b.OrderID AND MoneyType=1)-(SELECT isnull(SUM(TRPrice) ,0) FROM DD_RecMoney b WHERE a.OrderID =b.OrderID AND MoneyType=2)) as saleprice from DD_orderinfo a ' +@WhereSqls
exec sp_executesql @Sqls--print @Sqls
GO现在我想对以上saleprice进行求和,直接用sum是无法实现的,请问怎么写这个ms sql呢
订单总利润统计
*/
CREATE PROCEDURE UP_DD_OrderInfo_TotalPrice11@WhereStr varchar(1000)
as
declare @WhereSqls varchar(2000) --where 子句
declare @Sqls nvarchar(4000) --最终组合成的Sqls语句if @WhereStr <> ''
set @WhereSqls = ' where ('+@WhereStr+')'
else
set @WhereSqls = ''
set @Sqls =' Select ((SELECT isnull(SUM(TRPrice) ,0) FROM DD_RecMoney b WHERE a.OrderID =b.OrderID AND MoneyType=1)-(SELECT isnull(SUM(TRPrice) ,0) FROM DD_RecMoney b WHERE a.OrderID =b.OrderID AND MoneyType=2)) as saleprice from DD_orderinfo a ' +@WhereSqls
exec sp_executesql @Sqls--print @Sqls
GO现在我想对以上saleprice进行求和,直接用sum是无法实现的,请问怎么写这个ms sql呢
订单总利润统计
*/
CREATE PROCEDURE UP_DD_OrderInfo_TotalPrice11 @WhereStr varchar(1000)
as
declare @WhereSqls varchar(2000) --where 子句
declare @Sqls nvarchar(4000) --最终组合成的Sqls语句
decalare @db table --自定义一个表if @WhereStr <> ''
set @WhereSqls = ' where ('+@WhereStr+')'
else
set @WhereSqls = ''
set @Sqls =' Select ((SELECT isnull(SUM(TRPrice) ,0) FROM DD_RecMoney b WHERE a.OrderID =b.OrderID AND MoneyType=1)-(SELECT isnull(SUM(TRPrice) ,0) FROM DD_RecMoney b WHERE a.OrderID =b.OrderID AND MoneyType=2)) as saleprice from DD_orderinfo a ' +@WhereSqls + 'into dt'
为什么不能直接用SUM实现的?没明白你的意思,你就在加个求和不可以吗:
set @Sqls ='
SELECT SUM(Tab.saleprice) AS saleprice FROM (
Select (
(SELECT isnull(SUM(TRPrice) ,0) FROM DD_RecMoney b
WHERE a.OrderID =b.OrderID AND MoneyType=1)-
(SELECT isnull(SUM(TRPrice) ,0) FROM DD_RecMoney b
WHERE a.OrderID =b.OrderID AND MoneyType=2)) as saleprice from DD_orderinfo a ' +@WhereSqls +') Tab'
exec sp_executesql @Sqls