问题描述:

I have the following two MySQL tables

TABLE NAMES

NAME_ID NAME

1 name1

2 name2

3 name3

TABLE STATUS

STATUS_ID NAME_ID TIMESTAMP

1 1 2010-12-20 12:00

2 2 2010-12-20 10:00

3 3 2010-12-20 10:30

4 3 2010-12-20 14:00

I would like to select all info from table NAMES and add most recent correspondent TIMESTAMP column from table STATUS

RESULT

NAME_ID NAME TIMESTAMP

1 name1 2010-12-20 12:00

2 name2 2010-12-20 10:00

3 name3 2010-12-20 14:00

Am stuck on this one.

How do I left join only on the newer timestamp?

网友答案:

try this query :

select n.NAME_ID ,  n.NAME , max(TIMESTAMP) as time from NAMES n left join 
STATUS s on s.NAME_ID = n.NAME_ID group by n.NAME_ID
网友答案:
SELECT  *
FROM    table_names tn
LEFT JOIN
        table_status ts
ON      ts.status_id = 
        (
        SELECT  status_id
        FROM    table_status tsi
        WHERE   tsi.name_id = tn.name_id
        ORDER BY
                name_id DESC, TIMESTAMP DESC, status_id DESC
        LIMIT 1
        )

This will handle duplicates correctly.

Create an index on table_status (name_id, timestamp, status_id) for this to work fast.

网友答案:
SELECT
    status.*
FROM
    status
        JOIN
            (SELECT name_id, MAX(timestamp)AS latest FROM status GROUP BY name_id) AS sub 
            ON (status.name_id = sub.name_id AND status.timestamp = sub.latest);
相关阅读:
Top