取代游标的方案

博客涉及日期、表格及插入相关内容,但具体信息缺失。推测可能围绕在信息技术中对日期的处理、表格的运用以及插入操作展开。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

----使用min()函数或max()函数--------------------
declare @emp_id varchar(15),@name varchar(50),@job_id int,@hire_date datetime
set @emp_id=(select min(emp_id) from pubs.dbo.employee where minit is not null)
while 1=1
begin
select @name=fname+lname,@job_id=job_id,@hire_date=hire_date from pubs.dbo.employee where emp_id=@emp_id
print @name+' , '+cast(@job_id as varchar(5))+' , '+convert(varchar(10),@hire_date,20)
select @emp_id=min(emp_id) from pubs.dbo.employee where minit is not null and emp_id>@emp_id
if @emp_id is null break
end

 

-----用表函数------------
declare @count int,@n int,@emp_id varchar(15),@name varchar(50),@job_id int,@hire_date datetime
declare @employee table(
id int identity(1,1),
emp_id varchar(15),
name varchar(50),
job_id int,
hire_date datetime
)
insert @employee
select emp_id,fname+lname as name,job_id,hire_date from pubs.dbo.employee where minit is not null
set @n=1
set @count=(select max(id) from @employee)
while @n<=@count
begin
select @name=name,@job_id=job_id,@hire_date=hire_date from @employee where id=@n
print @name+' , '+cast(@job_id as varchar(5))+' , '+convert(varchar(10),@hire_date,20)
set @n=@n+1
end

 

------使用临时表-------------------
declare @count int,@n int,@emp_id varchar(15),@name varchar(50),@job_id int,@hire_date datetime
select identity(int,1,1) as id,emp_id,fname+lname as name,job_id,hire_date into #t from pubs.dbo.employee where minit is not null
set @n=1
set @count=(select max(id) from #t)
while @n<=@count
begin
select @name=name,@job_id=job_id,@hire_date=hire_date from #t where id=@n
print @name+' , '+cast(@job_id as varchar(5))+' , '+convert(varchar(10),@hire_date,20)
set @n=@n+1
end
drop table #t
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值