问题描述
我有3张桌子.
support_works =包含序列号
kickscooters =序列号,kickscooter_id(作为主ID,并命名为ID)
租金=包含kickscooter_id.
support_works = contain serial_number
kickscooters = contian serial_number, kickscooter_id(as primary id, and named id)
rents = contain kickscooter_id.
我正在尝试获取满足条件的support_works的序列号,然后将其与kickscooter的序列号进行匹配.然后获取其kickscooter_id,以获取与其匹配的与kickscooter_id相匹配的所有租金.
I am trying to get serial_number of support_works that satisfy conditions then match it with kickscooter's serial_number. Then get its kickscooter_id to get all rents that match kickscooter_id retrieved from it.
当前:
select k.id
from support_works sw
join kickscooters k
on k.serial_number = sw.serial_number
where
sw.work_type = 'deploy' and
(sw.updated_at between '2019-11-01 02:00:00' and '2019-11-01 10:00:00') and
k.id in (select kcu.kickscooter_id
from kickscooter_control_units kcu
where kcu.particle_product_id in (9358, 9383)));
这非常适合从kickscooter表中获取kickscooter_id.但是我现在使用它作为子查询来获取所有租金表数据,其中该子查询中包含了rents.kickscooter_id:
this works perfectly on getting kickscooter_id from kickscooter table. But I am now using this as subquery to get all rents table data where rents.kickscooter_id is in this subquery:
select *
from rents r
where r.kickscooter_id
in (select k.id
from support_works sw
join kickscooters k
on k.serial_number = sw.serial_number
where
sw.work_type = 'deploy' and
(sw.updated_at between '2019-11-01 02:00:00' and '2019-11-01 10:00:00') and
k.id in (select kcu.kickscooter_id
from kickscooter_control_units kcu
where kcu.particle_product_id in (9358, 9383)));
这花费了太长时间,我想使用多个联接来加快处理速度.我该怎么办?
This is taking too long and I want to use multiple joins to make things faster. How can I go about it?
我一直在使用CTE,但是我已经读到它在创建/删除临时表时会占用内存,因此尝试避免使用它.
I have been using CTE however I've read that it takes up memory while creating/deleting temporary table therefore tried to avoid it.
推荐答案
您可以使用exists
关键字(而不是IN)在subquery
上加入kickscooter_control_units
.
You can join kickscooter_control_units
on your subquery
the use exists
keyword rather than IN.
select *
from rents r
where
exists
(select 1
from support_works sw
join kickscooters k on k.serial_number = sw.serial_number
join kickscooter_control_units kcu on kcu.kickscooter_id = k.id and kcu.particle_product_id in (9358, 9383)
where
sw.work_type = 'deploy' and
(sw.updated_at between '2019-11-01 02:00:00' and '2019-11-01 10:00:00'))
似乎根据您的情况过滤了ID
. exists
仅在您要检查某些subquery
是否包含值时适用.
Seems based on your scenario you are filtering the ID
. exists
only applicable if you want to check if certain subquery
contains values.
select *
from rents r
where
r.kickscooter_id in
(select k.id
from support_works sw
join kickscooters k on k.serial_number = sw.serial_number
join kickscooter_control_units kcu on kcu.kickscooter_id = k.id and kcu.particle_product_id in (9358, 9383)
where
sw.work_type = 'deploy' and
(sw.updated_at between '2019-11-01 02:00:00' and '2019-11-01 10:00:00'))
这篇关于使用查询别名与另一个表MySQL连接的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!