mysql - SQL关联查询问题

Question

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

表二有字段
b_id,a_id,createtime

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

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

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

Answer 1

以下 SQL ok, 直接上图

附执行SQL

SELECT
    t1.a_id,
    t1. NAME,
    t2.b_id,
    t2.create_time
FROM
    a AS t1
LEFT OUTER JOIN b AS t2 ON t1.a_id = t2.a_id
WHERE
    t2.b_id = (
        SELECT
            b.b_id
        FROM
            b
        WHERE
            a_id = t1.a_id
        ORDER BY
            create_time ASC
        LIMIT 1
    )

Answer 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_id
left join tb2 on tb2.a_id = tb1.a_id and tb2.createtime = t.min_time

你看这样可行吗？

Answer 3

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