mysql别名是另一个表的数据_使用查询别名与另一个表MySQL连接

I have 3 tables.

support_works = contain serial_number

kickscooters = contian serial_number, kickscooter_id(as primary id, and named id)

rents = contain 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.

Currently:

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 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?

I have been using CTE however I've read that it takes up memory while creating/deleting temporary table therefore tried to avoid it.

解决方案

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

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