求一复杂的SQL语句，高手看看！

Question

--创建处理函数
create function f_str(@student_id int)
returns varchar(8000)
as
begin
declare @r varchar(8000)
set @r=''
select @r=@r+'$'+cast(lesson_id as varchar)
from tblLesson
where student_id=@student_id
return(substring(@r,2,8000))
end
go--调用实现查询
select auto_id=(select count(distinct student_id) from tblLesson where student_id<=a.student_id)
,lessonString=dbo.f_str(student_id)
,selectNum=count(*)
,total=(select count(distinct lesson_id) from tblLesson)
,[percent]=cast(cast(sum(100.0)
/(select count(distinct lesson_id) from tblLesson)
as decimal(10,2)) as varchar)+'%'
from tblLesson a
group by student_id

Answer 1

--测试--测试数据
create table tblLesson(auto_id int,student_id int,lesson_id int)
insert tblLesson select 1 ,1,10
union  all       select 2 ,1,11
union  all       select 3 ,1,12
union  all       select 4 ,2,21
union  all       select 5 ,2,22
union  all       select 6 ,2,23
union  all       select 7 ,2,24
union  all       select 8 ,3,10
union  all       select 9 ,3,11
union  all       select 10,3,12
go--创建处理函数
create function f_str(@student_id int)
returns varchar(8000)
as
begin
declare @r varchar(8000)
set @r=''
select @r=@r+'$'+cast(lesson_id as varchar)
from tblLesson
where student_id=@student_id
return(substring(@r,2,8000))
end
go--调用实现查询
select auto_id=(select count(distinct student_id) from tblLesson where student_id<=a.student_id)
,lessonString=dbo.f_str(student_id)
,selectNum=count(*)
,total=(select count(distinct lesson_id) from tblLesson)
,[percent]=cast(cast(sum(100.0)
/(select count(distinct lesson_id) from tblLesson)
as decimal(10,2)) as varchar)+'%'
from tblLesson a
group by student_id
go--删除测试
drop table tblLesson
drop function f_str/*--测试结果

auto_id     lessonString selectNum   total       percent    
----------- ------------ ----------- ----------- -----------
1           10$11$12     3           7           42.86%
2           21$22$23$2   4           7           57.14%
3           10$11$12     3           7           42.86%（所影响的行数为 3 行）
--*/

Answer 2

--上面少写了排序--创建处理函数
create function f_str(@student_id int)
returns varchar(8000)
as
begin
declare @r varchar(8000)
set @r=''
select @r=@r+'$'+cast(lesson_id as varchar)
from tblLesson
where student_id=@student_id
return(substring(@r,2,8000))
end
go--调用实现查询
select auto_id=(select count(distinct student_id) from tblLesson where student_id<=a.student_id)
,lessonString=dbo.f_str(student_id)
,selectNum=count(*)
,total=(select count(distinct lesson_id) from tblLesson)
,[percent]=cast(cast(sum(100.0)
/(select count(distinct lesson_id) from tblLesson)
as decimal(10,2)) as varchar)+'%'
from tblLesson a
group by student_id
order by selectNum desc
go

Answer 3

--测试--测试数据
create table tblLesson(auto_id int,student_id int,lesson_id int)
insert tblLesson select 1 ,1,10
union  all       select 2 ,1,11
union  all       select 3 ,1,12
union  all       select 4 ,2,21
union  all       select 5 ,2,22
union  all       select 6 ,2,23
union  all       select 7 ,2,24
union  all       select 8 ,3,10
union  all       select 9 ,3,11
union  all       select 10,3,12
go--创建处理函数
create function f_str(@student_id int)
returns varchar(8000)
as
begin
declare @r varchar(8000)
set @r=''
select @r=@r+'$'+cast(lesson_id as varchar)
from tblLesson
where student_id=@student_id
return(substring(@r,2,8000))
end
go--调用实现查询
select auto_id=(select count(distinct student_id) from tblLesson where student_id<=a.student_id)
,lessonString=dbo.f_str(student_id)
,selectNum=count(*)
,total=(select count(distinct lesson_id) from tblLesson)
,[percent]=cast(cast(sum(100.0)
/(select count(distinct lesson_id) from tblLesson)
as decimal(10,2)) as varchar)+'%'
from tblLesson a
group by student_id
order by selectNum desc
go--删除测试
drop table tblLesson
drop function f_str/*--测试结果

auto_id     lessonString selectNum   total       percent     
----------- ------------ ----------- ----------- ------------
2           21$22$23$2   4           7           57.14%
3           10$11$12     3           7           42.86%
1           10$11$12     3           7           42.86%（所影响的行数为 3 行）
--*/

Answer 4

create function getlessonString
(
@student_id int
)
returns varchar(500)
as
begin
   declare @r varchar(500)
   set @r=''
   select @r=@r+cast(lesson_id as varchar(10))+'$' from tblLesson where student_id=@student_id order by auto_id    
   if @r<>'' set @r=left(@r,len(@r)-1)
   return @r
endgo
select dbo.getlessonString(student_id) from lessonString,count(*) as selectNum,
(select count(distinct student_id) from tblLesson) as total,cast(count(*) as numeric(10,2))/(select count(distinct student_id) from tblLesson) as percent
from (select distinct student_id from tblLesson) as a
group by 1

Answer 5

更正：
select dbo.getlessonString(student_id) lessonString,count(*) as selectNum,
(select count(distinct student_id) from tblLesson) as total,
cast(count(*) as numeric(10,2))/(select count(distinct student_id) from tblLesson) as [percent]
from (select distinct student_id from tblLesson) as a
group by dbo.getlessonString(student_id)结果：lessonString                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         selectNum   total       percent                 
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------- ----------- ----------------------- 
10$11$12                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             2           3           .6666666666666
21$22$23$24                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          1           3           .3333333333333（所影响的行数为 2 行）

Answer 6

--auto_id有问题,生成不了,改临时表吧
select auto_id=identity(int,1,1),*
into #t
from(
select lessonString=dbo.f_str(student_id)
,selectNum=count(*)
,total=(select count(distinct lesson_id) from tblLesson)
,[percent]=cast(cast(sum(100.0)
/(select count(distinct lesson_id) from tblLesson)
as decimal(10,2)) as varchar)+'%'
from tblLesson a
group by student_id
)a order by selectNum descselect * from #t
drop table #t

Answer 7

--测试--测试数据
create table tblLesson(auto_id int,student_id int,lesson_id int)
insert tblLesson select 1 ,1,10
union  all       select 2 ,1,11
union  all       select 3 ,1,12
union  all       select 4 ,2,21
union  all       select 5 ,2,22
union  all       select 6 ,2,23
union  all       select 7 ,2,24
union  all       select 8 ,3,10
union  all       select 9 ,3,11
union  all       select 10,3,12
go--创建处理函数
create function f_str(@student_id int)
returns varchar(8000)
as
begin
declare @r varchar(8000)
set @r=''
select @r=@r+'$'+cast(lesson_id as varchar)
from tblLesson
where student_id=@student_id
return(substring(@r,2,8000))
end
go--调用实现查询
select auto_id=identity(int,1,1),*
into #t
from(
select lessonString=dbo.f_str(student_id)
,selectNum=count(*)
,total=(select count(distinct lesson_id) from tblLesson)
,[percent]=cast(cast(sum(100.0)
/(select count(distinct lesson_id) from tblLesson)
as decimal(10,2)) as varchar)+'%'
from tblLesson a
group by student_id
)a order by selectNum descselect * from #t
drop table #t
go--删除测试
drop table tblLesson
drop function f_str/*--测试结果

auto_id     lessonString selectNum   total       percent  
----------- ------------ ----------- ----------- ---------
1           21$22$23$2   4           7           57.14%
2           10$11$12     3           7           42.86%
3           10$11$12     3           7           42.86%（所影响的行数为 3 行）
--*/

Answer 8

版主：你的结果错了，他要按lessonString 统计

Answer 9

--真看错了.改select auto_id=identity(int,1,1),*
into #t
from(
select lessonString=dbo.f_str(student_id)
,selectNum=count(distinct student_id)
,total=(select count(distinct student_id) from tblLesson)
,[percent]=cast(cast(count(distinct student_id)*100.0
/(select count(distinct student_id) from tblLesson)
as decimal(10,2)) as varchar)+'%'
from tblLesson a
group by dbo.f_str(student_id)
)a order by selectNum descselect * from #t
drop table #t

Answer 10

--测试--测试数据
create table tblLesson(auto_id int,student_id int,lesson_id int)
insert tblLesson select 1 ,1,10
union  all       select 2 ,1,11
union  all       select 3 ,1,12
union  all       select 4 ,2,21
union  all       select 5 ,2,22
union  all       select 6 ,2,23
union  all       select 7 ,2,24
union  all       select 8 ,3,10
union  all       select 9 ,3,11
union  all       select 10,3,12
go--创建处理函数
create function f_str(@student_id int)
returns varchar(8000)
as
begin
declare @r varchar(8000)
set @r=''
select @r=@r+'$'+cast(lesson_id as varchar)
from tblLesson
where student_id=@student_id
return(substring(@r,2,8000))
end
go--调用实现查询
select auto_id=identity(int,1,1),*
into #t
from(
select lessonString=dbo.f_str(student_id)
,selectNum=count(distinct student_id)
,total=(select count(distinct student_id) from tblLesson)
,[percent]=cast(cast(count(distinct student_id)*100.0
/(select count(distinct student_id) from tblLesson)
as decimal(10,2)) as varchar)+'%'
from tblLesson a
group by dbo.f_str(student_id)
)a order by selectNum descselect * from #t
drop table #t
go--删除测试
drop table tblLesson
drop function f_str/*--测试结果

auto_id     lessonString selectNum   total       percent   
----------- ------------ ----------- ----------- ----------
1           10$11$12     2           3           66.67%
2           21$22$23$2   1           3           33.33%（所影响的行数为 2 行）
--*/