sql - 将 SQL 查询(与子查询连接)转换为 KnexJS
问题描述
我正在尝试将 SQL 查询转换为 KnexJS 格式,但当前的 KnexJS 查询给了我以下错误。
- “as”堆栈处或附近的语法错误:错误:“as”处或附近的语法错误
这是原始查询和我一直在为 KnexJS 工作的查询。请更正我的 KnexJS 查询。基本上,我想知道如何构建 KnexJS 查询 - 内部连接子查询提前谢谢!
原始 SQL 查询:
select DATE_RANGE.START_DATE, DATE_RANGE.END_DATE, count (distinct DATE) as DATE_COUNT
from TASK_HISTORY
join
(select
STORE_ID,
to_number(to_char(to_date(to_char(DATE,'99999999'),'YYYYMMDD') - 1,'YYYYMMDD'),'99999999') as END_DATE
, count (distinct DATE) as REC_COUNT
, to_number(to_char(to_date(to_char(lag (DATE) over (order by DATE asc),'99999999'),'YYYYMMDD') + 1,'YYYYMMDD'),'99999999') as START_DATE
, count (case when FINISH_TIME is not null then 1 end) as COUNT_FINISHED
, count (case when FINISH_TIME is null then 1 end) as COUNT_UNFINISHED
from TASK_HISTORY
where STORE_ID = 43
group by DATE, STORE_ID
having count (case when FINISH_TIME is not null then 1 end) = 0
order by DATE)
as DATE_RANGE
on TASK_HISTORY.DATE >= DATE_RANGE.START_DATE
AND TASK_HISTORY.DATE <= DATE_RANGE.END_DATE
AND TASK_HISTORY.STORE_ID = 43
group by DATE_RANGE.START_DATE, DATE_RANGE.END_DATE, DATE_RANGE.REC_COUNT
order by DATE_COUNT desc, START_DATE desc
更新:
这是对我有用的解决方案:
await db
.table("task_history")
.select('date_range.start_date', 'date_range.end_date')
.select(db.raw(`count(distinct date) as date_count`))
.join(
db
.select('task_history.store_id')
.table('task_history')
.select(db.raw(
`to_number(to_char(to_date(to_char(date,'99999999'),'YYYYMMDD') - 1,'YYYYMMDD'),'99999999') as end_date`
))
.select(db.raw(`count(distinct date) as rec_count`))
.select(db.raw(
`to_number(to_char(to_date(to_char(lag (date) over (order by date asc),'99999999'),'YYYYMMDD') + 1,'YYYYMMDD'),'99999999') as start_date`
))
.select(db.raw(`count(case when FINISH_TIME is not null then 1 end) as COUNT_FINISHED`))
.select(db.raw(`count(case when FINISH_TIME is null then 1 end) as COUNT_UNFINISHED`))
.where('task_history.store_id', 43)
.groupBy('task_history.date', 'task_history.store_id')
.having(db.raw(`count(case when FINISH_TIME is not null then 1 end) = 0 order by date`))
.as('date_range'),
function () {
this.on('task_history.date', '>=', 'date_range.start_date')
.andOn('task_history.date', '<=', 'date_range.end_date')
.andOn('task_history.store_id', 43)
}
)
.groupBy('date_range.start_date', 'date_range.end_date', 'date_range.rec_count')
.orderBy('date_count', 'desc')
.orderBy('start_date', 'desc')
解决方案
这可能会帮助你-
const sql = db.table("task_history")
.select('DATE_RANGE.START_DATE', 'DATE_RANGE.END_DATE')
.select(db.raw(`count(distinct DATE) as DATE_COUNT`))
.innerJoin(
db.select('store_id')
.table('task_history')
.select(db.raw(
`to_number(to_char(to_date(to_char(DATE,'99999999'),'YYYYMMDD') - 1,'YYYYMMDD'),'99999999') as END_DATE`
))
.select(db.raw(`count(distinct DATE) as REC_COUNT`))
.select(db.raw(
`to_number(to_char(to_date(to_char(lag (DATE) over (order by DATE asc),'99999999'),'YYYYMMDD') + 1,'YYYYMMDD'),'99999999') as START_DATE`
))
.select(db.raw(`count(case when FINISH_TIME is not null then 1 end) as COUNT_FINISHED`))
.select(db.raw(`count(case when FINISH_TIME is null then 1 end) as COUNT_UNFINISHED`))
.where('store_id', 43)
.groupBy('date', 'store_id')
.having(db.raw(`count(case when FINISH_TIME is not null then 1 end) = 0 order by DATE`))
.as('DATE_RANGE')
, function () {
this.on('DATE_RANGE.START_DATE', '>=', 'TASK_HISTORY.DATE')
.andOn('TASK_HISTORY.DATE', '<=', 'DATE_RANGE.END_DATE')
.andOn('TASK_HISTORY.STORE_ID', 43)
})
.where('task_history.date', '>=', 'DATE_RANGE.START_DATE')
.where('task_history.date', '<=', 'DATE_RANGE.END_DATE')
.groupBy('DATE_RANGE.START_DATE', 'DATE_RANGE.END_DATE', 'DATE_RANGE.REC_COUNT')
.orderBy('DATE_COUNT', 'desc')
.orderBy('START_DATE', 'desc')
.toSQL();
console.log(sql);
推荐阅读
- jquery - 使用jquery从图像元素列表中获取特定图像元素的数据id属性值?
- c++ - 我在我创建的代码中发现了这个错误,该错误是为了从 2 个整数中获取最大值,并且该变量正在为自己分配一个值
- sql - Postgresql:将行分组并添加数组
- logstash-grok - 将 GROK 应用到日志文件
- python - 从数据框中提取字典值并创建一个新的 Pandas 列
- c# - Websocket客户端未连接到Websocket服务器
- excel - 查找 col A 和 B 匹配且 col C 之和 = 0 的行
- angular - Chart.js v3 - 版本 3 支持注释吗?
- python - Selenium 有文字时找不到文字
- linux - 将文件作为函数的输入 - Posix shell 脚本