27,579
社区成员
发帖
与我相关
我的任务
分享
declare @tb_studenttype table (id varchar(3),name varchar(5))
insert into @tb_studenttype
select '001','类型1' union all
select '002','类型2' union all
select '003','类型3'
declare @tb_student table (id varchar(3),name varchar(4),typeid varchar(3))
insert into @tb_student
select '001','张三','001' union all
select '002','李四','001' union all
select '003','王五','002' union all
select '004','李六','003'
select id,name as 类型,null as 学生编号,null as 学生姓名 from @tb_studenttype
union all
select a.id,null,b.id,b.name from @tb_studenttype a,@tb_student b where a.id=b.typeid
order by 1,2 desc
/*
id 类型 学生编号 学生姓名
---- ----- ---- ----
001 类型1 NULL NULL
001 NULL 001 张三
001 NULL 002 李四
002 类型2 NULL NULL
002 NULL 003 王五
003 类型3 NULL NULL
003 NULL 004 李六
(7 行受影响)
select name as 类型,null as 学生编号,null as 学生姓名 from tb_studenttype
union all
select null,b.id,b.name from tb_studenttype a,tb_student b where a.id=b.typeid
order by 1
declare @tb_studenttype table (id varchar(3),name varchar(5))
insert into @tb_studenttype
select '001','类型1' union all
select '002','类型2' union all
select '003','类型3'
declare @tb_student table (id varchar(3),name varchar(4),typeid varchar(3))
insert into @tb_student
select '001','张三','001' union all
select '002','李四','001' union all
select '003','王五','002' union all
select '004','李六','003'
select id=name,name=null,id from @tb_studenttype union all
select * from @tb_student
order by 3,2
/*
id name id
----- ---- ----
类型1 NULL 001
002 李四 001
001 张三 001
类型2 NULL 002
003 王五 002
类型3 NULL 003
004 李六 003
*/