Sequelize似乎不支持从子查询中进行选择(https://github.com/sequelize/sequelize/issues/5354)
需要在Sequelize Node js 中编写以下SQL查询
select a, b, c
from (
select tbl.*,
count(*) over(partition by a) cnt,
row_number() over (partition by a order by b) brn,
row_number() over (partition by a order by c) crn
from tbl
where c in (4, 5)
) t
where cnt = 2 and brn = crn;
这就是我想到的。我不知道把条件where cnt = 2 and brn = crn;放在哪里
t.findAll({
attributes: {
include: [
[sequelize.literal('count(*) over(partition by a)'),'cnt'],
[sequelize.literal('row_number() over (partition by a order by b)'),'brn'],
[sequelize.literal('row_number() over (partition by a order by c)'),'crn'],
]
},
where: {
c: {[Op.in]:[4,5]},
}
})
您可以使用原始查询(https://sequelize.org/master/manual/raw-queries.html)：
const { QueryTypes } = require('sequelize');
let result = await sequelize.query(`
select a, b, c
from (
select tbl.*,
count(*) over(partition by a) cnt,
row_number() over (partition by a order by b) brn,
row_number() over (partition by a order by c) crn
from tbl
where c in (4, 5)
) t
where cnt = 2 and brn = crn;
`, {type: QueryTypes.SELECT});