表a,有字段id,表b有字段cid,create_time,a表中的id对应b表中的cid,a表左连接b表,现在a中一条数据对应b中多条数据,如何只取b表中create_time最大的一条 ?
CREATE
TABLE
a (id INT
);
CREATE
TABLE
B (cid INT,
create_timeDATETIME);
INSERT
INTO
a SELECT
1 UNION SELECT
2;
INSERT
INTO
b
SELECT
1,
'2010-10-01'
UNION
SELECT
1,
'2010-10-02'
UNION
SELECT
2,
'2010-10-01'
UNION
SELECT
2,
'2010-10-02'
UNION
SELECT
2,
'2010-10-03';
SELECT
*
FROM
a LEFT
JOIN
(
SELECT
MAX(
create_time)AS
create_time,cid FROM
b
GROUP
BY
cid) b1
ON
b1.cid = a.id;