--尽量自己学着理解,自己去写。declare @sql varchar(8000) set @sql='select bm' select @sql=@sql+',['+km+']=sum(case km when '''+km+''' then fy else 0 end)' from (select distinct km from 表名)a set @sql=@sql+' from 表名 group by bm' exec(@sql)
科目是动态的,科目是动态的费用如何归结?不做二级科目,做核算项目不也是固定的!!!DECLARE @tab TABLE([id] int,[time] datetime,bm varchar(20),fy decimal(18,4),km varchar(20))INSERT INTO @tab([id],[time],bm,fy,km) SELECT 1,'2010-01-01','行政部',100,'550201' UNION all SELECT 2,'2010-01-02','行政部',200,'550202' UNION all SELECT 3,'2010-01-03','市场部',300,'550204' UNION all SELECT 4,'2001-01-04','市场部',400,'550203'DECLARE @t1 DATETIME DECLARE @t2 DATETIME SET @t1 = '2010-01-01' SET @t2 = '2010-01-31' DECLARE @dd1 INT DECLARE @dd2 INT SET @dd1=YEAR(@t1)*365+MONTH(@t1)*30+DAY(@t1) SET @dd2=YEAR(@t2)*365+MONTH(@t2)*30+DAY(@t2)SELECT ttt.bm AS 部门,ISNULL(ttt.办公费,0) AS 办公费,ISNULL(ttt.耗材费,0) AS 耗材费,ISNULL(ttt.水电费,0) 水电费,ISNULL(ttt.税费,0) AS 税费,(ISNULL(ttt.办公费,0) + ISNULL(ttt.耗材费,0) + ISNULL(ttt.水电费,0) + ISNULL(ttt.税费,0)) AS 合计 FROM ( SELECT tt.bm,SUM(tt.办公费) AS 办公费,SUM(tt.耗材费) AS 耗材费,SUM(tt.水电费) AS 水电费,SUM(tt.税费) AS 税费 FROM ( SELECT t.id,t.time,t.bm,t.km, CASE t.km WHEN '550201' THEN t.fy END AS 办公费, CASE t.km WHEN '550202' THEN t.fy END AS 耗材费, CASE t.km WHEN '550203' THEN t.fy END AS 水电费, CASE t.km WHEN '550204' THEN t.fy END AS 税费 FROM @tab AS t ) AS tt WHERE (YEAR(tt.time)*365+MONTH(tt.time)*30+DAY(tt.time)) >= @dd1 AND (YEAR(tt.time)*365+MONTH(tt.time)*30+DAY(tt.time)) <= @dd2 GROUP BY tt.bm ) AS ttt/* (所影响的行数为 4 行)部门 办公费 耗材费 水电费 税费 合计 -------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- 市场部 .0000 .0000 .0000 300.0000 300.0000 行政部 100.0000 200.0000 .0000 .0000 300.0000(所影响的行数为 2 行)*/
-- 开始时间和结束时间 declare @s datetime, @e datetime;declare @stmt nvarchar(4000); set @stmt=N'select [bm] [部门]'; select @stmt=@stmt+N',sum(case [km] when '''+[km]+N''' then [fy] else 0 end) ['+[km]+']' from @tb group by [km]; set @stmt=@stmt+N',sum([fy]) [合计] from tb where [time] between @start and @end group by [bm]';-- print @stmt; exec sp_executesql @stmt, N'@start datetime, @end datetime', @s, @e;
Create procedure Countfy declare @stime varchar(30), declare @etime varchar(30) as declare @sql varchar(8000) set @sql='select bm' select @sql=@sql+',['+cashxm+']=sum(case cashxm when '''+cashxm+''' then bxje else 0 end)' from (select distinct cashxm from fybxinfo)aset @sql=@sql+'from fybxinfo where bxtime>='''+@stime+''' and '''+@etime+''' group by bm' exec(@sql)go我写的存储过程怎么运行报错 求高手指教
--尽量自己学着理解,自己去写。declare @sql varchar(8000)
set @sql='select bm'
select @sql=@sql+',['+km+']=sum(case km when '''+km+''' then fy else 0 end)'
from (select distinct km from 表名)a
set @sql=@sql+' from 表名 group by bm'
exec(@sql)
SELECT 1,'2010-01-01','行政部',100,'550201' UNION all
SELECT 2,'2010-01-02','行政部',200,'550202' UNION all
SELECT 3,'2010-01-03','市场部',300,'550204' UNION all
SELECT 4,'2001-01-04','市场部',400,'550203'DECLARE @t1 DATETIME
DECLARE @t2 DATETIME
SET @t1 = '2010-01-01'
SET @t2 = '2010-01-31'
DECLARE @dd1 INT
DECLARE @dd2 INT
SET @dd1=YEAR(@t1)*365+MONTH(@t1)*30+DAY(@t1)
SET @dd2=YEAR(@t2)*365+MONTH(@t2)*30+DAY(@t2)SELECT ttt.bm AS 部门,ISNULL(ttt.办公费,0) AS 办公费,ISNULL(ttt.耗材费,0) AS 耗材费,ISNULL(ttt.水电费,0) 水电费,ISNULL(ttt.税费,0) AS 税费,(ISNULL(ttt.办公费,0) + ISNULL(ttt.耗材费,0) + ISNULL(ttt.水电费,0) + ISNULL(ttt.税费,0)) AS 合计
FROM
(
SELECT tt.bm,SUM(tt.办公费) AS 办公费,SUM(tt.耗材费) AS 耗材费,SUM(tt.水电费) AS 水电费,SUM(tt.税费) AS 税费
FROM
(
SELECT t.id,t.time,t.bm,t.km,
CASE t.km WHEN '550201' THEN t.fy END AS 办公费,
CASE t.km WHEN '550202' THEN t.fy END AS 耗材费,
CASE t.km WHEN '550203' THEN t.fy END AS 水电费,
CASE t.km WHEN '550204' THEN t.fy END AS 税费
FROM @tab AS t
) AS tt
WHERE (YEAR(tt.time)*365+MONTH(tt.time)*30+DAY(tt.time)) >= @dd1 AND (YEAR(tt.time)*365+MONTH(tt.time)*30+DAY(tt.time)) <= @dd2
GROUP BY tt.bm
) AS ttt/*
(所影响的行数为 4 行)部门 办公费 耗材费 水电费 税费 合计
-------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ---------------------------------------- ----------------------------------------
市场部 .0000 .0000 .0000 300.0000 300.0000
行政部 100.0000 200.0000 .0000 .0000 300.0000(所影响的行数为 2 行)*/
-- 开始时间和结束时间
declare @s datetime, @e datetime;declare @stmt nvarchar(4000);
set @stmt=N'select [bm] [部门]';
select @stmt=@stmt+N',sum(case [km] when '''+[km]+N''' then [fy] else 0 end) ['+[km]+']' from @tb group by [km];
set @stmt=@stmt+N',sum([fy]) [合计] from tb where [time] between @start and @end group by [bm]';-- print @stmt;
exec sp_executesql @stmt, N'@start datetime, @end datetime', @s, @e;
Create procedure Countfy
declare @stime varchar(30),
declare @etime varchar(30)
as
declare @sql varchar(8000)
set @sql='select bm'
select @sql=@sql+',['+cashxm+']=sum(case cashxm when '''+cashxm+''' then bxje else 0 end)'
from (select distinct cashxm from fybxinfo)aset @sql=@sql+'from fybxinfo where bxtime>='''+@stime+''' and '''+@etime+''' group by bm'
exec(@sql)go我写的存储过程怎么运行报错 求高手指教