求一简单查询语句，要测试数据及语句，谢谢

[WeiWY]

有如下两个表
--测试数据
create table tba(aid int,aname varchar(20))
insert tba select 1 ,'计算机系'
union all select 2 ,'经管系'
union all select 3 ,'物理系'
union all select 4 ,'艺术系'
go

create table tbb(bid int ,aid int,bname varchar(20))
insert tbb select 1, 1 ,'计算机系下专业1'
union all select 2, 1 ,'计算机系下专业2'
union all select 3, 2 ,'经管系下专业1'
union all select 4, 2 ,'经管系下专业2'
union all select 5, 4 ,'艺术系下专业1'
go
--查询

--删除测试
--drop table tba
--drop table tbb
--希望查询结果如下：
aid aname bid bname
1 计算机系
1 计算机系下专业1
2 计算机系下专业2
2 经管系
3 经管系下专业1
4 经管系下专业2
3 物理系
4 艺术系
5 艺术系下专业1

[ChinaJiaBing]

 create  table  tba(aid  int,aname  varchar(20)) 
  insert  tba  select  1  ,'计算机系' 
  union  all  select  2  ,'经管系' 
  union  all  select  3  ,'物理系' 
  union  all  select  4  ,'艺术系' 
  go    
  create  table  tbb(bid int ,aid  int,bname  varchar(20)) 
  insert  tbb  select  1, 1  ,'计算机系下专业1' 
  union  all  select  2, 1  ,'计算机系下专业2' 
  union  all  select  3, 2  ,'经管系下专业1' 
  union  all  select  4, 2  ,'经管系下专业2' 
  union  all  select  5, 4  ,'艺术系下专业1' 
  select a.* ,b.bid,b.bname from tba a full join 
  (select  aid=case when exists (select * from tbb  where aid=a.aid and bid<a.bid)
  then '' else a.aid  end ,a.bid,a.bname 
  from  tbb a ) b on a.aid=b.aid
  order by bid  
  drop table tba
  drop table tbb

aid         aname                bid         bname
----------- -------------------- ----------- --------------------
3           物理系                  NULL        NULL
1           计算机系                 1           计算机系下专业1
NULL        NULL                 2           计算机系下专业2
2           经管系                  3           经管系下专业1
NULL        NULL                 4           经管系下专业2
4           艺术系                  5           艺术系下专业1

[jiujiang88]

SELECT A.aid,A.aname,B.bid,B.bname FROM dbo.tba A left join dbo.tbb B on A.aid=B.aid

[wsh236]

--测试数据
DECLARE @t TABLE(ID char(3),PID char(3),Name nvarchar(10))
INSERT @t SELECT '001',NULL ,'山东省'
UNION ALL SELECT '002','001','烟台市'
UNION ALL SELECT '004','002','招远市'
UNION ALL SELECT '003','001','青岛市'
UNION ALL SELECT '005',NULL ,'四会市'
UNION ALL SELECT '006','005','清远市'
UNION ALL SELECT '007','006','小分市'

--深度排序显示处理
--生成每个节点的编码累计(相同当单编号法的编码)
DECLARE @t_Level TABLE(ID char(3),Level int,Sort varchar(8000))
DECLARE @Level int
SET @Level=0
INSERT @t_Level SELECT ID,@Level,ID
FROM @t
WHERE PID IS NULL
WHILE @@ROWCOUNT>0
BEGIN
	SET @Level=@Level+1
	INSERT @t_Level SELECT a.ID,@Level,b.Sort+a.ID
	FROM @t a,@t_Level b
	WHERE a.PID=b.ID
		AND b.Level=@Level-1
END

--显示结果
SELECT SPACE(b.Level*2)+'|--'+a.Name
FROM @t a,@t_Level b
WHERE a.ID=b.ID
ORDER BY b.Sort
/*--结果
|--山东省
  |--烟台市
    |--招远市
  |--青岛市
|--四会市
  |--清远市
    |--小分市
--*/

[dawugui]

select aid , aname , bid , bname from
(
select cast(m.aid as varchar) aid , m.aname , bid = '' , bname = '' , px1 = m.aid , px2 = 0 from tba m
union all
select aid = '', aname = '' , cast(n.bid as varchar), n.bname , px1 = m.aid , px2 = n.bid from tba m , tbb n where m.aid = n.aid
) t
order by px1 , px2

/*
aid                            aname                bid                            bname                
------------------------------ -------------------- ------------------------------ -------------------- 
1                              计算机系                                                
                                                    1                              计算机系下专业1
                                                    2                              计算机系下专业2
2                              经管系                                                 
                                                    3                              经管系下专业1
                                                    4                              经管系下专业2
3                              物理系                                                 
4                              艺术系                                                 
                                                    5                              艺术系下专业1

（所影响的行数为 9 行）


*/

[pengxuan]

create  table  tba(aid  int,aname  varchar(20)) 
  insert  tba  select  1  ,'计算机系' 
  union  all  select  2  ,'经管系' 
  union  all  select  3  ,'物理系' 
  union  all  select  4  ,'艺术系' 
  go  
  
  create  table  tbb(bid int ,aid  int,bname  varchar(20)) 
  insert  tbb  select  1, 1  ,'计算机系下专业1' 
  union  all  select  2, 1  ,'计算机系下专业2' 
  union  all  select  3, 2  ,'经管系下专业1' 
  union  all  select  4, 2  ,'经管系下专业2' 
  union  all  select  5, 4  ,'艺术系下专业1' 
  go  


select aaid=case when aname='' then '' else aid end,aname,bid,bname from (
select aid=cast(aid as varchar(10)),aname,bid='',bname='' from tba
union all
select aid=cast(tba.aid as varchar(10)),aname='',cast(bid as varchar(10)),bname from tba inner join tbb on tba.aid=tbb.aid)a order by aid,aname desc