这样的一个存储过程怎么写？

[caoit]

一个工资表：姓名（Name),工资月份(SalaryMonth(2004-07-01,代表七月份)，工资金额(Salary)


我想用存储过程建一个表，显示某人一年十个月的工资。
姓名 一 二 三 四 五
- - -

[leeyoong]

select Name,(case when 工资月份 = '2004-01-01' then Salary else 0 end) as '一'，
(case when 工资月份 = '2004-02-01' then Salary else 0 end) as '二'，
(case when 工资月份 = '2004-03-01' then Salary else 0 end) as '三'，
(case when 工资月份 = '2004-04-01' then Salary else 0 end) as '四'，
(case when 工资月份 = '2004-05-01' then Salary else 0 end) as '五'，
(case when 工资月份 = '2004-06-01' then Salary else 0 end) as '六'，
(case when 工资月份 = '2004-07-01' then Salary else 0 end) as '七'，
(case when 工资月份 = '2004-08-01' then Salary else 0 end) as '八'，
(case when 工资月份 = '2004-09-01' then Salary else 0 end) as '九'，
(case when 工资月份 = '2004-10-01' then Salary else 0 end) as '十'
from 工资表
where year(工资月份)='2004'

[pbsql]

select Name,
sum(case month(SalaryMonth) when 1 then Salary else 0 end) 一,
sum(case month(SalaryMonth) when 1 then Salary else 0 end) 二,
sum(case month(SalaryMonth) when 1 then Salary else 0 end) 三,
...
from t
group by Name

[zjcxc]

--1~10月? 如果是到12月,自己补上.

select 姓名=name
,一=sum(case month(SalaryMonth) when 1 then Salary else 0 end)
,二=sum(case month(SalaryMonth) when 2 then Salary else 0 end)
,三=sum(case month(SalaryMonth) when 3 then Salary else 0 end)
.......
,十=sum(case month(SalaryMonth) when 10 then Salary else 0 end)
from 工资表
group by name

[leeyoong]

select Name,sum(case when 工资月份 = '2004-01-01' then Salary else 0 end) as '一'，
sum(case when 工资月份 = '2004-02-01' then Salary else 0 end) as '二'，
sum(case when 工资月份 = '2004-03-01' then Salary else 0 end) as '三'，
sum(case when 工资月份 = '2004-04-01' then Salary else 0 end) as '四'，
sum(case when 工资月份 = '2004-05-01' then Salary else 0 end) as '五'，
sum(case when 工资月份 = '2004-06-01' then Salary else 0 end) as '六'，
sum(case when 工资月份 = '2004-07-01' then Salary else 0 end) as '七'，
sum(case when 工资月份 = '2004-08-01' then Salary else 0 end) as '八'，
sum(case when 工资月份 = '2004-09-01' then Salary else 0 end) as '九'，
sum(case when 工资月份 = '2004-10-01' then Salary else 0 end) as '十'
from 工资表
where year(工资月份)='2004'
group by name --sorry,少了一行

[caoit]

现问一个存储过程问题，解决马上结帐了！！
员工表：员工ID
员工工资表：员工ID，工资

我增加一条工资记录时，如何让员工表所有的员工都增加到工资表里，同样的工资记录？

[victorycyz]

不需要存储过程，用SQL语句就行了：

insert 员工工资表 (员工ID) select 员工ID from 员工表

不过，不建议这样处理。一般，有某人的工资记录，就添加一笔工资记录，不需要全部员工都加进来。

[caoit]

工资怎么加到每个员工身上了？

[caoit]

我要显示所有年年份的数据，你们说的只能显示2004年的

[victorycyz]

select 姓名=name
,年份=year(salarymonth)
,一=sum(case month(SalaryMonth) when 1 then Salary else 0 end)
,二=sum(case month(SalaryMonth) when 2 then Salary else 0 end)
,三=sum(case month(SalaryMonth) when 3 then Salary else 0 end)
.......
,十=sum(case month(SalaryMonth) when 10 then Salary else 0 end)
from 工资表
group by name,year(salarymonth)

[zjcxc]

select 姓名=name,年份=year(SalaryMonth)
,一=sum(case month(SalaryMonth) when 1 then Salary else 0 end)
,二=sum(case month(SalaryMonth) when 2 then Salary else 0 end)
,三=sum(case month(SalaryMonth) when 3 then Salary else 0 end)
,四=sum(case month(SalaryMonth) when 4 then Salary else 0 end)
,五=sum(case month(SalaryMonth) when 5 then Salary else 0 end)
,六=sum(case month(SalaryMonth) when 6 then Salary else 0 end)
,七=sum(case month(SalaryMonth) when 7 then Salary else 0 end)
,八=sum(case month(SalaryMonth) when 8 then Salary else 0 end)
,九=sum(case month(SalaryMonth) when 9 then Salary else 0 end)
,十=sum(case month(SalaryMonth) when 10 then Salary else 0 end)
,十一=sum(case month(SalaryMonth) when 11 then Salary else 0 end) --如果只到10月,这条及下条不要
,十二=sum(case month(SalaryMonth) when 12 then Salary else 0 end)
from 工资表
group by name,year(SalaryMonth)

[caoit]

up

[huwgao]

--根据给定工资直接就加到每一个人上
declare @工资 money
insert 员工工资表 (员工ID,工资) select 员工ID,@工资 from 员工表

[caoit]

up