上面的sql通过login_time排序但是结果不对？

Question

上面的sql通过login_time排序但是结果不对？login_time类型是DATETIME

Answer 1

的SQL语句中有几个语法错误和拼写错误，这可能是导致结果不符合预期的原因。以下是修正后的SQL查询

SELECT 
  JSON_ARRAYAGG(
    JSON_OBJECT(
      'id', loginLog.id,
      'location', loginLog.location,
      'loginTime', loginLog.login_time,
      'ip', loginLog.login_ip
    )
  ) AS log,
  user.name AS userName,
  user.phone AS phone,
  user.email AS email
FROM user
LEFT JOIN (
  SELECT * 
  FROM login_log
  WHERE login_time > '2025-01-10 03:28:01.486471'
  ORDER BY login_time DESC
) AS loginLog ON user.id = loginLog.uid
WHERE user.id = '04006a9b-246c-46ac-a5a9-6b5fd150bcd1';

关键的修正点包括：

1. JSON 0BJECT 应改为 JSON_OBJECT。
2. 在 JSON_OBJECT 函数中，键和值之间需要用逗号分隔，并且需要确保所有的键名和对应的字段名正确无误。
3. user'.name, user'.phone, 和 user'.email中的引号使用有误，应该更正为user.name, user.phone, 和 user.email`。
4. 子查询别名 loginLog 后面的 0N 应该是 ON。
5. 确保 login_time 字段在比较和排序时的格式与数据库中的存储格式匹配（既然您说它是 DATETIME 类型，那么示例中的格式是正确的）。

Answer 2

请确认你的字段是否是DATETIME, 如果是才可以按照此方式排序：

SELECT * FROM table_name ORDER BY datetime_column ASC; -- 按照datetime字段升序排序
SELECT * FROM table_name ORDER BY datetime_column DESC; -- 按照datetime字段降序排序

Answer 3

你的排序应该是在where 后面而不是在 left join 里面进行排序

Answer 4

ORDER BY 在子查询中并不会影响最终的结果集的顺序，因为 JSON_ARRAYAGG 函数不会保留子查询的排序。如果希望最终的 log 数组按 login_time 排序，需要在外层查询中再次进行排序。

SELECT JSON_ARRAYAGG(
    JSON_OBJECT(
        'id',
        loginLog.id,
        'location',
        loginLog.location,
        'loginTime',
        loginLog.login_time,
        'ip',
        loginLog.login_ip
    ) ORDER BY loginLog.login_time DESC
) AS log,
    `user`.`name` AS userName,
    `user`.`phone` AS phone,
    `user`.`email` AS email
FROM `user`
LEFT JOIN (
    SELECT *
    FROM login_log
    WHERE login_time >= '2025-01-10 03:28:01.486471'
    ORDER BY login_time DESC
) AS `loginLog` ON `user`.id = `loginLog`.uid
WHERE user.id = '04006a9b-246c-46ac-a5a9-6b5fd150bcd1';

Answer 5

-- 给login_time和uid建复合索引也可以：

CREATE INDEX idx_login_log_uid_time ON login_log(uid, login_time);