主子表 取子表的第一条数据 并与主表一起显示

当主表需要子表字段较少的情况下可以使用

SELECT 
a.*
,(select top 1 b.id from table1 where a.mianid=b.main order by b.id) as 字段1
,(select top 1 b.name from table1 where a.mianid=b.main order by b.id) as 字段2
from maintable  as a 

 

当主表需要子表字段较多的情况下可以使用

 

SELECT 

*
from maintable  as a 
	left join  table1  as b on a.mainid=b.mainid and b.id=(select top 1 id from  table1 as b1 where b1.mainid=a.mainid order by b1.id)
	left join  table2  as c ON c.mainid=a.mainid and c.id=(select top 1 id from  table2 as c1 where c1.mainid=a.mainid order by c1.id)
	left JOIN  ttable1 as d on c.id=d.t1id       and d.id=(select top 1 id from  ttable1 as d1 where c.id=d.t1id order by d1.id)
where a.id>''

高手指导的语句,比我的要快很多

select *,c.* from maintable as a
inner join (select min(id) as id from table1 ) as b 
inner join table1  as c on b.id=c.id

 

两表关联取子表包含某一内容的

select id from tablea as a

where a.id in(select top 1 aa.id from tableb as b on a.id=b.id where b.re like '%aa%')

展开阅读全文

没有更多推荐了,返回首页