I'm writing what should be a fairly straightforward SQL query for a modification of the IMDB database; it's supposed to return a list of all films that are categorized as BOTH horror and comedy, which I've done by creating a list of horror, a list of comedy, and then deleting everything from one that's not in the other. The query is as follows:
我正在编写一个相当简单的SQL查询来修改IMDB数据库;它应该返回一个所有被归类为恐怖和喜剧的电影的列表，我做的是创建一个恐怖的列表，一个喜剧的列表，然后从一个不在另一个的列表中删除所有的东西。查询内容如下:
WITH
table_left AS (SELECT primary_names.name AS name, year, genre, title_id
FROM titles NATURAL JOIN primary_names NATURAL JOIN title_genres WHERE genre = 'Horror'),
table_right AS (SELECT primary_names.name AS name, year, genre, title_id
FROM titles NATURAL JOIN primary_names NATURAL JOIN title_genres WHERE genre = 'Comedy')
DELETE FROM table_right WHERE (title_id NOT IN (SELECT table_left.title_id))
SELECT name, year FROM table_right;
However, this generates an "ERROR: syntax error at or near 'SELECT'" on the last line of the query. I'm fairly new to SQL, but have gone over the syntax multiple times and checked some guides and I just can't understand what's going wrong. There shouldn't be a comma after the DELETE FROM statement, I don't think I've got a comma in any inappropriate places...it may be staring me in the face, but I'm at a loss, and would love to hear any suggestions.
但是，这会在查询的最后一行“SELECT”或“SELECT”附近生成“ERROR:语法错误”。我对SQL很陌生，但是已经多次检查语法，检查了一些向导，我只是不知道哪里出了问题。从语句删除后不应该有逗号，我不认为我在任何不合适的地方有逗号……它可能正盯着我的脸，但我不知所措，我很想听听任何建议。
1 个解决方案
#1
2
When you use the WITH syntax, you can declare multiple table expressions, but then you can follow it with just one SQL query.
当您使用WITH语法时，您可以声明多个表表达式，但是您可以使用一个SQL查询来跟踪它。
But you have two — a DELETE followed by a SELECT, with no statement terminator between the two statements. This doesn't match any syntax rule of SQL.
但是您有两个——一个DELETE后面跟着一个SELECT，两个语句之间没有语句结束符。这与SQL的任何语法规则都不匹配。
I could comment that another way to achieve what you want, listing films that are in two categories, is to do a self-join.
我可以评论另一种实现你想要的东西的方式，列出属于两类的电影，就是做一个自连接。
SELECT p.name, t.year, t.title_id
FROM titles AS t
INNER JOIN title_genres AS g1 ON t.title_id = g1.title_id AND g1.genre = 'Horror'
INNER JOIN title_genres AS g2 ON t.title_id = g2.title_id AND g2.genre = 'Comedy'
INNER JOIN primary_names AS p ON t.title_id = p.title_id
You really need to learn how to use JOIN if you're coding with SQL. Not doing so is like using another language like Java or Ruby without understanding loops. I don't mean joins are like loops, just that joins are a foundational part of the SQL language, and you need to know how to use them.
如果您正在使用SQL编码，那么您确实需要学习如何使用JOIN。不这样做就像不理解循环而使用另一种语言，比如Java或Ruby。我并不是说连接就像循环，只是连接是SQL语言的基础部分，您需要知道如何使用它们。
#1
2
When you use the WITH syntax, you can declare multiple table expressions, but then you can follow it with just one SQL query.
当您使用WITH语法时，您可以声明多个表表达式，但是您可以使用一个SQL查询来跟踪它。
But you have two — a DELETE followed by a SELECT, with no statement terminator between the two statements. This doesn't match any syntax rule of SQL.
但是您有两个——一个DELETE后面跟着一个SELECT，两个语句之间没有语句结束符。这与SQL的任何语法规则都不匹配。
I could comment that another way to achieve what you want, listing films that are in two categories, is to do a self-join.
我可以评论另一种实现你想要的东西的方式，列出属于两类的电影，就是做一个自连接。
SELECT p.name, t.year, t.title_id
FROM titles AS t
INNER JOIN title_genres AS g1 ON t.title_id = g1.title_id AND g1.genre = 'Horror'
INNER JOIN title_genres AS g2 ON t.title_id = g2.title_id AND g2.genre = 'Comedy'
INNER JOIN primary_names AS p ON t.title_id = p.title_id
You really need to learn how to use JOIN if you're coding with SQL. Not doing so is like using another language like Java or Ruby without understanding loops. I don't mean joins are like loops, just that joins are a foundational part of the SQL language, and you need to know how to use them.
如果您正在使用SQL编码，那么您确实需要学习如何使用JOIN。不这样做就像不理解循环而使用另一种语言，比如Java或Ruby。我并不是说连接就像循环，只是连接是SQL语言的基础部分，您需要知道如何使用它们。