IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME='tbl_User_Level')
DROP TABLE tbl_User_Level
GO
CREATE TABLE [dbo].[tbl_User_Level]
(
[LevelID] [int] IDENTITY(1,1) NOT NULL,
[LevelName] [varchar](20) NOT NULL,
[RequiredPoint] [int] NOT NULL
PRIMARY KEY CLUSTERED
(
[LevelID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]GO
IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME='tbl_User_Users')
DROP TABLE tbl_User_Users
GO
CREATE TABLE [dbo].[tbl_User_Users]
(
[UserID] [uniqueidentifier] NOT NULL DEFAULT NEWSEQUENTIALID(),
[UserName] [varchar](50) NOT NULL,
[SavePoint] [int] NOT NULL DEFAULT 0
PRIMARY KEY CLUSTERED
(
[UserID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]GOINSERT INTO [dbo].[tbl_User_Level]([LevelName],[RequiredPoint])
VALUES ('FIRST',0)
INSERT INTO [dbo].[tbl_User_Level]([LevelName],[RequiredPoint])
VALUES ('SECOND',,100)
INSERT INTO [dbo].[tbl_User_Level]([LevelName],[RequiredPoint])
VALUES ('THIRD',1000)
INSERT INTO [dbo].[tbl_User_Level]([LevelName],[RequiredPoint])
VALUES ('FOURTH',,10000)
INSERT INTO [dbo].[tbl_User_Users] ([UserName],[SavePoint])
VALUES ('admin',15)
INSERT INTO [dbo].[tbl_User_Users] ([UserName],[SavePoint])
VALUES ('rose',100)savepoint是用户实际积分点,RequiredPoint是当你达到这个积分点,那么你就是相应等级的用户,
现在我想查找每一个用户对应的等级名称,
用sql脚本该怎么写?
DROP TABLE tbl_User_Level
GO
CREATE TABLE [dbo].[tbl_User_Level]
(
[LevelID] [int] IDENTITY(1,1) NOT NULL,
[LevelName] [varchar](20) NOT NULL,
[RequiredPoint] [int] NOT NULL
PRIMARY KEY CLUSTERED
(
[LevelID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]GO
IF EXISTS (SELECT * FROM SYSOBJECTS WHERE NAME='tbl_User_Users')
DROP TABLE tbl_User_Users
GO
CREATE TABLE [dbo].[tbl_User_Users]
(
[UserID] [uniqueidentifier] NOT NULL DEFAULT NEWSEQUENTIALID(),
[UserName] [varchar](50) NOT NULL,
[SavePoint] [int] NOT NULL DEFAULT 0
PRIMARY KEY CLUSTERED
(
[UserID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]GOINSERT INTO [dbo].[tbl_User_Level]([LevelName],[RequiredPoint])
VALUES ('FIRST',0)
INSERT INTO [dbo].[tbl_User_Level]([LevelName],[RequiredPoint])
VALUES ('SECOND',,100)
INSERT INTO [dbo].[tbl_User_Level]([LevelName],[RequiredPoint])
VALUES ('THIRD',1000)
INSERT INTO [dbo].[tbl_User_Level]([LevelName],[RequiredPoint])
VALUES ('FOURTH',,10000)
INSERT INTO [dbo].[tbl_User_Users] ([UserName],[SavePoint])
VALUES ('admin',15)
INSERT INTO [dbo].[tbl_User_Users] ([UserName],[SavePoint])
VALUES ('rose',100)savepoint是用户实际积分点,RequiredPoint是当你达到这个积分点,那么你就是相应等级的用户,
现在我想查找每一个用户对应的等级名称,
用sql脚本该怎么写?
等级 = (select top 1 levelid from tbl_User_Level b where b.[RequiredPoint]>a.[SavePoint] order by b.[RequiredPoint] asc)
from tbl_User_Users a
-- 结果
A8479CB9-CCA0-E111-99CC-00219B6F7DCA admin 15 2
A9479CB9-CCA0-E111-99CC-00219B6F7DCA rose 100 3
select a.* ,
select a.* ,
等级 = (select top 1 levelid from tbl_User_Level b where b.[RequiredPoint]<=a.[SavePoint] order by b.[RequiredPoint] desc)
from tbl_User_Users a
-- 结果
A8479CB9-CCA0-E111-99CC-00219B6F7DCA admin 15 2
A9479CB9-CCA0-E111-99CC-00219B6F7DCA rose 100 2
因为没写明具体的等级情况,lz适当修改 b.[RequiredPoint]<=a.[SavePoint] 中的<=,<,>,>=等应该就可以了。