Sequelize中CASE条件使用moment日期格式变量统计周度任务数出错如何解决

问题根源 #

你的代码错误核心是混淆了Node.js端与SQL端的执行逻辑：
moment(Tasks.sequelize.col("creation_date_time")).format("dddd")这部分代码是在Node.js服务端提前执行的，moment接收到的不是数据库内的creation_date_time字段实际值，而是Sequelize的Col实例对象，转成字符串后是固定值。你把这个固定值拼接进sequelize.literal的SQL语句中，导致所有CASE的判断条件都是同一个静态值，自然只会有第一个匹配的CASE生效。

解决方法 #

统计星期几的逻辑要直接用数据库原生的日期函数实现，不要用Node.js端的moment处理，以下是不同数据库的写法：

MySQL 写法 #

module.exports.Get_Perday_Creation_Reports = async (user_info) => {
  const daily_reports = await Tasks.findAll({
    attributes: [
      [
        sequelize.literal(`sum(CASE WHEN DAYNAME(creation_date_time) = 'Monday' THEN 1 ELSE 0 END)`),
        "Monday",
      ],
      [
        sequelize.literal(`sum(CASE WHEN DAYNAME(creation_date_time) = 'Tuesday' THEN 1 ELSE 0 END)`),
        "Tuesday",
      ],
      [
        sequelize.literal(`sum(CASE WHEN DAYNAME(creation_date_time) = 'Wednesday' THEN 1 ELSE 0 END)`),
        "Wednesday",
      ],
      [
        sequelize.literal(`sum(CASE WHEN DAYNAME(creation_date_time) = 'Thursday' THEN 1 ELSE 0 END)`),
        "Thursday",
      ],
      [
        sequelize.literal(`sum(CASE WHEN DAYNAME(creation_date_time) = 'Friday' THEN 1 ELSE 0 END)`),
        "Friday",
      ],
    ],
    raw: true,
  });
  console.log(daily_reports);
};

PostgreSQL 写法 #

将DAYNAME(creation_date_time)替换为trim(to_char(creation_date_time, 'Day'))即可。

SQL Server 写法 #

将DAYNAME(creation_date_time)替换为DATENAME(WEEKDAY, creation_date_time)即可。

兼容多数据库的优化写法 #

如果需要适配不同类型的数据库，可以用Sequelize的fn方法封装函数调用，避免写死数据库专属语法：

// 以统计周一为例
[
  sequelize.literal(`sum(CASE WHEN ? = 'Monday' THEN 1 ELSE 0 END)`.replace('?', sequelize.fn('DAYNAME', sequelize.col('creation_date_time')))),
  'Monday'
]

内容的提问来源于stack exchange，提问作者hassan akbar