SELECT Emp_Name, SUM(基本工资) AS 基本工资, SUM(奖金) AS 奖金, SUM(水电费)
AS 水电费
FROM (
SELECT Emp_Name, Money AS 基本工资, 0 AS 奖金, 0 AS 水电费
FROM V_WagesDetail
WHERE (Item_id = 1)
UNION ALL
SELECT Emp_Name, 0 AS 基本工资, Money AS 奖金, 0 AS 水电费
FROM V_WagesDetail
WHERE (Item_id = 2)
UNION ALL
SELECT Emp_Name, 0 AS 基本工资, 0 AS 奖金, Money AS 水电费
FROM V_WagesDetail
WHERE (Item_id = 3)) a
GROUP BY Emp_Name
create procedure P_QueryWages @Start_ID int, @End_ID int
ASdeclare @cmd varchar(8000)
set @Cmd = "SELECT Emp_Name, "declare @Num int
select @Num=count(id) from Itemsdeclare @i int
set @i=1
while @i<@Num
begin
declare @name varchar(100)
select @name=Name from Items where id=@i
set @Cmd = @Cmd +'SUM('+ @name +') AS '+ @name
if (@i<>@Num)
set @Cmd=@Cmd + ','
end
set @Cmd = @Cmd + 'FROM (SELECT Emp_Name, 'set @i=1
while @i<@Num
begin
declare @j int
set @j=1
while @j<@Num
begin
declare @name1 varchar(100)
select @name1 = Name from Items where id= @j
if(@i==@j)
set @Cmd=@Cmd + 'Money AS'+@name1
else
set @Cmd=@Cmd + '0 AS '+@name1
if(@j<>@Num)
set @Cmd=@Cmd + ','
end
set @Cmd = @Cmd + ' FROM V_WagesDetail WHERE (Item_id = ' + @i + ') and [id] >= start_id and [id] <= end_id'
if (@i<>@Num)
set @Cmd =@Cmd + 'union all '
end
set @Cmd = @Cmd + ') a GROUP BY Emp_Name '
exec(@Cmd)
AS 水电费
FROM (
SELECT Emp_Name, Money AS 基本工资, 0 AS 奖金, 0 AS 水电费
FROM V_WagesDetail
WHERE (Item_id = 1)
UNION ALL
SELECT Emp_Name, 0 AS 基本工资, Money AS 奖金, 0 AS 水电费
FROM V_WagesDetail
WHERE (Item_id = 2)
UNION ALL
SELECT Emp_Name, 0 AS 基本工资, 0 AS 奖金, Money AS 水电费
FROM V_WagesDetail
WHERE (Item_id = 3)) a
GROUP BY Emp_Name
create procedure P_QueryWages @Start_ID int, @End_ID int
ASdeclare @cmd varchar(8000)
set @Cmd = "SELECT Emp_Name, "declare @Num int
select @Num=count(id) from Itemsdeclare @i int
set @i=1
while @i<@Num
begin
declare @name varchar(100)
select @name=Name from Items where id=@i
set @Cmd = @Cmd +'SUM('+ @name +') AS '+ @name
if (@i<>@Num)
set @Cmd=@Cmd + ','
end
set @Cmd = @Cmd + 'FROM (SELECT Emp_Name, 'set @i=1
while @i<@Num
begin
declare @j int
set @j=1
while @j<@Num
begin
declare @name1 varchar(100)
select @name1 = Name from Items where id= @j
if(@i==@j)
set @Cmd=@Cmd + 'Money AS'+@name1
else
set @Cmd=@Cmd + '0 AS '+@name1
if(@j<>@Num)
set @Cmd=@Cmd + ','
end
set @Cmd = @Cmd + ' FROM V_WagesDetail WHERE (Item_id = ' + @i + ') and [id] >= start_id and [id] <= end_id'
if (@i<>@Num)
set @Cmd =@Cmd + 'union all '
end
set @Cmd = @Cmd + ') a GROUP BY Emp_Name '
exec(@Cmd)
第 31 行: '=' 附近有语法错误。
服务器: 消息 156,级别 15,状态 1,过程 P_QueryWages,行 33
在关键字 'else' 附近有语法错误。