实现等级的存储过程sql
1
create table UserInfo
2(
3 UserID int primary key not null,
4 UserName varchar(30),
5 UserPass varchar(30),
6 Orders int
7)
8-------
9
10insert UserInfo
11values(1,'wt','123',1500)
12insert UserInfo
13values (2,'aa','569',50)
14
15insert UserInfo
16values(3,'ad','56',160)
17insert UserInfo
18values(4,'ad','99',806)
19insert UserInfo
20values(5,'df','656',656)
21insert UserInfo
22values(6,'adsf','adf',1502)
23create table GradeUser
24(
25 GradeUserID int primary key not null,
26 GradeUserName varchar(20),
27 Orders int
28)
29insert GradeUser
30values(1,'小学',0)
31insert GradeUser
32values (2,'中学',300)
33
34insert GradeUser
35values(3,'大学',800)
36insert GradeUser
37values(4,'博士',1500)
38insert GradeUser
39values(5,'博导',15000)
40go
41
42---------------
43
44Create proc selectUserInfo
45@UserID int
46as
47set nocount on
48begin
49 select top 1 u.UserName,u.Orders,g.GradeUserName
50 from UserInfo u INNER JOIN GradeUser g
51 ON u.Orders >= g.Orders
52 where u.UserID=@UserID
53 order by g.GradeUserID desc
54end
55go
56
57exec selectUserInfo 2
58
create table UserInfo2
(3
UserID int primary key not null,4
UserName varchar(30),5
UserPass varchar(30),6
Orders int 7
)8
-------9
10
insert UserInfo 11
values(1,'wt','123',1500)12
insert UserInfo13
values (2,'aa','569',50)14
15
insert UserInfo16
values(3,'ad','56',160)17
insert UserInfo18
values(4,'ad','99',806)19
insert UserInfo20
values(5,'df','656',656)21
insert UserInfo22
values(6,'adsf','adf',1502)23
create table GradeUser24
(25
GradeUserID int primary key not null,26
GradeUserName varchar(20),27
Orders int 28
)29
insert GradeUser 30
values(1,'小学',0)31
insert GradeUser32
values (2,'中学',300)33
34
insert GradeUser35
values(3,'大学',800)36
insert GradeUser37
values(4,'博士',1500)38
insert GradeUser39
values(5,'博导',15000)40
go41
42
---------------43
44
Create proc selectUserInfo45
@UserID int 46
as47
set nocount on48
begin49
select top 1 u.UserName,u.Orders,g.GradeUserName50
from UserInfo u INNER JOIN GradeUser g 51
ON u.Orders >= g.Orders 52
where u.UserID=@UserID 53
order by g.GradeUserID desc54
end 55
go 56
57
exec selectUserInfo 258
为成功找方法,不为失败找借口!
