我正在尝试将SQL查询转换为KnexJS格式,但当前的KnexJS查询显示以下错误。
或附近出现语法错误
以下是原始查询,也是我一直在为KnexJS处理的查询。请更正我的KnexJS查询。
提前谢谢你!
原始SQL查询:
select count(distinct date)
from task_history
where
store_id = 100 and date >
(select date from (
select date, count(*) as count_all, count(case when finish_time is not null
then 1 else null end) as count_finished
from task_history
where store_id = 100
group by date
order by count_finished, date desc
fetch first row only) as get_max_date)
KnexJS查询:
.table("task_history")
.count(db.raw("'distinct date'"))
.where('store_id', 100)
.where('date', '>', function() {
this.select('date')
.from(function() {
this.select('date')
.table("task_history")
.first()
.count('* as count_all')
.count(db.raw(`case when finish_time is not null then 1 else null end as count_finished`))
.where('store_id', 100)
.groupBy('date')
.orderBy('count_finished', 'desc')
.orderBy('date', 'desc')
.as('get_max_date')
})
})
发布于 2018-10-31 11:22:24
The following query worked for me:
.table("task_history")
.count(db.raw("distinct date"))
.where('store_id', 100)
.where('date', '>', function() {
this.select('date')
.from(function() {
this.select('date')
.table("task_history")
.first()
.count('* as count_all')
.select( db.raw("count(case when finish_time is not null then 1 else null end) as count_finished"))
.where('store_id', 100)
.groupBy('date')
.orderBy('count_finished', 'asc')
.orderBy('date', 'desc')
.as('get_max_date')
})
});
发布于 2018-10-31 07:03:17
这是一个复杂的查询。由于您尚未共享SQL结构以供其他人尝试相同的查询,因此我建议您尝试在您的查询中包含这个“调试”子句:
.on('query-error', function(ex, obj) {
console.log("KNEX query-error ex:", ex, "obj:", obj);
})
这将在查询崩溃时为您输出生成的SQL。这可能会告诉您语句的哪一部分是不正确的。
祝好运。
https://stackoverflow.com/questions/53064253
复制相似问题