mysql - SQL关联查询问题

问题描述

我有两张表，表一有字段 a_id,name

表二有字段 b_id,a_id,createtime

两个表的a_id是关联的，并且是一对多的关系。

请问怎么能通过1个sql查询出 a_id,name,b_id

其中b_id是createtime最小的行对应的b_id.

问题解答

以下 SQL ok, 直接上图

附执行SQL

SELECT t1.a_id, t1. NAME, t2.b_id, t2.create_timeFROM a AS t1LEFT OUTER JOIN b AS t2 ON t1.a_id = t2.a_idWHERE t2.b_id = (SELECT b.b_idFROM bWHERE a_id = t1.a_idORDER BY create_time ASCLIMIT 1 )回答2：

select tb1.a_id,tb2.b_id,name from tb1 left join (select a_id,min(createtime) as min_time from tb2 group by a_id) t on t.a_id = tb1.a_idleft join tb2 on tb2.a_id = tb1.a_id and tb2.createtime = t.min_time

你看这样可行吗？

create table a (a_id int,name varchar(15));create table b (b_id int ,a_id int,create_time datetime);insert into a set a_id=1,name=’1’;insert into a set a_id=2,name=’2’;insert into b set b_id=1,a_id=1,create_time=now();insert into b set b_id=2,a_id=1,create_time=now();insert into b set b_id=3,a_id=1,create_time=now();insert into b set b_id=4,a_id=2,create_time=now();insert into b set b_id=5,a_id=2,create_time=now();select a.a_id,name,b_id,create_time from a,(select * from b group by a_id order by create_time asc ) c where a.a_id=c.a_id ;+------+------+------+---------------------+| a_id | name | b_id | create_time |+------+------+------+---------------------+| 1 | 1 | 1 | 2016-11-24 18:34:56 || 2 | 2 | 4 | 2016-11-24 18:35:53 |+------+------+------+---------------------+