每个ID的SELECT MAX DATE

I have two calls this "tipo_hh" and "tipo_hh_historial".

我有两个电话叫“tipo_hh”和“tipo_hh_historial”。

I need to make a join between the two tables, where "id" is the same in both tables. But I need that for each "id" in the table "tipo_hh" select the "valor" on the table "tipo_hh_historial" with the condition that is the record with "fecha_cambio" and "hora_cambio" maxima.

我需要在两个表之间建立连接，其中“id”在两个表中都是相同的。但是我需要为表格“tipo_hh”中的每个“id”选择表“tipo_hh_historial”上的“valor”，条件是带有“fecha_cambio”和“hora_cambio”maxima的记录。

"id" is primary key and auto increment in the table "tipo_hh"

“id”是表“tipo_hh”中的主键和自动增量

Something like this.

像这样的东西。

This is the table "tipo_hh"

这是表“tipo_hh”

id  nombre
1   Reefer
2   Lavados
3   Dry
4   Despacho

This is the table "tipo_hh_historial"

这是表“tipo_hh_historial”

id  valor   fecha_cambio    hora_cambio
1   1.50    27/06/2013  19:15:05
1   5.50    27/06/2013  19:19:32
1   5.50    27/06/2013  19:20:06
1   2.50    27/06/2013  21:03:30
2   4.66    27/06/2013  19:15:17
2   3.00    27/06/2013  19:20:22
3   5.00    27/06/2013  19:20:32
4   1.50    27/06/2013  19:20:50

And I need this:

我需要这个：

id  nombre     valor
1   Reefer     2.50
2   Lavados    3.00
3   Dry        5.00
4   Despacho   1.50

4 个解决方案

#1

Using a sub query to get the max date / time for the historical record for each id, and using that to get the rest of the latest historical record:-

使用子查询获取每个id的历史记录的最大日期/时间，并使用它来获取最新的历史记录的其余部分： -

SELECT tipo_hh.id, tipo_hh.nombre, tipo_hh_historial.valor
FROM tipo_hh
INNER JOIN 
(
    SELECT id, MAX(STR_TO_DATE(CONCAT(fecha_cambio, hora_cambio), '%d/%m/%Y%k:%i:%s')) AS MaxDateTime
    FROM tipo_hh_historial
    GROUP BY id
) Sub1
ON tipo_hh.id = Sub1.id
INNER JOIN tipo_hh_historial
ON tipo_hh_historial.id = Sub1.id
AND STR_TO_DATE(CONCAT(fecha_cambio, hora_cambio), '%d/%m/%Y%k:%i:%s') = Sub1.MaxDateTime

SQL Fiddle:-

SQL小提琴： -

http://www.sqlfiddle.com/#!2/68baa/2

http://www.sqlfiddle.com/#!2/68baa/2

#2

First of all you should use proper data types for your columns like for date there should a column of type data same as for the time column in you sample data set you have date formatted as '%d/%m/%Y' id this could be change to standard format '%Y-%m-%d' this will be good to so the below query is for proper types for the columns

首先，您应该为您的列使用正确的数据类型，例如日期应该有一个类型数据列与您的样本数据集中的时间列相同，您将日期格式化为'％d /％m /％Y'id this可能会更改为标准格式'％Y-％m-％d'这将是好的，所以下面的查询是为列的正确类型

SELECT t.* ,new_tipo_hh_historial.`valor`
FROM tipo_hh_new t
JOIN (
SELECT  th.*
FROM tipo_hh_historial_new th
JOIN (
SELECT id,valor,
 MAX(fecha_cambio ) fecha_cambio
,MAX(hora_cambio) hora_cambio
FROM `tipo_hh_historial_new` 
GROUP BY id
) thh 
ON (
th.`id` =thh.`id` 
AND th.fecha_cambio=thh.`fecha_cambio` 
AND th.hora_cambio = thh.`hora_cambio`
)
) new_tipo_hh_historial 
USING (id)

Fiddle Demo

And for in case you have date and time stored as string then you need to format them as real types you can use below query but not recommended

如果您将日期和时间存储为字符串，则需要将它们格式化为可在查询下方使用的实际类型，但不建议使用

SELECT t.* ,new_tipo_hh_historial.`valor`
FROM tipo_hh t
JOIN (
SELECT  th.*
FROM tipo_hh_historial th
JOIN (
SELECT id,valor,
 MAX(STR_TO_DATE(fecha_cambio , '%d/%m/%Y')) fecha_cambio
,MAX(TIME_FORMAT(hora_cambio,'%H:%i:%s')) hora_cambio
FROM `tipo_hh_historial` 
GROUP BY id
) thh 
ON (
th.`id` =thh.`id` 
AND STR_TO_DATE(th.fecha_cambio , '%d/%m/%Y')=thh.`fecha_cambio` 
AND TIME_FORMAT(th.hora_cambio,'%H:%i:%s') = thh.`hora_cambio`
)
) new_tipo_hh_historial 
USING (id)

Fiddle Demo

Your problem seems like the greatest-n-per-group problem so you can first get the maxima from your table tipo_hh_historial maxima of fecha_cambio and hora_cambio and need to self join with multiple conditions to get the maximums like i.e

你的问题似乎是每组最大的问题所以你可以先从fecha_cambio和hora_cambio的表tipo_hh_historial maxima中获得最大值，并且需要自我加入多个条件以获得最大值，即

ON (
th.`id` =thh.`id` 
AND th.fecha_cambio=thh.`fecha_cambio` 
AND th.hora_cambio = thh.`hora_cambio`
)

and then join with your first table to get the expected results

然后加入第一个表格以获得预期结果

Edit: the problem spotted by @Kickstart he already answered so i will provide the another way to overcome.There should be single field to store the date and time for the record like for fecha_cambio DATETIME so there will no chance to miss the id and get the correct maxima for date and time.See below updated query

编辑：@Kickstart发现的问题他已经回答了，所以我将提供另一种方法来克服。应该有单个字段来存储记录的日期和时间，如fecha_cambio DATETIME所以没有机会错过id并得到日期和时间的正确最大值。请参阅下面更新的查询

SELECT t.* ,new_tipo_hh_historial.`valor`
FROM tipo_hh_new t
JOIN (
SELECT  th.*
FROM tipo_hh_historial_alter th
JOIN (
SELECT id,valor,
 MAX(fecha_cambio ) fecha_cambio
FROM `tipo_hh_historial_alter` 
GROUP BY id
) thh 
ON (
th.`id` =thh.`id` 
AND th.fecha_cambio=thh.`fecha_cambio` 
)
) new_tipo_hh_historial 
USING (id)

Updated fiddle demo

#3

try this:

尝试这个：

SELECT A.id, B.nombre, A.valor, MAX(A.hora_cambio) AS hora_cambio_time
FROM tipo_hh_historial AS A
INNER JOIN tipo_hh AS B
ON(A.id = B.id)
GROUP BY A.id

#4

SELECT tipo_hh.id, tipo_hh.nombre, tipo_hh_historial.valor
   FROM tipo_hh INNER JOIN tipo_hh_historial 
   ON tipo.id = tipo_hh_historial.id AS 
   group by tipo_hh_historial.id 
   Having max(tipo_hh_historial.hora_cambio);