sql存储过程中合计的问题

jio891250 2010-10-29 05:19:20

存储过程如下：
1.这个是根据时间查询的
declare @sql varchar(4000)
set @sql='select hyh,LargeBodyColorID,ColorID,SizeID,sum(Quantity) as sumcol '
select @sql=@sql+',sum(case DateTate when '''+RTRIM(DateTate)+''' then Quantity else 0 end) as ['+RTRIM(DateTate)+'] ' from (select distinct DateTate from CuttingPlan where DateTate<='10-10-18' and DateTate>='10-10-12') as a
set @sql=@sql+' from CuttingPlan c where DateTate<=''10-10-18'' and DateTate>=''10-10-12'' group by hyh, LargeBodyColorID,ColorID,SizeID union select OrderID=''合计'',LargeBodyColorID='''',ColorID='''',SizeID='''',sumcol=sum(Quantity)'
select @sql=@sql+',sum(case DateTate when '''+RTRIM(DateTate)+''' then Quantity else 0 end) as ['+RTRIM(DateTate)+'] ' from (select distinct DateTate from CuttingPlan where DateTate<='10-10-18' and DateTate>='10-10-12') as b
set @sql=@sql+' from CuttingPlan as d where d.DateTate<=''10-10-18'' and d.DateTate>=''10-10-12'''
exec(@sql)
print(@sql)

2.这个是查询所有的没有条件的
declare @sql varchar(4000)
set @sql='select hyh,LargeBodyColorID,ColorID,SizeID,sum(Quantity) as sumcol '
select @sql=@sql+',sum(case DateTate when '''+RTRIM(DateTate)+''' then Quantity else 0 end) as ['+RTRIM(DateTate)+'] ' from (select distinct top 7 DateTate from CuttingPlan ) as a
set @sql=@sql+' from CuttingPlan c group by hyh, LargeBodyColorID,ColorID,SizeID union select hyh=''合计'',LargeBodyColorID='''',ColorID='''',SizeID='''',sumcol=sum(Quantity)'
select @sql=@sql+',sum(case DateTate when '''+RTRIM(DateTate)+''' then Quantity else 0 end) as ['+RTRIM(DateTate)+'] ' from (select distinct top 7 DateTate from CuttingPlan ) as b
set @sql=@sql+' from CuttingPlan as d '
exec(@sql)
print(@sql)
这个是第一个存储过程的结果
hyh LargeBodyColorID ColorID SizeID sumcol 10-10-12 10-10-13 10-10-14 10-10-15 10-10-16 10-10-17 10-10-18
1 浓芷青 A25EF1E3-2C3B-4F6D-B617-1901CCF75743 M 28 4 4 4 4 4 4 4
2 芷青 AE91D58F-2995-4309-A570-0BB5E947447C M 294 42 42 42 42 42 42 42
合计 322 46 46 46 46 46 46 46
这个是第二个存储过程的结果
hyh LargeBodyColorID ColorID SizeID sumcol 10-09-27 10-09-28 10-09-29 10-09-30 10-10-01 10-10-02 10-10-03
1 浓芷青 A25EF1E3-2C3B-4F6D-B617-1901CCF75743 M 156 4 4 4 4 4 4 4
2 芷青 AE91D58F-2995-4309-A570-0BB5E947447C M 1470 42 42 42 42 42 42 42
合计 1626 46 46 46 46 46 46 46
现在的问题两个存储过程查询出来的suncol是不一样的。
怎么能让第一个的存储过程的结果和第二个的结果是一样的。

...全文

153 10 打赏收藏转发到动态举报

写回复

用AI写文章

10 条回复

切换为时间正序

请发表友善的回复…

发表回复

jio891250 2010-10-30

打赏
举报

我想要的结果是根据条件查询出来的sumcol和查询全部的相同。

jio891250 2010-10-30

打赏
举报

恩。知道了。。谢谢您！！！根据您的回答问题已经解决了。
马上给分结贴。。。。。

SQLCenter 2010-10-30

打赏
举报

有
先不要条件查sumcol，再查其它，两个结果集联合，效率更差。

jio891250 2010-10-30

打赏
举报

不要条件的我知道的。需要条件的话。有其他办法实现没有啊？

SQLCenter 2010-10-30

打赏
举报

那你只能这样了，删除from里面的条件:

存储过程如下：
1.这个是根据时间查询的
declare @sql varchar(4000)
set @sql='select hyh,LargeBodyColorID,ColorID,SizeID,sum(Quantity) as sumcol '
select @sql=@sql+',sum(case DateTate when '''+RTRIM(DateTate)+''' then Quantity else 0 end) as ['+RTRIM(DateTate)+'] ' from (select distinct DateTate from CuttingPlan where DateTate<='10-10-18' and DateTate>='10-10-12') as a
set @sql=@sql+' from CuttingPlan c ~~where DateTate<=''10-10-18'' and DateTate>=''10-10-12''~~ group by hyh, LargeBodyColorID,ColorID,SizeID union select OrderID=''合计 '',LargeBodyColorID='''',ColorID='''',SizeID='''',sumcol=sum(Quantity)'
select @sql=@sql+',sum(case DateTate when '''+RTRIM(DateTate)+''' then Quantity else 0 end) as ['+RTRIM(DateTate)+'] ' from (select distinct DateTate from CuttingPlan where DateTate<='10-10-18' and DateTate>='10-10-12') as b
set @sql=@sql+' from CuttingPlan as d ~~where d.DateTate<=''10-10-18'' and d.DateTate>=''10-10-12''~~'
exec(@sql)
print(@sql)

jio891250 2010-10-30

打赏
举报

[Quote=引用 5 楼 sqlcenter 的回复:]
存储过程如下：
1.这个是根据时间查询的
declare @sql varchar(4000)

declare @sum int
select @sum = sum(Quantity) from CuttingPlan

set @sql='select hyh,LargeBodyColorID,ColorID,SizeID,sum(Quantity) as sumcol '
s……
[/Quote]
根据你的方法总数是对的。但是一行的数据还是不对的
结果如下的：
hyh sumcol
1 156
2 1470
合计 1626

SQLCenter 2010-10-30

打赏
举报

存储过程如下：
1.这个是根据时间查询的
declare @sql varchar(4000)

declare @sum int
select @sum = sum(Quantity) from CuttingPlan

set @sql='select hyh,LargeBodyColorID,ColorID,SizeID,sum(Quantity) as sumcol '
select @sql=@sql+',sum(case DateTate when '''+RTRIM(DateTate)+''' then Quantity else 0 end) as ['+RTRIM(DateTate)+'] ' from (select distinct DateTate from CuttingPlan where DateTate<='10-10-18' and DateTate>='10-10-12') as a
set @sql=@sql+' from CuttingPlan c where DateTate<=''10-10-18'' and DateTate>=''10-10-12'' group by hyh, LargeBodyColorID,ColorID,SizeID union select OrderID=''合计 '',LargeBodyColorID='''',ColorID='''',SizeID='''',sumcol='+ltrim(@sum)
select @sql=@sql+',sum(case DateTate when '''+RTRIM(DateTate)+''' then Quantity else 0 end) as ['+RTRIM(DateTate)+'] ' from (select distinct DateTate from CuttingPlan where DateTate<='10-10-18' and DateTate>='10-10-12') as b
set @sql=@sql+' from CuttingPlan as d where d.DateTate<=''10-10-18'' and d.DateTate>=''10-10-12'''
exec(@sql)
print(@sql)