主从表查询

2012-04-10 09:55:42 325 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

Rust练手：从零手撸RISC-V架构虚拟机（一）(附完整代码) 使用Rust实现一个简单的RISC-V架构的CPU雏形，支持add，sub，addi三个指令。阅读详情

主表字段



id int

title varchar

从表字段



id int

parentid int  --主表的ID

filename varchar

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

...全文

告别概念晕眩：用TC3xx SMU和TLF35584的配置实例，搞懂ASIL D功能安全落地本文通过英飞凌TC3xx系列MCU的SMU模块与TLF35584电源管理芯片的协同配置实例，详细解析了如何实现符合ASIL D功能安全标准的刹车辅助ECU。文章涵盖SMU报警源配置、响应动作类型、TLF35584的安全状态管理，以及构建完整安全闭环的实践方法，帮助工程师将抽象的安全概念落地为具体设计方案。阅读详情

Taiji02

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

APHY 2012-04-10

打赏
举报

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

*/

Taiji02 2012-04-10

打赏
举报

[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]
你这个没有测试,也不对.我要求主表的字段只能出现一次.

JayPan2008 2012-04-10

打赏
举报



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<>''

试试

APHY 2012-04-10

打赏
举报

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

Taiji02 2012-04-10

打赏
举报

最后结果需要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 2012-04-10

打赏
举报

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

APHY 2012-04-10

打赏
举报

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

wfkmu 2012-04-10

打赏
举报

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

不知道对不对。试试

kingtiy 2012-04-10

打赏
举报

select top 25 a.* from ta a join tb b on a.id=b.parentid 

where b.filename is not null or filename<>''

Taiji02 2012-04-10

打赏
举报

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



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

Taiji02 2012-04-10

打赏
举报



1	test1	1	file1

2	test2	1	file2--这个是有问题的

3	test3	2	file3

4	test4	3	file4

6	test6	4	file6

APHY 2012-04-10

打赏
举报

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 2012-04-10

打赏
举报

[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呢

主从表查询

相关推荐