問題描述

我有兩張表，表一有字段 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 |+------+------+------+---------------------+