时间戳

首页 > 解决方案 > SQL 查询耗时太长，甚至没有响应

sql - SQL 查询耗时太长，甚至没有响应

问题描述

SQL> desc airport
 Name                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 AIRPORT_ID                NOT NULL NUMBER(5)
 CITY_ID                   NOT NULL NUMBER(5)
 AIRPORT                        VARCHAR2(59)
 IATA_CODE                      VARCHAR2(3)

SQL> desc city
 Name                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 CITY_ID                   NOT NULL NUMBER(5)
 COUNTRY_ID                NOT NULL NUMBER(5)
 CITY                           VARCHAR2(36)

SQL> desc country
 Name                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 COUNTRY_ID                NOT NULL NUMBER(5)
 COUNTRY                        VARCHAR2(36)

这是查询：

select airport_id, airport, iata_code, city, country 
from airport a, city b, country c 
where a.city_id = b.city_id 
  and b.country_id = c.country_id 
  and b.city like '%tehran%' or iata_code like '%tehran%'
order by airport asc;

当我使用一个参数运行相同的查询时，此查询没有响应或几分钟响应，例如and b.city like '%tehran%'通过删除or iata_code like '%tehran%'然后它运行良好并且结果不到一秒

为什么它没有响应两个参数，例如and b.city like '%tehran%' or iata_code like '%tehran%'

标签： sqloracle

解决方案

看起来你想要

select airport_id, airport, iata_code, city, country 
from airport a
join city b on a.city_id=b.city_id and (b.city like '%tehran%' or b.iata_code like '%tehran%')
join country c on b.country_id=c.country_id 
order by airport asc;

注意 SQL 标准 JOIN 语法和使用括号固定的逻辑 AND/OR 操作的顺序。

推荐阅读