我不能使用intersect操作符

kiayqfof 于 2021-08-09 发布在 Java

关注(0)|答案(2)|浏览(320)

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

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

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

sql mysql where-clause select intersect

来源：https://stackoverflow.com/questions/62492574/i-cannot-use-the-intersect-operator

2条答案

按热度按时间

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

赞(0）回复(0）举报 2021-08-09

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

赞(0）回复(0）举报 2021-08-09

我来回答

我不能使用intersect操作符

2条答案

相关问题

热门标签

最新问答