有两个表:
学生:
stu_id,stu_name,class_name
-------------------------
01,'张三','班级1'
02,'李四','班级1'
03,'王五','班级2'
........老师:
t_id,t_name,class_name
-----------------------
01,'李老师','班级1'
02,'黄老师','班级2'
03,'于老师','班级2'用sql生成如下报表样式:
班级 学生 老师
--------------------------
班级1 张三 李老师
班级1 李四
班级2 王五 黄老师
班级2 于老师
学生:
stu_id,stu_name,class_name
-------------------------
01,'张三','班级1'
02,'李四','班级1'
03,'王五','班级2'
........老师:
t_id,t_name,class_name
-----------------------
01,'李老师','班级1'
02,'黄老师','班级2'
03,'于老师','班级2'用sql生成如下报表样式:
班级 学生 老师
--------------------------
班级1 张三 李老师
班级1 李四
班级2 王五 黄老师
班级2 于老师
(
stu_id char(2),
stu_name nvarchar(20),
class_name nvarchar(20)
)
insert 学生 select '01','张三','班级1'
insert 学生 select '02','李四','班级1'
insert 学生 select '03','王五','班级2'create table 老师
(
t_id char(2),
t_name nvarchar(20),
class_name nvarchar(20)
)insert 老师 select '01','李老师','班级1'
insert 老师 select '02','黄老师','班级2'
insert 老师 select '03','于老师','班级2'select a.class_name,b.stu_name,a.t_name
from 老师 a left join 学生 b on a.class_name=b.class_namedrop table 学生,老师/*
class_name stu_name t_name
-------------------- -------------------- --------------------
班级1 张三 李老师
班级1 李四 李老师
班级2 王五 黄老师
班级2 王五 于老师(所影响的行数为 4 行)*/
(
stu_id char(2),
stu_name nvarchar(20),
class_name nvarchar(20)
)
insert 学生 select '01','张三','班级1'
insert 学生 select '02','李四','班级1'
insert 学生 select '03','王五','班级2'create table 老师
(
t_id char(2),
t_name nvarchar(20),
class_name nvarchar(20)
)insert 老师 select '01','李老师','班级1'
insert 老师 select '02','黄老师','班级2'
insert 老师 select '03','于老师','班级2'
insert 老师 select '03','赵老师','班级1'select a.class_name,b.stu_name,a.t_name
from 老师 a left join 学生 b on a.class_name=b.class_namedrop table 学生,老师-------------------------------------
班级1 张三 李老师
班级1 李四 李老师
班级2 王五 黄老师
班级2 王五 于老师
班级1 张三 赵老师
班级1 李四 赵老师(6 行受影响)
from 老师 a left join 学生 b on a.class_name=b.class_nameupdate a
set stu_name=''
from #tmp a where exists(select * from #tmp where class_name=a.classname and stu_name=a.stu_name and id<a.id)update a
set t_name=''
from #tmp a where exists(select * from #tmp where class_name=a.classname and t_name=a.t_name and id<a.id)select class_name,stu_name,t_name from #tmp
drop table #tmp两个update语句去掉重复的学生与老师名
insert into A values('01','张三','班级1')
insert into A values('02','李四','班级1')
insert into A values('03','王五','班级2')
create table B(t_id varchar(10),t_name varchar(10),class_name varchar(10))
insert into B values('01','李老师','班级1')
insert into B values('02','黄老师','班级2')
insert into B values('03','于老师','班级2')
goselect B.class_name , A.stu_name , B.t_name
from B left join A
on B.class_name = A.class_namedrop table A,B/*
class_name stu_name t_name
---------- ---------- ----------
班级1 张三 李老师
班级1 李四 李老师
班级2 王五 黄老师
班级2 王五 于老师(4 行受影响)
*/
create table A(stu_id varchar(10),stu_name varchar(10),class_name varchar(10) )
insert into A values('01','张三','班级1')
insert into A values('02','李四','班级1')
insert into A values('03','王五','班级2')
create table B(t_id varchar(10),t_name varchar(10),class_name varchar(10))
insert into B values('01','李老师','班级1')
insert into B values('02','黄老师','班级2')
insert into B values('03','于老师','班级2')
goselect class_name ,
case when t_name = (select min(t_name) from
(
select B.class_name , A.stu_name , B.t_name
from B left join A
on B.class_name = A.class_name
) t
where stu_name = m.stu_name) then stu_name else '' end as stu_name,
case when stu_name = (select max(stu_name) from
(
select B.class_name , A.stu_name , B.t_name
from B left join A
on B.class_name = A.class_name
) t
where t_name = m.t_name) then t_name else '' end as t_name
from
(
select B.class_name , A.stu_name , B.t_name
from B left join A
on B.class_name = A.class_name
) mdrop table A,B/*
class_name stu_name t_name
---------- ---------- ----------
班级1 张三 李老师
班级1 李四
班级2 王五 黄老师
班级2 于老师(4 行受影响)
*/
create table aa
(
id char(2),
b nvarchar(20),
a nvarchar(20),
sh int
)
insert aa select '1','张三','班级1',null
insert aa select '2','李四','班级1',null
insert aa select '3','王五','班级2',null
insert aa select '4','王五2','班级2',null
insert aa select '5','王五3','班级1',null create table bb
(
id char(2),
c nvarchar(20),
a nvarchar(20),
sh int
)insert bb select '1','李老师','班级1' ,null
insert bb select '2','黄老师','班级2' ,null
insert bb select '3','于老师','班级2',null
insert bb select '4','赵老师','班级1',null
declare @i int
set @i=1
while (1>0)
begin
--select a,min(id)id,@i sh from aa where sh is null group by a update aa set sh=@i from aa a,(select a,min(id)id from aa where sh is null group by a) b where a.id=b.id
if (@@rowcount=0) begin break end
set @i=@i+1
endset @i=1
while (1>0)
begin
update bb set sh=@i from bb a,(select a,min(id)id from bb where sh is null group by a) b where a.id=b.id
if (@@rowcount=0) begin break end
set @i=@i+1
end
select case when aa.a is null then bb.a else aa.a end as a,aa.b,bb.c
from aa full Join bb on aa.a=bb.a and aa.sh=bb.sh
drop table aa
drop table bb
insert into A values('01','张三','班级1')
insert into A values('02','李四','班级1')
insert into A values('03','王五','班级2')
insert into A values('04','aa','班级1')create table B(t_id varchar(10),t_name varchar(10),class_name varchar(10))
insert into B values('01','李老师','班级1')
insert into B values('02','黄老师','班级1')
insert into B values('03','于老师','班级2')
insert into B values('04','于老师1','班级2') goselect class_name ,
case when t_name = (select min(t_name) from
(
select B.class_name , A.stu_name , B.t_name
from B left join A
on B.class_name = A.class_name
) t
where stu_name = m.stu_name) then stu_name else '' end as stu_name,
case when stu_name = (select max(stu_name) from
(
select B.class_name , A.stu_name , B.t_name
from B left join A
on B.class_name = A.class_name
) t
where t_name = m.t_name) then t_name else '' end as t_name
from
(
select B.class_name , A.stu_name , B.t_name
from B left join A
on B.class_name = A.class_name
) mdrop table A,Bclass_name stu_name t_name
---------- ---------- ----------
班级1 李老师
班级1
班级1
班级1 张三 黄老师
班级1 李四
班级1 aa
班级2 王五 于老师
班级2 于老师1
(8 行受影响)
create table A(stu_id varchar(10),stu_name varchar(10),class_name varchar(10) )
insert into A values('01','张三','班级1')
insert into A values('02','李四','班级1')
insert into A values('03','王五','班级2')
create table B(t_id varchar(10),t_name varchar(10),class_name varchar(10))
insert into B values('01','李老师','班级1')
insert into B values('02','黄老师','班级2')
insert into B values('03','于老师','班级2')
go
select px = identity(int,1,1), B.class_name , A.stu_name , B.t_name
into temp
from B left join A
on B.class_name = A.class_name
select class_name ,
case when px = (select min(px) from temp where stu_name = m.stu_name) then stu_name else '' end as stu_name,
case when px = (select min(px) from temp where t_name = m.t_name) then t_name else '' end as t_name
from temp mdrop table A,B,temp/*
class_name stu_name t_name
---------- ---------- ----------
班级1 张三 李老师
班级1 李四
班级2 王五 黄老师
班级2 于老师(4 行受影响)
*/
create table A(stu_id varchar(10),stu_name varchar(10),class_name varchar(10) )
insert into A values('01','张三','班级1')
insert into A values('02','李四','班级1')
insert into A values('03','王五','班级2')
insert into A values('04','aa' ,'班级1') create table B(t_id varchar(10),t_name varchar(10),class_name varchar(10))
insert into B values('01','李老师','班级1')
insert into B values('02','黄老师','班级2')
insert into B values('03','于老师','班级2')
insert into B values('04','于老师1','班级2')
go
select px = identity(int,1,1), B.class_name , A.stu_name , B.t_name
into temp
from B left join A
on B.class_name = A.class_name
select class_name ,
case when px = (select min(px) from temp where stu_name = m.stu_name) then stu_name else '' end as stu_name,
case when px = (select min(px) from temp where t_name = m.t_name) then t_name else '' end as t_name
from temp mdrop table A,B,temp/*
class_name stu_name t_name
---------- ---------- ----------
班级1 张三 李老师
班级1 李四
班级1 aa
班级2 王五 黄老师
班级2 于老师
班级2 于老师1(6 行受影响)
*/
create table A(stu_id varchar(10),stu_name varchar(10),class_name varchar(10) )
insert into A values('01','张三','班级1')
insert into A values('02','李四','班级1')
insert into A values('03','王五','班级2')
insert into A values('04','aa' ,'班级1')
create table B(t_id varchar(10),t_name varchar(10),class_name varchar(10))
insert into B values('01','李老师','班级1')
insert into B values('02','黄老师','班级2')
insert into B values('03','于老师','班级2')
insert into B values('04','于老师1','班级2')
go
select class_name ,
case when px = (select min(px) from
(
select B.class_name , A.stu_name , B.t_name , px = row_number() over(order by B.class_name , A.stu_name , B.t_name)
from B left join A
on B.class_name = A.class_name
) t
where stu_name = m.stu_name) then stu_name else '' end as stu_name,
case when px = (select min(px) from
(
select B.class_name , A.stu_name , B.t_name , px = row_number() over(order by B.class_name , A.stu_name , B.t_name)
from B left join A
on B.class_name = A.class_name
) t
where t_name = m.t_name) then t_name else '' end as t_name
from
(
select B.class_name , A.stu_name , B.t_name , px = row_number() over(order by B.class_name , A.stu_name , B.t_name)
from B left join A
on B.class_name = A.class_name
) mdrop table A,B/*
class_name stu_name t_name
---------- ---------- ----------
班级1 aa 李老师
班级1 李四
班级1 张三
班级2 王五 黄老师
班级2 于老师
班级2 于老师1
(6 行受影响)
*/