这样的报表怎么实现

[xc008]

环境:sqlserver2000

--类型表
create table ptype(tid int, tname varchar(30))
insert into ptype(tid, tname) values(1, 'aaa')
insert into ptype(tid, tname) values(2, 'bbb')
insert into ptype(tid, tname) values(3, 'ccc')

--类型详细表
create table ptypeDetail(tdid int, tid int, tdname varchar(30))
insert into ptypeDetail(tdid, tid, tdname) values(1,1,'aaa1')
insert into ptypeDetail(tdid, tid, tdname) values(2,1,'aaa2')
insert into ptypeDetail(tdid, tid, tdname) values(3,1,'aaa3')
insert into ptypeDetail(tdid, tid, tdname) values(4,1,'aaa4')
insert into ptypeDetail(tdid, tid, tdname) values(5,2,'bbb1')
insert into ptypeDetail(tdid, tid, tdname) values(6,2,'bbb2')
insert into ptypeDetail(tdid, tid, tdname) values(7,3,'ccc1')
insert into ptypeDetail(tdid, tid, tdname) values(8,3,'ccc2')
insert into ptypeDetail(tdid, tid, tdname) values(9,3,'ccc3')


--要求报表结果(取类型表对应详细的第一条记录)
--类型编号 类型名称 类型详细
--1 aaa aaa1
--2 bbb bbb1
--3 ccc ccc1


drop table ptype
drop table ptypeDetail

[ying_wzm]

select a.tid,a.tname,d.tdname from ptype a
left join (
select c.tid,c.tdname from
(select min(tdid) as new,tid from ptypeDetail group by tid) b
left join ptypeDetail c on b.new=c.tdid ) d
on a.tid =d.tid

[xiaxianshuang]

--类型表
create table ptype(tid int, tname varchar(30))
insert into ptype(tid, tname) values(1, 'aaa')
insert into ptype(tid, tname) values(2, 'bbb')
insert into ptype(tid, tname) values(3, 'ccc')

--类型详细表
create table ptypeDetail(tdid int, tid int, tdname varchar(30))
insert into ptypeDetail(tdid, tid, tdname) values(1,1,'aaa1')
insert into ptypeDetail(tdid, tid, tdname) values(2,1,'aaa2')
insert into ptypeDetail(tdid, tid, tdname) values(3,1,'aaa3')
insert into ptypeDetail(tdid, tid, tdname) values(4,1,'aaa4')
insert into ptypeDetail(tdid, tid, tdname) values(5,2,'bbb1')
insert into ptypeDetail(tdid, tid, tdname) values(6,2,'bbb2')
insert into ptypeDetail(tdid, tid, tdname) values(7,3,'ccc1')
insert into ptypeDetail(tdid, tid, tdname) values(8,3,'ccc2')
insert into ptypeDetail(tdid, tid, tdname) values(9,3,'ccc3')
insert into ptypeDetail(tdid, tid, tdname) values(9,5,'ccc3')


--要求报表结果(取类型表对应详细的第一条记录)
--类型编号 类型名称 类型详细
--1 aaa aaa1
--2 bbb bbb1
--3 ccc ccc1

select a.tid,a.tdid,a.tdname,b.tname
from ptypeDetail a left join ptype b on a.tid=b.tid


drop table ptype
drop table ptypeDetail


tid tdid tdname tname
----------- ----------- ------------------------------ ------------------------------
1 1 aaa1 aaa
1 2 aaa2 aaa
1 3 aaa3 aaa
1 4 aaa4 aaa
2 5 bbb1 bbb
2 6 bbb2 bbb
3 7 ccc1 ccc
3 8 ccc2 ccc
3 9 ccc3 ccc
5 9 ccc3 NULL

[jimoshatan]

--类型表 
create table ptype(tid int, tname varchar(30)) 
insert into ptype(tid, tname) values(1, 'aaa') 
insert into ptype(tid, tname) values(2, 'bbb') 
insert into ptype(tid, tname) values(3, 'ccc') 

--类型详细表 
create table ptypeDetail(tdid int, tid int, tdname varchar(30)) 
insert into ptypeDetail(tdid, tid, tdname) values(1,1,'aaa1') 
insert into ptypeDetail(tdid, tid, tdname) values(2,1,'aaa2') 
insert into ptypeDetail(tdid, tid, tdname) values(3,1,'aaa3') 
insert into ptypeDetail(tdid, tid, tdname) values(4,1,'aaa4') 
insert into ptypeDetail(tdid, tid, tdname) values(5,2,'bbb1') 
insert into ptypeDetail(tdid, tid, tdname) values(6,2,'bbb2') 
insert into ptypeDetail(tdid, tid, tdname) values(7,3,'ccc1') 
insert into ptypeDetail(tdid, tid, tdname) values(8,3,'ccc2') 
insert into ptypeDetail(tdid, tid, tdname) values(9,3,'ccc3') 


select  a.*,b.tdname
 from ptype a left join 
( select * from ptypeDetail t where (select count(*) from ptypeDetail where tid=t.tid and tdid<t.tdid)<1)b on a.tid=b.tid

drop table ptypeDetail,ptype

/**

tid         tname                          tdname                         
----------- ------------------------------ ------------------------------ 
1           aaa                            aaa1
2           bbb                            bbb1
3           ccc                            ccc1

(3개 행 적용됨)

[jiang5311]

不清楚，帮顶下吧

[百年树人]

select 
  a.tid,
  a.tname,
  tdname=(select top 1 tdname from ptypeDetail where tid=a.tid order by tdname) 
from ptype a
/**
tid         tname                          tdname                         
----------- ------------------------------ ------------------------------ 
1           aaa                            aaa1
2           bbb                            bbb1
3           ccc                            ccc1

（所影响的行数为 3 行）
**/

[ilovewalk]

create table ptype(tid int, tname varchar(30))
insert into ptype(tid, tname) values(1, 'aaa')
insert into ptype(tid, tname) values(2, 'bbb')
insert into ptype(tid, tname) values(3, 'ccc')

--类型详细表
create table ptypeDetail(tdid int, tid int, tdname varchar(30))
insert into ptypeDetail(tdid, tid, tdname) values(1,1,'aaa1')
insert into ptypeDetail(tdid, tid, tdname) values(2,1,'aaa2')
insert into ptypeDetail(tdid, tid, tdname) values(3,1,'aaa3')
insert into ptypeDetail(tdid, tid, tdname) values(4,1,'aaa4')
insert into ptypeDetail(tdid, tid, tdname) values(5,2,'bbb1')
insert into ptypeDetail(tdid, tid, tdname) values(6,2,'bbb2')
insert into ptypeDetail(tdid, tid, tdname) values(7,3,'ccc1')
insert into ptypeDetail(tdid, tid, tdname) values(8,3,'ccc2')
insert into ptypeDetail(tdid, tid, tdname) values(9,3,'ccc3')
GO
SELECT p.tid,tname,tdname FROM ptype p
INNER JOIN (SELECT tdid=MIN(tdid),tid FROM ptypeDetail GROUP BY tid) t
ON p.tid=t.tid
INNER JOIN ptypeDetail pt ON t.tdid=pt.tdid

tid tname tdname
----------- ------------------------------ ------------------------------
1 aaa aaa1
2 bbb bbb1
3 ccc ccc1

（所影响的行数为 3 行）