如何在 Sequelize 中使用 CASE WHEN 表达式? [英] How to use CASE WHEN expression in Sequelize?

问题描述

使用 Sequelize ORM for Node.js，如何在 select 语句中使用 CASE/WHEN 表达式?

Using Sequelize ORM for Node.js, how do you use CASE/WHEN expression in a select statement?

我没有看到任何使用 SQL 表达式的参考或示例 CASE 根据 Sequelize 文档.这可能吗?

I do not see any reference or examples to use the SQL expression CASE according to Sequelize Documentation . Is this possible?

这是我的例子:

SQL

SELECT userId, Status, 
  MAX(CASE Type WHEN 'Employee' THEN Rate ELSE 0 END) AS "Employee",
  MAX(CASE Type WHEN 'School' THEN Rate ELSE 0 END) AS "School",
  MAX(CASE Type WHEN 'Public' THEN Rate ELSE 0 END) AS "Public",
  MAX(CASE Type WHEN 'Other' THEN Rate ELSE 0 END) AS "Other"
FROM Database.dbo.invoice
WHERE Status IN ('Included', 'Excluded')
GROUP BY userId,  Status

续集

var query = {
  attributes: ['userId'], // need to include Rate (as Employee, School, Public, Other for each CASE/WHEN)
  /*
    MAX(CASE Type WHEN 'Employee' THEN Rate ELSE 0 END) AS "Employee",
    MAX(CASE Type WHEN 'School' THEN Rate ELSE 0 END) AS "School",
    MAX(CASE Type WHEN 'Public' THEN Rate ELSE 0 END) AS "Public",
    MAX(CASE Type WHEN 'Other' THEN Rate ELSE 0 END) AS "Other"
*/
  where: {
    user: userId,
    Type: ['Employee', 'School', 'Public', 'Other'],
  },
  group: ['userId'],
  raw: true
};
models.invoice.findAll(query).then(result => {
  console.log(result);
});

数据库结构

+--------+------+----------+
| userId | Rate | Type     |
+--------+------+----------+
| 1      | 2.00 | Employee |
+--------+------+----------+
| 1      | 3.50 | School   |
+--------+------+----------+
| 1      | 4.00 | Public   |
+--------+------+----------+
| 1      | 2.50 | Other    |
+--------+------+----------+
| 2      | 3.75 | Employee |
+--------+------+----------+
| 2      | 4.25 | School   |
+--------+------+----------+
| 2      | 2.00 | Public   |
+--------+------+----------+
| 2      | 3.00 | Other    |
+--------+------+----------+

预期结果:

+--------+----------+--------+--------+-------+
| userId | Employee | School | Public | Other |
+--------+----------+--------+--------+-------+
| 1      | 2.00     | 3.50   | 4.00   | 2.50  |
+--------+----------+--------+--------+-------+
| 2      | 3.75     | 4.25   | 2.00   | 3.00  |
+--------+----------+--------+--------+-------+

推荐答案

您可以使用 Sequelize.literal 在 attributes 中构建特殊查询

You can use Sequelize.literal in attributes to build special queries

var query = {
  attributes: [
    'userId',
    [Sequelize.literal(`MAX(CASE Type WHEN 'Employee' THEN Rate ELSE 0 END)`), 'Employee'],
    [Sequelize.literal(`MAX(CASE Type WHEN 'School' THEN Rate ELSE 0 END)`), 'School'],
    [Sequelize.literal(`MAX(CASE Type WHEN 'Public' THEN Rate ELSE 0 END)`), 'Public'],
    [Sequelize.literal(`MAX(CASE Type WHEN 'Other' THEN Rate ELSE 0 END)`), 'Other'],
  ],
  where: {
    user: userId,
    Type: ['Employee', 'School', 'Public', 'Other'],
  },
  group: ['userId'],
  raw: true
};
models.invoice.findAll(query).then(result => {
  console.log(result);
});

这篇关于如何在 Sequelize 中使用 CASE WHEN 表达式?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！