如何在将 groupby 与 Sequelize 和 SQLite 一起使用时获取等于某个值的行数?

Question

我有一个有email, name, order_number, review的表。所有这些字段都是text。我按email对结果进行分组，并计算有多少人有order_number。

review字段的值为'Live'、'Failed'或null。我想计算每个电子邮件地址中每个值出现的次数，并在新列中返回计数(review_live_count、review_failed_count和review_null_count)。

这是我当前的代码：

let users = await db.reservations.findAll({
attributes: ['email', 'name',[db.sequelize.fn('count', db.sequelize.col('order_number')), 'orders']], group: ["email"],
raw:true
});

我正在使用Node，Sequelize和SQLite。Sequelize和sequelize都通过db对象公开。

Answer 1

你能尝试使用续集文字吗

let users = await db.reservations.findAll({
attributes: [
'email', 
'name',
[db.sequelize.fn('count', db.sequelize.col('order_number')), 'orders'],
[db.sequelize.literal(`COUNT (distinct "id") FILTER ( WHERE "review" = 'Live' )` ), 'review_live_count'],
[db.sequelize.literal(`COUNT (distinct "id") FILTER ( WHERE "review" = 'Failed' )` ), 'review_failed_count'],
[db.sequelize.literal(`COUNT (distinct "id") FILTER ( WHERE "review" is null )` ), 'review_null_count'],
],
group: ["email"],
raw:true
});

希望对你有帮助