WEB开发网
开发学院数据库MSSQL Server SQL语句和存储过程 查询语句的流程控制 阅读

SQL语句和存储过程 查询语句的流程控制

 2007-11-11 07:40:02 来源:WEB开发网   
核心提示: 写好的存储过程如下createPROCEDUREPr_GetClass @TeacherIDintasDeclare @achar(50), @bchar(50), @cchar(50), @dchar(50), @echar(50)select@a=DRClass1,@b=DRClass2,@c=DRClass3,

  写好的存储过程如下


create PROCEDURE Pr_GetClass  
@TeacherID int
as 

Declare @a char(50), @b char(50), @c char(50), @d char(50), @e char(50) 
select @a=DRClass1, @b=DRClass2, @c=DRClass3, @d=DRClass4, @e=DRClass5 from Teacher Where TeacherID = @TeacherID 
DROP TABLE classname 
create table  classname(classname char(50)) 
insert into classname  (classname) values (@a) 
if (@b is not null)  
begin 
insert into classname  (classname) values (@b) 
if (@c is not null) 
  begin 
    insert into classname  (classname) values (@c) 
    if (@d is not null)  
    begin 
      insert into classname  (classname) values (@d) 
      if (@e is not null)  
      begin 
       insert into classname  (classname) values (@e) 
      end 
    end 
  end 
end  
select * from classname
go



2 连表查询

我有三个表
KJ表
KJID
TeacherID
..........................................................


Teacher表

TeacherID
TeacherName
CollageID
.........................................................

Collage表

CollageID
CollageName

我想写一个SQL语句,查询所有的KJ,根据KJ的TeacherID查到TeacherName,同时根据TeacherID查到Teacher,Teacher的CollageID查到Collage,最后生成的数据集里KJ的属性里除了本身的KJName以外,还想加上TeacherName,CollageName。


语句如下

  SELECT T1.KJName, T2.TeacherName, T3.CollageName
  FROM KJ T1
  LEFT JOIN Teacher T2 ON T2.TeacherID=T1.TeacherID
  LEFT JOIN Collage T3 ON T3.CollageID=T2.CollageID

上一页  1 2 

Tags:SQL 语句 存储

编辑录入:coldstar [复制链接] [打 印]
赞助商链接