MySQL:将表字段连接到相关结果集

Question

这个问题已经有答案了:

奥 git _a (27 个回答)

已关闭 7 年前。

我有两张 table 。

“参数”表(如下)跟踪附加到各个组件的参数:

id (pk) | name | component

“parameterLog”表跟踪参数随时间变化的值:

action_date | parameter (fk) | value | notes

我有以下结果集:

SELECT * FROM parameters WHERE component = 1;
+----+------+-----------+
| id | name | component |
+----+------+-----------+
|  1 | par1 |         1 |
|  2 | par2 |         1 |
+----+------+-----------+

对于上述结果集中的每个条目，我需要“parameterLog”表中相应的最新操作日期、值和注释。

[编辑]为了清楚起见，我在下面列出了带有数据的表格。

parameters:
+----+------+-----------+
| id | name | component |
+----+------+-----------+
|  1 | par1 |         1 |
|  2 | par2 |         1 |
|  3 | par1 |         2 |
|  4 | par2 |         2 |
|  5 | par1 |         3 |
|  6 | par2 |         3 |
|  7 | par3 |         3 |
|  8 | par1 |         4 |
|  9 | par2 |         4 |
| 10 | par1 |         6 |
| 11 | par2 |         6 |
+----+------+-----------+

parameterLog:
+-------------+-----------+-------+-------+
| action_date | parameter | value | notes |
+-------------+-----------+-------+-------+
| 2010-01-01  |         1 | 0     | NULL  |
| 2010-02-03  |         1 | 4     | NULL  |
| 2010-01-01  |         2 | 0     | NULL  |
| 2010-01-01  |         3 | 1     | NULL  |
| 2010-01-01  |         4 | 1     | NULL  |
| 2010-01-14  |         5 | 0     | NULL  |
| 2010-01-14  |         6 | 1     | NULL  |
| 2010-01-14  |         7 | 2     | NULL  |
| 2010-01-14  |         8 | 3     | NULL  |
| 2010-02-03  |         8 | 1     | NULL  |
| 2010-01-14  |         9 | 4     | NULL  |
| 2010-02-03  |         9 | 1     | NULL  |
| 2010-02-25  |        10 | 9     | NULL  |
| 2010-02-25  |        11 | 9     | NULL  |
+-------------+-----------+-------+-------+

Answer 1

SELECT param.*,
       plog.action_date,
       plog.value,
       plog.notes 
FROM parameters param,parameter_log plog 
WHERE param.id=plog.parameter 
      and action_date = (select max(action_date) 
                         from parameterLog 
                         where parameter = param.id) 
      and component = 1;