时间戳

首页 > 解决方案 > 如何在 node.js 中减去 knex.raw 中的查询

mysql - 如何在 node.js 中减去 knex.raw 中的查询

问题描述

我已经在 mysql 中完成了这个查询，但我不知道如何在 knex.raw 中执行此操作。

    select ((select leave_trackers.available_leaves from leave_trackers) -
(SELECT ((DATEDIFF('2018-06-11', '2018-06-01') + 1) - 
(WEEK('2018-06-11') - WEEK('2018-06-01')) -
(case when weekday('2018-06-11') = 6 then 1 else 0 end) -
(SELECT IFNULL(sum(total), 0)
from
(
select count(holidays.date) as total
FROM holidays, leave_applications 
WHERE holidays.date between '2018-06-01' and '2018-06-01'
GROUP BY holiday_id) as holiday_leave 
)
) as available_leaves
FROM leaves
group by leave_id
));

有人可以帮我把它转换成 knex.raw 吗？

标签： mysqlnode.jsknex.jsbookshelf.js

解决方案

knex.raw(`
select ((select leave_trackers.available_leaves from leave_trackers) -
(SELECT ((DATEDIFF('2018-06-11', '2018-06-01') + 1) - 
(WEEK('2018-06-11') - WEEK('2018-06-01')) -
(case when weekday('2018-06-11') = 6 then 1 else 0 end) -
(SELECT IFNULL(sum(total), 0)
from
(
select count(holidays.date) as total
FROM holidays, leave_applications 
WHERE holidays.date between '2018-06-01' and '2018-06-01'
GROUP BY holiday_id) as holiday_leave 
)
) as available_leaves
FROM leaves
group by leave_id
))
`).then(res => console.log(res));

If that doesn't work we need more info about what is going wrong.

推荐阅读