文件名称：批量过程SQL 相同部门中年龄最大 廷时 分页
文件大小：3KB
文件格式：SQL
更新时间：2014-04-12 16:12:29
product 过程 相同部门中年龄最大 廷时
create table user_info ( userid int identity(1,1) not null, --序号 userName varchar(50) not null, --姓名 sex varchar(4) , --性别 userNo varchar(50) null, --身份证号 userXieli varchar(20) null,--学历 style varchar(50) null,--类型 speciality varchar(50) null, --专业 tel varchar(50) null, --联系电话 city varchar(50) null, --意向城市 job varchar(50) null, --意向岗位 othreJob varchar(50) null,--其它岗位 salary varchar(50) null, --薪资要求 art varchar(50) null, ---专业特长 superemacy varchar(200) null, --优势 remark varchar(500) null, --备注 primary key (userid) ) create proc sp_add_userInfo as declare @userName varchar(50) --姓名 declare @sex varchar(4) --性别 declare @userNo varchar(50) --身份证号 declare @userXieli varchar(20) --学历 declare @style varchar(50) --类型 declare @speciality varchar(50) --专业 declare @tel varchar(50) --联系电话 declare @city varchar(50) --意向城市 declare @job varchar(50) --意向岗位 declare @othreJob varchar(50) --其它岗位 declare @salary varchar(50) --薪资要求 declare @art varchar(50) ---专业特长 declare @superemacy varchar(200) --优势 declare @remark varchar(500) declare @count integer declare @index integer declare @userID varchar(200) set @index = 0 set @count = 20 while @index < @count begin insert into user_info( userName , sex, userNo, userXieli, style, speciality, tel, city, job, othreJob, salary, art, superemacy, remark ) values ( @userName , @sex, @userNo, @userXieli, @style, @speciality, @tel, @city, @job, @othreJob, @salary, @art, @superemacy, @remark ) set @index = @index+1 end go CREATE PROCEDURE add_UserInfo AS DECLARE @userCode VARCHAR(30) DECLARE @userName VARCHAR(30) DECLARE @userCode_base VARCHAR(30) DECLARE @count INTEGER DECLARE @index INTEGER DECLARE @rand1 INTEGER DECLARE @rand2 INTEGER SET @userCode_base='qs_' SET @userName='userName' SET @count=100000 SET @index=10000 WHILE @index<@count BEGIN SET @userCode=@userCode_base+CONVERT(VARCHAR,@index) SET @rand1=convert(int,rand()*5) SET @rand2=convert(int,rand()*5) INSERT INTO userInfo (userCode,roleType,groupID,userName,text1,text2,text3) VALUES (@userCode,@rand1,@rand2,@userName,'aokei kaol jof','','aokei kaol jof') SET @index=@index+1 END GO DECLARE @MyCounter INT SET @MyCounter = 0 /*设置变量*/ WHILE (@MyCounter < 2) /*设置循环的次数*/ BEGIN WAITFOR DELAY '000:00:10' /*延时10秒*/ INSERT INTO time_by_day (time_id, the_date, the_year, month_of_year, quarter, day_of_month) SELECT TOP 1 time_id + 1 AS time_id, the_date + 1 AS the_date, YEAR(the_date + 1) AS the_year, MONTH(the_date + 1) AS month_of_year, { fn QUARTER(the_date + 1) } AS quarter, DAY(the_date + 1) AS day_of_month FROM time_by_day ORDER BY time_id DESC SET @MyCounter = @MyCounter + 1 END select userid insert into select * from person; select * insert into person values('a','b',20,'110') select * from emp where hiredate in ( select hiredate from emp group by hiredate,deptno having count(*)>1 ) and deptno in (select deptno from emp group by hiredate,deptno having count(*)>1) select rownum,empno,ename from emp where rownum<11 minus select rownum,empno,ename from emp where rownum<6 查询表中相同部门中年龄最大的职员信息 select * from person2 p, (select max(age) age,dept from person2 group by dept)g where p.age = g.age and p.dept = g.dept