时间戳

首页 > 解决方案 > MySQL获取每个用户ID的最大日期并将它们转换为sequelize

javascript - MySQL获取每个用户ID的最大日期并将它们转换为sequelize

问题描述

这是我的 MySQL 查询

SELECT a.id,
       a.name,
       a.pname,
       b.skill,
       date_add(a.end_date, interval 1 DAY) AS available_date
FROM
  (SELECT user.id,
          concat(user.first_name, " ", user.last_name) AS name,
          resource_allocated.project_id,
          project.project_name AS pname,
          resource_allocated.end_date
   FROM USER
   RIGHT JOIN resource_allocated ON user.id = resource_allocated.user_id
   RIGHT JOIN project ON resource_allocated.project_id = project.id
   WHERE resource_allocated.is_active=1) a
LEFT JOIN
  (SELECT user.id,
          concat(user.first_name, " ", user.last_name) AS name,
          user_skill.skill_id,
          skill.skill_name AS skill
   FROM USER
   RIGHT JOIN user_skill ON user.id = user_skill.user_id
   RIGHT JOIN skill ON user_skill.skill_id = skill.id) b ON a.id = b.id
ORDER BY a.id;

结果如下所示：

+----+----------+-------+-------+----------------+
| id |   name   | pname | skill | available_date |
+----+----------+-------+-------+----------------+
|  1 | john doe | hms   | php   | 2019-01-02     |
|  1 | john doe | hms   | react | 2019-01-02     |
|  2 | jane doe | hms   | java  | 2020-01-16     |
|  2 | jane doe | IS    | java  | 2019-06-21     |
|  2 | jane doe | hms   | js    | 2020-01-16     |
|  2 | jane doe | IS    | js    | 2019-06-21     |
+----+----------+-------+-------+----------------+

我希望 mysql 查询获取每个用户的最大日期，如下所示：

+----+----------+-------+-------+----------------+
| id |   name   | pname | skill | available_date |
+----+----------+-------+-------+----------------+
|  1 | john doe | hms   | php   | 2019-01-02     |
|  1 | john doe | hms   | react | 2019-01-02     |
|  2 | jane doe | hms   | java  | 2020-01-16     |
|  2 | jane doe | IS    | java  | 2020-01-16     |
|  2 | jane doe | hms   | js    | 2020-01-16     |
|  2 | jane doe | IS    | js    | 2020-01-16     |
+----+----------+-------+-------+----------------+

如果可能，有人可以为结果查询提供等效的续集代码吗？提前致谢。

标签： javascriptmysqlsqlnode.jssequelize.js

解决方案

如果您想在结果中获取最大日期（或最后日期），请使用max()函数并group by name , pname在最后使用

max(date_add(a.end_date, interval 1 day)) as available_date

如果您想按日期排列它们，请在查询末尾写下：

group by name, pname order by available_date

所以最终的查询将如下所示：

SELECT a.id,
       a.name,
       a.pname,
       b.skill,
       max(date_add(a.end_date, interval 1 day)) as available_date
FROM
  (SELECT user.id,
          concat(user.first_name, " ", user.last_name) AS name,
          resource_allocated.project_id,
          project.project_name AS pname,
          resource_allocated.end_date
   FROM USER
   RIGHT JOIN resource_allocated ON user.id = resource_allocated.user_id
   RIGHT JOIN project ON resource_allocated.project_id = project.id
   WHERE resource_allocated.is_active=1) a
LEFT JOIN
  (SELECT user.id,
          concat(user.first_name, " ", user.last_name) AS name,
          user_skill.skill_id,
          skill.skill_name AS skill
   FROM USER
   RIGHT JOIN user_skill ON user.id = user_skill.user_id
   RIGHT JOIN skill ON user_skill.skill_id = skill.id) b ON (a.id = b.id)
GROUP by name, pname , skill
ORDER by a.id, available_date;

推荐阅读