主从表查询

[Taiji02]

主表字段

id int
title varchar

从表字段

id int
parentid int  --主表的ID
filename varchar

简单的1对n的关系,要求查出主表中的25条,filename不为空的记录;从表的记录没有约定,只要一条filename不为空即满足条件.

[Taiji02]

[Quote=引用 10 楼 的回复:]

SQL code
SELECT distinct T1.ID,T1.TITLE,T2.parentid FROM
(SELECT  TOP 25  A.ID,A.TITLE FROM  temp_main a,temp_detail b WHERE a.id=b.parentid AND( b.filename is not null AND b.filename<>'')) T1,temp_……

[/Quote]
谢谢你,从表的filename呢

[APHY]

SELECT distinct T1.ID,T1.TITLE,T2.parentid,T2.filename FROM
(SELECT  TOP 25  A.ID,A.TITLE FROM  temp_main a,temp_detail b WHERE a.id=b.parentid AND( b.filename is not null AND b.filename<>'')) T1,temp_detail T2
WHERE T1.ID=T2.ID

[Taiji02]

1	test1	1	file1
2	test2	1	file2--这个是有问题的
3	test3	2	file3
4	test4	3	file4
6	test6	4	file6

[Taiji02]

没办法,只有用临时表解决:

if object_id(N'#1') is not null drop table #1
--declare @colname varchar(40)
create table #1 (id int,title varchar(40),filename varchar(50))
insert into #1(id,title) select id,title from temp_main where id in(select distinct parentid from temp_detail)
update #1 set filename=(select top 1 filename from temp_detail where parentid=#1.id)
select * from #1
drop table #1

[kingtiy]

select top 25 a.* from ta a join tb b on a.id=b.parentid 
where b.filename is not null or filename<>''

[wfkmu]

select top 25 a.* from tb a,tb b where a.id=b.id and b.filename is not null

不知道对不对。试试

[APHY]

SELECT  top 25 a.* FROM  ta a,tb b WHERE a.id=b.parentid AND( b.filename is not null AND filename<>'')

[APHY]

SELECT  TOP 25 a.* FROM  ta a,tb b WHERE a.id=b.parentid AND( b.filename is not null AND b.filename<>'')

[Taiji02]

最后结果需要filename这个字段.
测试数据:

create table temp_main
(
[id][int]IDENTITY (1, 1) NOT NULL ,
[title][varchar](30) null,
	CONSTRAINT [PK_temp_main] PRIMARY KEY  CLUSTERED 
	(
		[id]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO	
create table temp_detail
(
[id][int]IDENTITY (1, 1) NOT NULL ,
[parentid][int]null,
[filename][varchar](30) null,
	CONSTRAINT [PK_temp_detail] PRIMARY KEY  CLUSTERED 
	(
		[id]
	)  ON [PRIMARY] 
) ON [PRIMARY]
GO	

insert into temp_main(title)values('test1')
insert into temp_main(title)values('test2')
insert into temp_main(title)values('test3')
insert into temp_main(title)values('test4')
insert into temp_main(title)values('test5')
insert into temp_main(title)values('test6')
insert into temp_detail(parentid,filename)values(1,'file1')
insert into temp_detail(parentid,filename)values(1,'file2')
insert into temp_detail(parentid,filename)values(2,'file3')
insert into temp_detail(parentid,filename)values(3,'file4')
insert into temp_detail(parentid,filename)values(3,'file5')
insert into temp_detail(parentid,filename)values(4,'file6')
insert into temp_detail(parentid,filename)values(6,'file7')
insert into temp_detail(parentid,filename)values(6,'file8')

go

最后结果:
主表的ID,title和从表的filename:
id title filename

[APHY]

SELECT T1.ID,T1.TITLE,T2.parentid
(SELECT  TOP 25  ID,TITLE FROM  ta a,tb b WHERE a.id=b.parentid AND( b.filename is not null AND b.filename<>'')) T1,tb T2
WHERE T1.ID=T2.ID

[JayPan2008]

select top 25 p.id,p.title,s.filename from temp_main as p inner join temp_detail as s
on p.id=s.parentid 
where filename is not null or filename<>''

试试

[Taiji02]

[Quote=引用 6 楼 的回复:]

SQL code
SELECT T1.ID,T1.TITLE,T2.parentid
(SELECT TOP 25 ID,TITLE FROM ta a,tb b WHERE a.id=b.parentid AND( b.filename is not null AND b.filename<>'')) T1,tb T2
WHERE T1.ID=T2.ID
[/Quote]
你这个没有测试,也不对.我要求主表的字段只能出现一次.

[APHY]

SELECT distinct T1.ID,T1.TITLE,T2.parentid FROM
(SELECT  TOP 25  A.ID,A.TITLE FROM  temp_main a,temp_detail b WHERE a.id=b.parentid AND( b.filename is not null AND b.filename<>'')) T1,temp_detail T2
WHERE T1.ID=T2.ID

/*
1	test1	1
2	test2	1
3	test3	2
4	test4	3
6	test6	4
*/