如何用存储过程实现？？

gycgjy 2007-12-18 01:42:54
现有两个表：
主表Teacher:
字段名： TeacherID TeacherName
内容： 123456 王强
... ...

子表Student:
字段名： StudentID TeacherID StudentName Sequence
内容： 123456001 123456 王小强 NO1
123456002 123456 王小波 NO3
123456003 123456 李刚 NO2
123456004 123456 李大刚 NO4
123456005 123456 李小刚 NO6
123456006 123456 李刚强 NO5
... ... .... ...
两个表中有主表有几千条记录，子表有几万条记录.
如何用存储过程实现下面的结果：

结果：
TeacherID TeacherName Student1,Student2,Student3,Student4
123456 王强 王小强 李刚 王小波 李大刚,李刚强,李小刚
...全文
96 17 打赏 收藏 转发到动态 举报
写回复
用AI写文章
17 条回复
切换为时间正序
请发表友善的回复…
发表回复
中国风 2007-12-18
  • 打赏
  • 举报
 回复
下午有事走开：
--前三条记录分开显示，最后一条记录显示为一行,举个例子给楼主,也可以实现三条记录一条这样的显示，楼主改一下字符的生成规则就行了。。。
create table Teacher(  TeacherID int,    TeacherName    nvarchar(20)  ) 

insert  Teacher select    123456,           '王强' 

go





create table Student(StudentID int,   TeacherID int,    StudentName nvarchar(20) ,    [Sequence] nvarchar(10))

insert  Student select 123456001,           123456,         '王小强',             'NO1' 

insert  Student select 123456002,           123456,         '王小波',             'NO3' 

insert  Student select 123456003,           123456,         '李刚',                 'NO2' 

insert  Student select 123456004,           123456,         '李大刚',                 'NO4' 

insert  Student select 123456005,           123456,         '李小刚',                 'NO6' 

insert  Student select 123456006,           123456,         '李刚强',                 'NO5' 



go



declare @s nvarchar(4000),@i int,@j int

select 

    @i=max(con)

from 

    (select count(1) as con from Student group by TeacherID)T

select @j=1



while @j!>@i

select @s=isnull(@s+',','')+case when @j<5 then '[StudentName'+rtrim(@j)+']=max(case when con='+rtrim(@j)+' then StudentName else '''' end)'

				else '+'',''+max(case when con='+rtrim(@j)+' then StudentName else '''' end)' end,@j=@j+1

set @s =replace(@s,',+','+')





exec('select t2.TeacherID,'+@s+

'from (select *,con=(select count(1) from Student where TeacherID=a.TeacherID and [Sequence]!>a.[Sequence])from Student a)

TT join Teacher t2 on tt.TeacherID=t2.TeacherID

group by t2.TeacherID,t2.TeacherName')





TeacherID   StudentName1         StudentName2         StudentName3         StudentName4                                                   

----------- -------------------- -------------------- -------------------- -------------------------------------------------------------- 

123456      王小强                  李刚                   王小波                  李大刚,李刚强,李小刚
chuifengde 2007-12-18
  • 打赏
  • 举报
 回复 
create table Teacher(TeacherID varchar(20),TeacherName varchar(20))

insert Teacher select '123456','王强'

insert Teacher select '123457','王刚'



create table Student(StudentID varchar(20),TeacherID varchar(20),StudentName varchar(20),Sequence varchar(20))

insert Student select '123456001','123456','王小强','NO1' 

union all select '123456002','123456','王小波','NO3' 

union all select '123456003','123456','李刚','NO2' 

union all select '123456004','123456','李大刚','NO4' 

union all select '123456005','123456','李小刚','NO6'

union all select '123456006','123456','李刚强','NO5' 

union all insert student select '123456007','123457','李强','NO1' 



create function getStudent(@Tid varchar(20))

returns varchar(200)

as

begin

	declare @sql varchar(200)

	select @sql=isnull(@sql+',','')+studentname from

	(

		select top 100 percent StudentName from Student 

		where TeacherID=@Tid and cast(replace(Sequence,'NO','') as int)>3 

		order by cast(replace(Sequence,'NO','') as int)

	)aa

	return @sql

end



go

select a.TeacherID ,a.TeacherName ,b.*

from Teacher a,

(select TeacherID,

	Student1=max(case when Sequence='NO1' then StudentName else '' end),

	Student2=max(case when Sequence='NO2' then StudentName else '' end),

	Student3=max(case when Sequence='NO3' then StudentName else '' end),

	Student4=isnull(dbo.getStudent(TeacherID),'') from Student group by TeacherID

)b

where a.TeacherID=b.TeacherID



--result

/*TeacherID            TeacherName          TeacherID            Student1             Student2             Student3             Student4                                                                                                                                                                                                 

-------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- 

123456               王强                   123456               王小强                  李刚                   王小波                  李大刚,李刚强,李小刚

123457               王刚                   123457               李强                                                             



（所影响的行数为 2 行）

*/
-狙击手- 2007-12-18
  • 打赏
  • 举报
 回复
/*

TeacherID TeacherName student1 student2 student3 student4
----------- ----------- ---------- ---------- ---------- ----------------------------------------------------------------------------------------------------
123456 王强 王小强 李刚 王小波 李大刚,李小刚,李刚强


*/
-狙击手- 2007-12-18
  • 打赏
  • 举报
 回复
中国风，楼主是想把大于NO3的最后显示成一行
中国风 2007-12-18
  • 打赏
  • 举报
 回复
select
t2.TeacherID,
[StudentName1]=max(case when con=1 then StudentName else '' end),
[StudentName2]=max(case when con=2 then StudentName else '' end),
[StudentName3]=max(case when con=3 then StudentName else '' end),
[StudentName4]=max(case when con=4 then StudentName else '' end)+','+max(case when con=5 then StudentName else '' end)+','+max(case when con=6 then StudentName else '' end)

from (select *,con=(select count(1) from Student where TeacherID=a.TeacherID and [Sequence]!>a.[Sequence])from Student a)
TT join Teacher t2 on tt.TeacherID=t2.TeacherID
group by t2.TeacherID,t2.TeacherName

--这样显示?
中国风 2007-12-18
  • 打赏
  • 举报
 回复 
create table Teacher(  TeacherID int,    TeacherName    nvarchar(20)  ) 

insert  Teacher select    123456,           '王强' 

go





create table Student(StudentID int,   TeacherID int,    StudentName nvarchar(20) ,    [Sequence] nvarchar(10))

insert  Student select 123456001,           123456,         '王小强',             'NO1' 

insert  Student select 123456002,           123456,         '王小波',             'NO3' 

insert  Student select 123456003,           123456,         '李刚',                 'NO2' 

insert  Student select 123456004,           123456,         '李大刚',                 'NO4' 

insert  Student select 123456005,           123456,         '李小刚',                 'NO6' 

insert  Student select 123456006,           123456,         '李刚强',                 'NO5' 



go



declare @s nvarchar(4000),@i int

select 

    @i=max(con)

from 

    (select count(1) as con from Student group by TeacherID)T

set @s=''



while @i>0

select @s='[StudentName'+rtrim(@i)+']=max(case when con='+rtrim(@i)+' then StudentName else '''' end),'

    +@s,@i=@i-1

set @s=left(@s,len(@s)-1)



exec('select t2.TeacherID,'+@s+

'from (select *,con=(select count(1) from Student where TeacherID=a.TeacherID and [Sequence]!>a.[Sequence])from Student a)

TT join Teacher t2 on tt.TeacherID=t2.TeacherID

group by t2.TeacherID,t2.TeacherName')







TeacherID   StudentName1         StudentName2         StudentName3         StudentName4         StudentName5         StudentName6         

----------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- 

123456      王小强                  李刚                   王小波                  李大刚                  李刚强                  李小刚



select t2.TeacherID,[StudentName1]=max(case when con=1 then StudentName else '' end),[StudentName2]=max(case when con=2 then StudentName else '' end),[StudentName3]=max(case when con=3 then StudentName else '' end),[StudentName4]=max(case when con=4 then StudentName else '' end),[StudentName5]=max(case when con=5 then StudentName else '' end),[StudentName6]=max(case when con=6 then StudentName else '' end)from (select *,con=(select count(1) from Student where TeacherID=a.TeacherID and [Sequence]!>a.[Sequence])from Student a)

TT join Teacher t2 on tt.TeacherID=t2.TeacherID

group by t2.TeacherID,t2.TeacherName
-狙击手- 2007-12-18
  • 打赏
  • 举报
 回复 
create table Teacher(TeacherID int,TeacherName varchar(10))

insert into teacher select 123456,'王强' 

create table Student(StudentID int,TeacherID int,StudentName varchar(10),[Sequence] varchar(4))

insert into student select 123456001,123456         ,'王小强','NO1' 

insert into student select 123456002,123456         ,'王小波','NO3' 

insert into student select 123456003,123456         ,'李刚','NO2' 

insert into student select 123456004,123456         ,'李大刚','NO4' 

insert into student select 123456005,123456         ,'李小刚','NO6' 

insert into student select 123456006,123456         ,'李刚强','NO5'

go



create function f_str(@teacher int)

returns varchar(100)

as

begin

declare @s varchar(8000)

select @s = ''

select @s = @s + ','+studentname from student where cast(substring([Sequence],3,10)  as int) > 3

set @s = stuff(@s,1,1,'')

return @s



end 

go



declare @sql varchar(8000)

set @sql = 'select a.TeacherID,a.TeacherName '



select @sql = @sql 

+ ',[student'+ltrim(N)+']= max(case when [Sequence] = ''NO'+ltrim(N)+''' then b.StudentName else '''' end)'

from (select 1 as N union select 2 union select 3) aa



exec ( @sql+' ,dbo.f_str(a.TeacherID) as student4 from teacher a

left join student b

on a.TeacherID = b.TeacherID

group by a.TeacherID,a.TeacherName')







drop table student,teacher

drop function f_str



/*



TeacherID   TeacherName student1   student2   student3   student4                                                                                             

----------- ----------- ---------- ---------- ---------- ---------------------------------------------------------------------------------------------------- 

123456      王强          王小强        李刚         王小波        李大刚,李小刚,李刚强





*/
-狙击手- 2007-12-18
  • 打赏
  • 举报
 回复 
create table Teacher(TeacherID int,TeacherName varchar(10))

insert into teacher select 123456,'王强' 

create table Student(StudentID int,TeacherID int,StudentName varchar(10),[Sequence] varchar(4))

insert into student select 123456001,123456         ,'王小强','NO1' 

insert into student select 123456002,123456         ,'王小波','NO3' 

insert into student select 123456003,123456         ,'李刚','NO2' 

insert into student select 123456004,123456         ,'李大刚','NO4' 

insert into student select 123456005,123456         ,'李小刚','NO6' 

insert into student select 123456006,123456         ,'李刚强','NO5'

go



create function f_str(@teacher int)

returns varchar(100)

as

begin

declare @s varchar(8000)

select @s = ''

select @s = @s + ','+studentname from student where cast(substring([Sequence],3,10)  as int) > 3

return @s



end 

go



declare @sql varchar(8000)

set @sql = 'select a.TeacherID,a.TeacherName '



select @sql = @sql 

+ ',[student'+ltrim(N)+']= max(case when [Sequence] = ''NO'+ltrim(N)+''' then b.StudentName else '''' end)'

from (select 1 as N union select 2 union select 3) aa



exec ( @sql+' ,dbo.f_str(a.TeacherID) as student4 from teacher a

left join student b

on a.TeacherID = b.TeacherID

group by a.TeacherID,a.TeacherName')







drop table student,teacher

drop function f_str



/*



TeacherID   TeacherName student1   student2   student3   student4                                                                                             

----------- ----------- ---------- ---------- ---------- ---------------------------------------------------------------------------------------------------- 

123456      王强          王小强        李刚         王小波        ,李大刚,李小刚,李刚强





*/
中国风 2007-12-18
  • 打赏
  • 举报
 回复
动态生成的语句如上..
复制到下面去了 Sequence有大小顺序时，可以这个实现
中国风 2007-12-18
  • 打赏
  • 举报
 回复 
select TeacherID,[StudentName1]=max(case when con=1 then StudentName else '' end),[StudentName2]=max(case when con=2 then StudentName else '' end),[StudentName3]=max(case when con=3 then StudentName else '' end),[StudentName4]=max(case when con=4 then StudentName else '' end),[StudentName5]=max(case when con=5 then StudentName else '' end),[StudentName6]=max(case when con=6 then StudentName else '' end)from (select *,con=(select count(1) from Student where TeacherID=a.TeacherID and [Sequence]!>a.[Sequence])from Student a)

TT group by TeacherID
动态生成的语句如下:
中国风 2007-12-18
  • 打赏
  • 举报
 回复 
create table Student(StudentID int,   TeacherID int,    StudentName nvarchar(20) ,    [Sequence] nvarchar(10))

insert  Student select 123456001,           123456,         '王小强',             'NO1' 

insert  Student select 123456002,           123456,         '王小波',             'NO3' 

insert  Student select 123456003,           123456,         '李刚',                 'NO2' 

insert  Student select 123456004,           123456,         '李大刚',                 'NO4' 

insert  Student select 123456005,           123456,         '李小刚',                 'NO6' 

insert  Student select 123456006,           123456,         '李刚强',                 'NO5' 



go



declare @s nvarchar(4000),@i int

select 

	@i=max(con)

from 

	(select count(1) as con from Student group by TeacherID)T

set @s=''



while @i>0

select @s='[StudentName'+rtrim(@i)+']=max(case when con='+rtrim(@i)+' then StudentName else '''' end),'

	+@s,@i=@i-1

set @s=left(@s,len(@s)-1)



exec('select TeacherID,'+@s+

'from (select *,con=(select count(1) from Student where TeacherID=a.TeacherID and [Sequence]!>a.[Sequence])from Student a)

TT group by TeacherID')



TeacherID   StudentName1         StudentName2         StudentName3         StudentName4         StudentName5         StudentName6         

----------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- 

123456      王小强                  李刚                   王小波                  李大刚                  李刚强                  李小刚
gycgjy 2007-12-18
  • 打赏
  • 举报
 回复
我要的结果中不是简单的将行转换成列，我想要的是有顺序的。
排名第一的学生放在Studet1中，排名第二的学生放在Student2中，但排名第二的学生在子表中是不一定是第二条记录
gycgjy 2007-12-18
  • 打赏
  • 举报
 回复
我用的就是 sql server 2005
gycgjy 2007-12-18
  • 打赏
  • 举报
 回复
我想要的结果是：


TeacherID TeacherName Student1 Student2 Student3 Student4
123456 王强 王小强 李刚 王小波 李大刚,李刚强,李小刚
中国风 2007-12-18
  • 打赏
  • 举报
 回复
2000需要用函数，05可用pivot
-狙击手- 2007-12-18
  • 打赏
  • 举报
 回复
动态SQL　＋　函数
toddzst 2007-12-18
  • 打赏
  • 举报
 回复
动态SQL,行列转换,你查下这方面的
视频打包为MP4格式并存储到TF卡的实现-第7/11季
本季实现将编码后的H.264视频流打包为MP4格式的录像，并存储到板载TF卡中。这个过程涉及到的一个主要技术是H.264的视频帧格式，I帧、P帧等概念，另一个主要技术是MP4格式的文件头、封包技术、MP4打包库的移植部署和调用等，最后是linux下TF卡的存储和文件管理（使用了FAT32文件系统）。
什么是存储过程？用什么调用
文章目录什么是存储过程？存储过程的好处示例 什么是存储过程？ 什么是存储过程：存储过程可以说是一个记录集吧，它是由一些T-SQL语句组成的代码块，这些T-SQL语句代码像一个方法一样实现一些功能（对单表或多表的增删改查），然后再给这个代码块取一个名字，在用到这个功能的时候调用他就行了。 用什么调用？ 可以用一个“execute 存储过程名 参数”命令来调用存储过程。 存储过程的好处 1.由于数据库执行动作时，是先编译后执行的。然而存储过程是一个编译过的代码块，所以执行效率要比T-SQL语句高。 2.一个存储
什么时候使用存储过程比较适合？
存储过程（Stored Procedure）是在大型数据库系统中，一组为了完成特定功能的SQL 语句集，存储在数据库中经过第一次编译后再次调用不需要再次编译，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程是数据库中的一个重要对象，任何一个设计良好的数据库应用程序都应该用到存储过程。 当一个事务涉及到多个SQL语句时或者涉及到对多个表的操作时就要考虑用存储过程
讨论：应不应该用存储过程？
事情起因于群里转发一篇文章《为什么阿里巴巴禁止使用存储过程？》 作者用自己的亲身经历讲解存储过程维护的不方便。 然后大家讨论存储过程的优势和缺点。 引子：存储过程 大白：存储过程在很多场景时有其优势，比如性能。但对于业务逻辑的通用方法，非常不推荐将其写在存储过程中，代码复用、扩展与客户端语言比，相差甚远。也许终究能实现，但代价与风险比客户端语言要高，得不偿失。 花也花不完：我的想法是可以用，分场景 如果应用的当，省时省力 我用的比较多 主要是性能是第一，依据自身去控制 St...
Hive 如何方便地实现存储过程？
Hive 提供了两种方式实现存储过程，但使用上都不方便。 第一种是 HPL/SQL。这种方式目前还不完善，比如游标使用限制多，很多功能无法实现，对变量要求严格，经常发生不兼容的错误。只要可调试，代码报错并非障碍，但 HPL/SQL 的问题在于不可调试，对于开发者就很不方便。 更不方便的是，HPL/SQL 缺乏 JDBC 接口，无法方便地嵌入 JAVA 程序，只能在 JAVA 中调用命令行执行 HPL/SQL，再由 HPL/SQL 实施计算并将结果回写 Hive 临时表，最后 JAVA 通过 Hive 的
疑难问题

22,210

社区成员

121,726

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章