1.使用
IF-ELSE
语句显示用户的权限
2.使用
WHILE
语句显示星级用户
3.使用CASE-END
显示用户的状态
--1.用T-SQL语句操作显示系统信息:SQL Server版本号、服务器的名称、错误号等。
--在第二章上机中,已在数据库表中插入测试数据,网上有人举报,可卡因涉嫌发表不合法言论,版主希望查看核实可卡因的发贴情况和权限。
select UName,UClass,UPoint
from BBSUser
where UName='可卡因'
declare @userid int
declare @UPoint int
select @userid=UID,@UPoint=UPoint
from BBSUser
where UName='可卡因'
print'可卡因发帖情况如下'
select CONVERT(varchar(10),TTime,111) as 发帖时间,TClickCount as 点击量,TTopic as 主题,TContents as 内容
from BBSTopic
where TUID=@userid
print'可卡因回帖情况如下'
select CONVERT(varchar(10),RTime,111) as 回帖时间,RContents as 回帖内容
from BBSReply
where RUID=@userid
if(@UPoint>30)
print'有权限发帖'
else
print'无权限发帖'
--2.鉴于目前的星级用户偏少,用户普遍因积分较少而发贴热情不高,这也间接影响了论坛的人气,版主希望通过提高用户的积分,从而增加星级用户。星级用户的评定规则如 下。
--积分:0~500评为1星级;500~1000评为2星级;1000~2000评为3星级;2000~4000评为4星级;4000~5000评为5星级,5000以上评为6星级,提分后显示用户的等级(星级)和积分情况 。
declare @score int,@avg int
set @score=0
while(1=1)
begin
select @avg=AVG(UPoint)
from BBSUser
if (@avg<2000)
update BBSUser set UPoint=UPoint+50
set @score=@score+50
select @avg=AVG(UPoint)
from BBSUser
if (@avg>2000)
break
end
print '提升分值:' +convert(varchar(8),@score)
declare @upoint int
select @upoint=UPoint
from BBSUser
update BBSUser set UClass=
case when @upoint<500 then 1
when @upoint between 500 and 1000 then 2
when @upoint between 1000 and 2000 then 3
when @upoint between 2000 and 4000 then 4
when @upoint between 4000 and 5000 then 5
else 6 end
select UName as 用户,case when UClass=1 then '1星'
when UClass=2 then '2星'
when UClass=3 then '3星'
when UClass=4 then '4星'
when UClass=5 then '5星'
else '6星
end as 星级,UPoint as 积分
from BBSUser
--3.论坛中经常需要查询统计某位用户的贴子总数和贴子内容,
--以作为评选精华贴、奖励论坛元老等的考核依据。请使用T-SQL
--语句实现:查询心酸果冻用户的发贴数和回贴数,如果发贴数>0
--,显示发贴数和具体的贴子信息,否则显示发贴数:0贴;同理,
--回贴也是如此。最后显示贴子总计数量(发贴数+回贴数),并根据贴子总量显示功臣级别 。
set nocount on
declare @userid int, @amount int, @temp int, @grade varchar(10)
select @userid=UID from BBSUser
where UName='心酸果冻'
select @temp=COUNT(*) from BBSTopic
where TUID =@userid
set @amount=@temp
if @temp>0
begin
print '心酸果冻发贴数:'+convert(varchar(3),@temp)+',贴子如下:'
select 发贴时间=CONVERT(varchar(10),TTime,111),
主题=TTopic, 内容=TContents
from BBSTopic
where TUID=@userid
end
else
print '心酸果冻发贴数:0'
-----------------------------------------------
select @temp=COUNT(*) from BBSReply where RUID=@userid
if @temp>0
begin
print '心酸果冻回贴数:'+convert(varchar(3),@temp)+',贴子如下:'
select 回贴时间=CONVERT(varchar(10),RTime,111), 点击率=RClickCount,
回贴内容=RContents
from BBSReply
where RUID=@userid
end
else
print '心酸果冻回贴数:0'
set @amount=@amount+@temp
select @grade=case
when @amount<10 then '新手上路'
when @amount between 10 and 20 then '侠客'
when @amount between 21 and 30 then '骑士'
when @amount between 31 and 40 then '精灵王'
when @amount between 41 and 50 then '光明使者'
else '法老'
end
print ''
print '心酸果冻贴数总计:'+convert(varchar(5),@amount)+'贴'+' 功臣级别:'+@grade
go