DECLARE @sql VARCHAR(8000)
DECLARE @str VARCHAR(8000)
SELECT @sql = ISNULL(@sql , '') + N' sum(case when jhdanwei = N''' + jhdanwei + ''' then shuliang else 0 end) as [' +jhdanwei+N'|数量]'
+N' , sum(case when jhdanwei = N''' + jhdanwei + N''' then ypjine else 0 end) as ['+jhdanwei+N'|金额]'+ N',' FROM (select distinct jhdanwei from Y_xskehu where diqu like '%'''+@日期1+'''%' and jhdanwei like '%'''+@日期2+'''%') t
SET @sql = 'SELECT y_baseinfo.cpid , y_baseinfo.yppinming,'+ left(@sql , LEN(@sql) - 1 ) + N',
ISNULL(sum(shuliang),0) as [合计|数量],
ISNULL(sum(ypjine),0) as [合计|金额]
from (select cpid,yppinming,jinhuodanwei as jhdanwei,shuliang,ypjine from y_liushui where caozuobiaoshi<>''入库'' and convert(varchar(100),caozuoriqi,23)>=''2014-01-01'' and convert(varchar(100),caozuoriqi,23)<=''2014-05-01'') t
right join y_baseinfo on t.cpid = y_baseinfo.cpid group by y_baseinfo.cpid,y_baseinfo.yppinming '
EXEC (@sql)
试试这个
DECLARE @str VARCHAR(8000)
SELECT @sql = ISNULL(@sql , '') + N' sum(case when jhdanwei = N''' + jhdanwei + ''' then shuliang else 0 end) as [' +jhdanwei+N'|数量]'
+N' , sum(case when jhdanwei = N''' + jhdanwei + N''' then ypjine else 0 end) as ['+jhdanwei+N'|金额]'+ N',' FROM (select distinct jhdanwei from Y_xskehu where diqu like '%'''+@日期1+'''%' and jhdanwei like '%'''+@日期2+'''%') t
SET @sql = 'SELECT y_baseinfo.cpid , y_baseinfo.yppinming,'+ left(@sql , LEN(@sql) - 1 ) + N',
ISNULL(sum(shuliang),0) as [合计|数量],
ISNULL(sum(ypjine),0) as [合计|金额]
from (select cpid,yppinming,jinhuodanwei as jhdanwei,shuliang,ypjine from y_liushui where caozuobiaoshi<>''入库'' and convert(varchar(100),caozuoriqi,23)>=''2014-01-01'' and convert(varchar(100),caozuoriqi,23)<=''2014-05-01'') t
right join y_baseinfo on t.cpid = y_baseinfo.cpid group by y_baseinfo.cpid,y_baseinfo.yppinming '
EXEC (@sql)
试试这个
解决方案 »
- 查询年龄小于30的病人,降序排序,女性如何排在前面
- 一条SQL语句 谢谢
- 崩溃了,再次请教如何查询出表中nvarchar数据类型字符串相同,并且大于一定数量的数据?的问题
- 触发器问题,紧急求助~~~~~~~~~
- 急,请高手帮助解析下:想把SQL存储过程中的where语句替换为参数传递!
- FTP搜寻.如果这个FTP网站的文件很多,有什么办法提高速度?
- 请问我要实现这样的功能,SQL怎么实现!
- 有啥方法把导出来的数据文件中在第一行加标题字段?
- 请问sql server 2005 通过链接服务器 联到oracle 9i ,如果能提高速度!
- 在触发器中建表时,相用字符串变量作为表名怎么办?
- 用SQL server 2008创建数据库时出现了文件激活错误
- 触发器问题
IF EXISTS (SELECT name FROM sysobjects
WHERE name = 'jieguo' AND type = 'P')
DROP PROCEDURE jieguo
go
IF EXISTS(SELECT NAME FROM sysobjects AS o WHERE NAME = 'T_jiamengdiantongji')
DROP TABLE T_jiamengdiantongji
GOcreate procedure jieguo
@地区 varchar(50),
@单位 varchar(50)
--@日期1 datetime(),
--@日期2 datetime()
as
DECLARE @sql VARCHAR(8000)
DECLARE @str VARCHAR(8000)
SELECT @sql = ISNULL(@sql , '') + N' sum(case when jhdanwei = N''' + jhdanwei + ''' then shuliang else 0 end) as [' +jhdanwei+N'|数量]'
+N' , sum(case when jhdanwei = N''' + jhdanwei + N''' then ypjine else 0 end) as ['+jhdanwei+N'|金额]'+ N',' FROM (select distinct jhdanwei from Y_xskehu where diqu like '%'''+@地区+'''%' and jhdanwei like '%'''+@单位+'''%') t
SET @sql = 'SELECT y_baseinfo.cpid , y_baseinfo.yppinming,'+ left(@sql , LEN(@sql) - 1 ) + N',
ISNULL(sum(shuliang),0) as [合计|数量],
ISNULL(sum(ypjine),0) as [合计|金额]
from (select cpid,yppinming,jinhuodanwei as jhdanwei,shuliang,ypjine from y_liushui where caozuobiaoshi<>''产品入库'' and convert(varchar(100),caozuoriqi,23)>=''2014-01-01'' and convert(varchar(100),caozuoriqi,23)<=''2014-05-01'') t
right join y_baseinfo on t.cpid = y_baseinfo.cpid group by y_baseinfo.cpid,y_baseinfo.yppinming '
--EXEC (@sql)set @str= 'select * into T_jiamengdiantongji from ('+@sql+') a '
exec(@str)
select * from T_jiamengdiantongji
goEXECUTE jieguo '北京','大兴店'