34,590
社区成员
发帖
与我相关
我的任务
分享
--这是积分表
RankName RankCondition
VIP会员 500
高级会员 200
普通会员 0
--这是用户表
UserName Scores
陈小羽 550
李凯特 400
李丽 30
--我现在写的这个查询语句会重复出现,也就是说如果用户是高等级的他同时会拥有了低等级
select u.*,sr.RankName, from tb_Users u,tb_ScoreRanks sr where u.Scores >= sr.RankCondition
select
*,
(select top 1 [RankNaem] from [tb_ScoreRanks] where [RankCondition]<=t.[Scores] order by [RankCondition] desc) as RankNaem
from
[tb_Users] t
select
b.UserName, b.Scores,
case when b.Scores>=500 then 'VIP会员'
when b.socres>=200 and b.scores<500 then '高级会员'
else '普通会员' end
from
tb_ScoreRanks a,tb_Users b
where
..
--> 测试数据:[tb_ScoreRanks]
if object_id('[tb_ScoreRanks]') is not null drop table [tb_ScoreRanks]
create table [tb_ScoreRanks]([RankNaem] varchar(8),[RankCondition] int)
insert [tb_ScoreRanks]
select 'VIP会员',500 union all
select '高级会员',200 union all
select '普通会员',0
--> 测试数据:[tb_Users]
if object_id('[tb_Users]') is not null drop table [tb_Users]
create table [tb_Users]([UserName] varchar(6),[Scores] int)
insert [tb_Users]
select '陈小羽',550 union all
select '李凯特',400 union all
select '李丽',30
select
t.*,
(select top 1 [RankNaem] from [tb_ScoreRanks] where [RankCondition]<=t.[Scores] order by [RankCondition] desc) as RankNaem
from [tb_Users] t
--------------------------
陈小羽 550 VIP会员
李凯特 400 高级会员
李丽 30 普通会员