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
AS
declare @cmd varchar(8000)
set @Cmd = "SELECT Emp_Name, "
declare @Num int
select @Num=count(id) from Items
declare @i int
set @i=1
while @i <@Num
begin
declare @name varchar(100)
select @name=Name from Items where [email protected]
set @Cmd = @Cmd + 'SUM( '+ @name + ') AS '+ @name
if (@i <> @Num)
set @[email protected] + ', '
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(@[email protected])
set @[email protected] + 'Money AS '[email protected]
else
set @[email protected] + '0 AS '[email protected]
if(@j <> @Num)
set @[email protected] + ', '
end
set @Cmd = @Cmd + ' FROM V_WagesDetail WHERE (Item_id = ' + @i + ') and [id] > = start_id and [id] <= end_id '