使用Sequelize ORM for Node.js,如何在select语句中使用CASE / WHEN表达式?
根据Sequelize文档,我看不到任何参考或示例来使用SQL表达式CASE。这可能吗?
这是我的示例:
的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建立特殊查询
attributes
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); });