34,838
社区成员




--汇总查询全部信息
select tb3.*,(cast(tb3.年休假天数 as int)-cast(tb3.实休 as int)) as 剩余
from
(select distinct tb1.姓名,tb1.科室,tb1.年休假天数,tb2.*
from
(select * from ciq_Vacation) as tb1,
(select 员工编号,
sum(case when 休假种类='年假' then isnull(实际休假天数,计划休假天数) else 0 end) as 实休,
sum(case when 休假种类='病假' then isnull(实际休假天数,计划休假天数) else 0 end) as 病假,
sum(case when 休假种类='事假' then isnull(实际休假天数,计划休假天数) else 0 end) as 事假,
sum(case when 休假种类='产假' then isnull(实际休假天数,计划休假天数) else 0 end) as 产假,
sum(case when 休假种类='婚假' then isnull(实际休假天数,计划休假天数) else 0 end) as 婚假,
sum(case when 休假种类='丧假' then isnull(实际休假天数,计划休假天数) else 0 end) as 丧假,
sum(case when 休假种类='探望父母' then isnull(实际休假天数,计划休假天数) else 0 end) as 探望父母,
sum(case when 休假种类='探望配偶' then isnull(实际休假天数,计划休假天数) else 0 end) as 探望配偶,
sum(case when 休假种类='其他' then isnull(实际休假天数,计划休假天数) else 0 end) as 其他
from ciq_Vacation
group by 员工编号) as tb2
where tb1.员工编号=tb2.员工编号) tb3
order by tb3.员工编号
if object_id('log20090925')is not null drop table log20090925
go
create table log20090925( a int, b int,c int )
insert log20090925 select
1,3,4 union all select
1,2,2
-------------------------------------------------
if object_id('pro')is not null drop proc pro
go
create proc pro
(@b int )
as
select * from log20090925 where b=@b
go
exec pro 2 ---- 这里传入一个参数 ,找到列b=2的记录
a b c
----------- ----------- -----------
1 2 2
(1 行受影响)
create proc pp
---这里可以带参数
as
--select 语句
go
-------------
insert tb exec pp 把结果放到表tb里