我有3个表。
support_works =含有serial_number
kickscooters = contian serial_number,kickscooter_id(如主ID,并命名为ID)
租金=含有kickscooter_id。
我试图让support_works的serial_number满足条件,然后用kickscooter的serial_number匹配。然后得到其kickscooter_id获得那场比赛kickscooter_id从中检索到的所有租金。
目前:
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在这个子查询的所有租金表中的数据:
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)));
过长这是考虑,我想使用多个连接,使事情更快。我怎么能走呢?
我一直在使用CTE但是我读过,它占用的内存,而因此creating/deleting临时表尽量避免它。
分析解答
您可以在subquery
加入kickscooter_control_units
使用exists
关键字,而不是。
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
包含的值。
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'))