----使用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 |