实现等级的存储过程sql

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

insert UserInfo
11

values(1,'wt','123',1500)
12

insert UserInfo
13

values (2,'aa','569',50)
14

insert UserInfo
16

values(3,'ad','56',160)
17

insert UserInfo
18

values(4,'ad','99',806)
19

insert UserInfo
20

values(5,'df','656',656)
21

insert UserInfo
22

values(6,'adsf','adf',1502)
23

create table GradeUser
24

(
25

GradeUserID int primary key not null,
26

GradeUserName varchar(20),
27

Orders int
28

)
29

insert GradeUser
30

values(1,'小学',0)
31

insert GradeUser
32

values (2,'中学',300)
33

insert GradeUser
35

values(3,'大学',800)
36

insert GradeUser
37

values(4,'博士',1500)
38

insert GradeUser
39

values(5,'博导',15000)
40

go
41

---------------
43

Create proc selectUserInfo
45

@UserID int
46

as
47

set nocount on
48

begin
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
54

end
55

go
56

exec selectUserInfo 2
58

posted @ 2006-12-30 13:44 突破666 阅读(306) 评论(0) 编辑收藏举报

刷新页面返回顶部

突破2018

小程序、微信公众平台、移动开发、中小企业信息化

实现等级的存储过程sql