sql 查询某个条件下多条数据中最新的一条数据或最老的一条数据
test_user表结构如下:
需求:查询李四、王五、李二创建的最初时间或者最新时间
1:查询最初的创建时间:
SELECT
*
FROM(
SELECT
*
FROM test_user
) AS tu
WHERE NOT EXISTS (
SELECT * FROM(
SELECT
*
FROM test_user
) AS tu2 WHERE tu2.user_name=tu.user_name AND DATE(tu.time)>DATE(tu2.time)
)
查询结果如下:
2:查询最新的记录
SELECT
*
FROM(
SELECT
*
FROM test_user
) AS tu
WHERE NOT EXISTS (
SELECT * FROM(
SELECT
*
FROM test_user
) AS tu2 WHERE tu2.user_name=tu.user_name AND DATE(tu.time)<DATE(tu2.time)
)
查询结果如下: