小第初学SQL遇到小问题,望各位大侠指点迷津。
use stuDb
if exists(select * from sysobjects where name='proc_stu')
drop procedure prec_stu
create procedure proc_stu
@notpasssum int output
@writtenpass int=60
@labpass int=60
as
print '笔试及格线'+convert(varchar(5),@writtenpass)
print '机试及格线'+convert(varchar(5),@labpass)
select stuName,stuinfo,stuNO,writtenexam,labexam from stuinfo left join
stus on stuinfo.stuno=stus.stuno where writtenexam<@writtenpass or labexam<@labpass
select @notpassSum=count(stuNO) from stus where writtenexam<@writtenpass or labexam<@labpass
godeclare @sum int
exec proc_stu @sum output
if(sum>3)
print'未通过人数'+convert(varchar(5),@sum)+'人,通过达60%'
else
print'未通过人数'+convert(varchar(5),@sum)+'人,通过未达60%'
end
go---p156
if exists(select * from sysobjects where name='proc_find')
drop procedure proc_find
go
create procedure proc_find
@username varchar(10),
@sumtopic int output,
@sumReply int output,
@secName varchar(10)
as
set nocount on
declare @userid varchar(10)
select @userid=uid from bbsUsers where uname=@username
if exists(select * from bbstopic where tuid=@userid)
begin
select @sumtopic=count(*) from bbstopic where tuid=@userid
select 发帖时间=convert(varchar(10),ttime,111),点击率=tclickcount,主题=Ttopic,内容=Tcontents from bbstopic
where Tuid=@userid
end
else
begin
set @sumtopic=0
print '该用户没有发过主帖'
end
if exists(select * from bbsReply where ruid=@userid)
begin
select @sumReply=count(*) from bbsReply where Ruid=@userid
select 发帖时间=convert(varchar(10),rtime,111),点击率=rclickcount,内容=rcontents from bbsReply
where Ruid=@userid
end
else
begin
set @sumReply=0
print '该用户没有回过帖'
end
godeclare @sumt int,@sumR int
exec proc_find '可卡因', @sumt output,@sumr output
if(@sumt>@sumR)
begin
print '发主帖大于回帖'
end
else
begin
print '回帖大于发主帖'
end
以上功能是可实现。如果显示在某板块显示改用户的发帖数和回帖数,该如何实现呢?
主帖表结构 topicTID Tsid Tuid TreplyCount Tface Ttopic Tcontents Ttime 板块结构 section
SID Sname SmasterID Sprofile
回帖表结构 reply
RID RtID RsID RuID Rface Rcontents
use stuDb
if exists(select * from sysobjects where name='proc_stu')
drop procedure prec_stu
create procedure proc_stu
@notpasssum int output
@writtenpass int=60
@labpass int=60
as
print '笔试及格线'+convert(varchar(5),@writtenpass)
print '机试及格线'+convert(varchar(5),@labpass)
select stuName,stuinfo,stuNO,writtenexam,labexam from stuinfo left join
stus on stuinfo.stuno=stus.stuno where writtenexam<@writtenpass or labexam<@labpass
select @notpassSum=count(stuNO) from stus where writtenexam<@writtenpass or labexam<@labpass
godeclare @sum int
exec proc_stu @sum output
if(sum>3)
print'未通过人数'+convert(varchar(5),@sum)+'人,通过达60%'
else
print'未通过人数'+convert(varchar(5),@sum)+'人,通过未达60%'
end
go---p156
if exists(select * from sysobjects where name='proc_find')
drop procedure proc_find
go
create procedure proc_find
@username varchar(10),
@sumtopic int output,
@sumReply int output,
@secName varchar(10)
as
set nocount on
declare @userid varchar(10)
select @userid=uid from bbsUsers where uname=@username
if exists(select * from bbstopic where tuid=@userid)
begin
select @sumtopic=count(*) from bbstopic where tuid=@userid
select 发帖时间=convert(varchar(10),ttime,111),点击率=tclickcount,主题=Ttopic,内容=Tcontents from bbstopic
where Tuid=@userid
end
else
begin
set @sumtopic=0
print '该用户没有发过主帖'
end
if exists(select * from bbsReply where ruid=@userid)
begin
select @sumReply=count(*) from bbsReply where Ruid=@userid
select 发帖时间=convert(varchar(10),rtime,111),点击率=rclickcount,内容=rcontents from bbsReply
where Ruid=@userid
end
else
begin
set @sumReply=0
print '该用户没有回过帖'
end
godeclare @sumt int,@sumR int
exec proc_find '可卡因', @sumt output,@sumr output
if(@sumt>@sumR)
begin
print '发主帖大于回帖'
end
else
begin
print '回帖大于发主帖'
end
以上功能是可实现。如果显示在某板块显示改用户的发帖数和回帖数,该如何实现呢?
主帖表结构 topicTID Tsid Tuid TreplyCount Tface Ttopic Tcontents Ttime 板块结构 section
SID Sname SmasterID Sprofile
回帖表结构 reply
RID RtID RsID RuID Rface Rcontents
坐着沙发,抽支烟~~~~