我这样写结果出现错误,另外最大值的ColumnID也不知怎么选出来:
SELECT a.PKID,
a.UserName,
a.NickName,
UserLevelUp=select MAX(CASE WHEN (ISNULL(d.Score,0) < 100) THEN 1
WHEN (d.Score >= 100 and d.Score < 500) THEN 2
WHEN (d.Score >= 500 and d.Score < 1000) THEN 3
WHEN (d.Score >= 1000 and d.Score < 2000) THEN 4
WHEN (d.Score >= 2000) THEN 5
END)
from UserScore d
WHERE a.PKID*=d.UserID
FROM [User] a
WHERE a.UserName = @UserName
SELECT a.PKID,
a.UserName,
a.NickName,
UserLevelUp=select MAX(CASE WHEN (ISNULL(d.Score,0) < 100) THEN 1
WHEN (d.Score >= 100 and d.Score < 500) THEN 2
WHEN (d.Score >= 500 and d.Score < 1000) THEN 3
WHEN (d.Score >= 1000 and d.Score < 2000) THEN 4
WHEN (d.Score >= 2000) THEN 5
END)
from UserScore d
WHERE a.PKID*=d.UserID
FROM [User] a
WHERE a.UserName = @UserName
得到: UserName = HuangJi , NickName = 黄京, UserLevelUp= 5 , ColumnID = 3
create table user1(userid int,username varchar(10),nickname varchar(10))
create table userscore(userid int,columnid int,score int)
insert user1 values(1, 'WenTao', '文涛')
insert user1 values(2, 'HuangJi', '黄京')
insert user1 values(3, 'zhoung', '赵天')
insert user1 values(4, 'zho', '仍天')
insert userscore values(1,1,400)
insert userscore values(1,2,50)
insert userscore values(3,1,70)
insert userscore values(2,3,2000)
insert userscore values(2,4,30)
--查询
declare @name varchar(10)
set @name='huangji'select c.userid,c.username,c.nickname,isnull(d.columnid,1) as columnid,c.score,
(case when c.score<100 then 1 when c.Score >= 100 and c.Score < 500 THEN 2 when c.Score >= 500 and c.Score < 1000 THEN 3
when c.Score >= 1000 and c.Score < 2000 THEN 4 else 5 end) as '等级'
from userscore d,
(select a.userid,a.username,a.nickname,max(case when score is null then 0 else score end) as score
from user1 a,userscore b
where a.userid*=b.userid
group by a.userid,a.username,a.nickname) c
where c.score*=d.score and c.username=@name
--结果
userid username nickname columnid score 等级
----------- ---------- ---------- ----------- ----------- -----------
2 HuangJi 黄京 3 2000 5(所影响的行数为 1 行)
UserLevelUp=MAX(CASE WHEN (ISNULL(d.Score,0) < 100) THEN 1
WHEN (d.Score >= 100 and d.Score < 500) THEN 2
WHEN (d.Score >= 500 and d.Score < 1000) THEN 3
WHEN (d.Score >= 1000 and d.Score < 2000) THEN 4
WHEN (d.Score >= 2000) THEN 5 END)
from UserScore d left join [User] a on a.PKID=d.UserID
WHERE a.UserName = @UserName
group by a.PKID, a.UserName, a.NickName