我不能使用intersect操作符

kiayqfof  于 2021-08-09  发布在  Java
关注(0)|答案(2)|浏览(381)

这个问题在这里已经有答案了

sql:intersect语法错误(6个答案)
10个月前关门了。
我正在尝试解决这个问题:
'从电台查询以元音(即a、e、i、o和u)作为首字母和末字母的城市名称列表。您的结果不能包含重复项。'
我试着用intersect操作符来解析它,如下所示:

SELECT CITY 
FROM STATION 
WHERE LEFT(CITY, 1) = 'A' OR LEFT(CITY, 1) = 'O'
OR LEFT(CITY, 1) = 'E' OR LEFT(CITY, 1) = 'I' OR
LEFT (CITY, 1) OR LEFT(CITY, 1) = 'U'

INTERSECT

SELECT CITY
FROM STATION
WHERE RIGHT(CITY, 1) = 'A' OR RIGHT(CITY, 1) = 'O'
OR RIGHT(CITY, 1) = 'E' OR RIGHT(CITY, 1) = 'I' OR
RIGHT (CITY, 1) OR RIGHT(CITY, 1) = 'U';

但我收到以下错误消息:

ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTERSECT
SELECT CITY
FROM STATION
WHERE RIGHT(CITY, 1) = 'A' OR RIGHT(CITY, 1)' at line

我做错了什么?还有什么方法可以解决这个问题?

ruoxqz4g

ruoxqz4g1#

查询列表 CITY 名字来自 STATION 它们的第一个和最后一个字符都是元音。
你好像把事情搞得太复杂了。据我所知,你只需要 where 一次过滤表的子句-正则表达式在这方面很方便:

select city
from station
where city regexp '^[aeiou].*[aeiou]$'

模式描述以wovel开头和结尾的字符串( ^ 表示字符串的开头,并且 $ 是结束)。
如果你有副本 city 在表中,然后使用 select distinct city from ... 相反。
如果你想用 left() 以及 right() :

select city
from station
where 
    right(city, 1) in ('a', 'e', 'i', 'o', 'u')
    and left(city, 1) in ('a', 'e', 'i', 'o', 'u')
kxeu7u2r

kxeu7u2r2#

你可以用 join :

SELECT CITY1 AS CITY
FROM
(
    SELECT CITY AS CITY1
    FROM STATION 
    WHERE LEFT(CITY, 1) = 'A' OR LEFT(CITY, 1) = 'O'
    OR LEFT(CITY, 1) = 'E' OR LEFT(CITY, 1) = 'I' OR
    LEFT (CITY, 1) OR LEFT(CITY, 1) = 'U'
) AS T1 JOIN (
    SELECT CITY AS CITY2
    FROM STATION
    WHERE RIGHT(CITY, 1) = 'A' OR RIGHT(CITY, 1) = 'O'
    OR RIGHT(CITY, 1) = 'E' OR RIGHT(CITY, 1) = 'I' OR
    RIGHT (CITY, 1) OR RIGHT(CITY, 1) = 'U'
) AS T2 ON CITY1 = CITY2

相关问题