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

ldfqzlk8  于 2021-06-20  发布在  Mysql
关注(0)|答案(2)|浏览(390)

我已经在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吗?

kyxcudwk

kyxcudwk1#

我找到了解决办法:-),

knex.raw('((select leave_trackers.available_leave_days from leave_trackers) - (select ((DATEDIFF(?, ?) + 1) - (WEEK(?) - WEEK(?)) - (case when weekday(?) = 6 then 1 else 0 end) - (SELECT IFNULL(sum(total), 0) from (select count(holidays.date) as total FROM holidays WHERE holidays.date between ? and ? && holidays.location_id = ?) as holiday_leave )) AS total_available_days)) AS total_available_leave_days',
                                        ['2018-06-11', '2018-06-01', '2018-06-11', '2018-06-01', '2018-06-11', '2018-06-01', '2018-06-11', '1']
                                    )
dnph8jn4

dnph8jn42#

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));

如果这不起作用,我们需要更多的信息是什么出了问题。

相关问题