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

Question

这是我的 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     |
+----+----------+-------+-------+----------------+

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

Answer 1

如果您想在结果中获得最大日期(或最后一个日期)，请使用 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;