mysql - 获取加入结果的最新记录

Question

我的数据库中有以下表格:

ATable
a_id | a_name
1    | name1
2    | name2
...

BTable
a_id | b_id | b_name
1    | 1    | name1
1    | 2    | name2
2    | 3    | name1
...

CTable
b_id | c_id | c_name
1    | 1    | name1
1    | 2    | name2
2    | 3    | name1
...

ValueTable
b_id | c_id | value | date
1    | 1    | abcd  | 2018-01-19 03:14:07
1    | 1    | wxyz  | 2018-01-20 10:45:15
2    | 3    | abcd2 | 2018-02-05 10:11:12
1    | 2    | abcd3 | 2018-02-11 14:29:36

对象 A 有一个对象 B 的列表。对象 B 有一个对象 C 的列表。对于给定的 b_id 和 c_id 组合，ValueTable 中可以有一个或多个值。

对于给定 a_id 下的 b_id 和 c_id 的有效组合，我想要 ValueTable 中最新插入的值。

对于上面的例子，我想要下面的输出:

b_id | c_id | value | date
1    | 1    | wxyz  | 2018-01-20 10:45:15
1    | 2    | abcd3 | 2018-02-11 14:29:36
2    | 3    | abcd2 | 2018-02-05 10:11:12

现在，我正在做的是:

select c.c_id, b.b_id
from ATable a
    join BTable b on a.a_id = b.a_id
    join CTable c on b.b_id = c.b_id
where a.a_id=?

然后，对于结果中的每一对 b_id 和 c_id，我执行:

select value, date
from ValueTable
where b_id=? and c_id=?
order by date desc limit 1

是否有一个查询可以完成这项工作？

示例模式:

create table ATable (
    a_id int auto_increment,
    a_name varchar(20),
    primary key (a_id)
);
create table BTable (
    b_id int auto_increment,
    a_id int,
    b_name varchar(20),
    primary key (b_id),
    foreign key (a_id) references ATable(a_id)
);
create table CTable (
    c_id int auto_increment,
    b_id int,
    c_name varchar(20),
    primary key (c_id),
    foreign key (b_id) references BTable(b_id)
);
create table ValueTable (
    b_id int,
    c_id int,
    val varchar(20),
    created timestamp DEFAULT CURRENT_TIMESTAMP,
    foreign key (c_id) references CTable(c_id),
    foreign key (b_id) references BTable(b_id)
);

insert into ATable (a_name) values
('a1'),('a2');
insert into BTable (a_id, b_name) values
(1, 'b1_1'), (1, 'b1_2'), (2, 'b2');
insert into CTable (b_id, c_name) values
(1, 'c1_1'), (1, 'c1_2'), (1, 'c1_3'),
(2, 'c2_1'), (2, 'c2_2'), (3, 'c3');
insert into ValueTable (b_id, c_id, val) values
(1, 1, 'value1'),
(1, 2, 'value2'),
(1, 3, 'value3'),
(2, 4, 'value4'),
(2, 5, 'value5'),
(3, 6, 'value6');
insert into ValueTable (b_id, c_id, val) values
(1, 1, 'value7'),
(1, 2, 'value8'),
(1, 3, 'value9'),
(2, 4, 'value10'),
(2, 5, 'value12'),
(3, 6, 'value13');
insert into ValueTable (b_id, c_id, val) values
(1, 1, 'value14'),
(1, 2, 'value15'),
(1, 3, 'value16'),
(2, 4, 'value17'),
(2, 5, 'value18'),
(3, 6, 'value19');

运行 ValueTable 插入几秒钟后，我想要的输出是:

a_id=1
b_id | c_id | value
1    | 1    | value14
1    | 2    | value15
1    | 3    | value16
2    | 4    | value17
2    | 5    | value18

Answer 1

问题的核心可以这样解决:

SELECT x.* 
  FROM valuetable x 
  JOIN 
     ( SELECT b_id
            , c_id
            , MAX(created) created 
         FROM valuetable 
        GROUP 
           BY b_id
            , c_id
     ) y 
    ON y.b_id = x.b_id 
   AND y.c_id = x.c_id 
   AND y.created = x.created;
+------+------+---------+---------------------+
| b_id | c_id | val     | created             |
+------+------+---------+---------------------+
|    1 |    1 | value14 | 2018-03-20 10:13:14 |
|    1 |    2 | value15 | 2018-03-20 10:13:14 |
|    1 |    3 | value16 | 2018-03-20 10:13:14 |
|    2 |    4 | value17 | 2018-03-20 10:13:14 |
|    2 |    5 | value18 | 2018-03-20 10:13:14 |
|    3 |    6 | value19 | 2018-03-20 10:13:14 |
+------+------+---------+---------------------+

我会把剩下的留给读者作为练习