有表t如下:
id(auto) customerID num ...
13 1 12 ...
14 1 15 ...
15 2 14 ...
16 2 10 ...
17 4 14 ...我怎样得到按customerID对应ID最大的记录集
(结果是每个customerID只对应一条最新记录)如:id(auto) customerID num ...
14 1 15 ...
16 2 10 ...
17 4 14 ...
mysql4.0不支持嵌套查询?
id(auto) customerID num ...
13 1 12 ...
14 1 15 ...
15 2 14 ...
16 2 10 ...
17 4 14 ...我怎样得到按customerID对应ID最大的记录集
(结果是每个customerID只对应一条最新记录)如:id(auto) customerID num ...
14 1 15 ...
16 2 10 ...
17 4 14 ...
mysql4.0不支持嵌套查询?
AS
SELECT customerID,MAX(id) AS id
FROM tb
GROUP BY customerID;SELECT
A.*
FROM tb AS A
JOIN tmp_tb AS B
ON A.customerID=B.customerID
AND A.id=B.id;
FROM tb AS A
WHERE NOT EXISTS
(
SELECT *
FROM tb
WHERE customerID=A.customerID
AND id>A.id
);
where not exists (select id from t where customerID=t.customerID and id>t.id);
where not exists (select id from t where customerID=t.customerID and id>t.id)
You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'exists (select id from t where customerID=t.customerID and id>t
PS:我怎样得到按customerID对应ID最大的记录集SELECT customerID,MAX(id) AS id
FROM 你的table
GROUP BY customerID;1说的这个不行吗?
id(auto) customerID num ...
13 1 12 ...
14 1 15 ...
15 2 14 ...
16 2 10 ...
17 4 14 ... 我怎样得到按customerID对应ID最大的记录集
(结果是每个customerID只对应一条最新记录)如: id(auto) customerID num ...
14 1 15 ...
16 2 10 ...
17 4 14 ... select a.* from t a,(
select customerID,max(id) as iid from t goup by customerID) b
where a.customerID=b.customerID
and a.id=b.iid这个试试