使用从另一个表 MYSQL 中获取的值从一个表中选择行

Question

提问by Stanley Ngumo

I have currently have 2 mysql tables in my db

我的数据库中目前有 2 个 mysql 表

Filmand Film_Ratings_Report

Film和 Film_Ratings_Report

The primary key for Film is filmidwhich is used to identify the film ratings in the Film_Ratings_Reporttable.

Film 的主键用于filmid标识Film_Ratings_Report表中的电影评级。

I would like to know if its possible using a MYSQL query only to search the ratings table and collect all film ids which fit a certain criteria then use the selected IDs to get the film titles from the Film table. Below is the MYSQL query Im using which isnt working:

我想知道是否可以仅使用 MYSQL 查询来搜索评级表并收集符合特定条件的所有电影 ID，然后使用选定的 ID 从电影表中获取电影片名。下面是我使用的 MYSQL 查询，它不起作用：

SELECT * 
FROM film 
UNION SELECT filmid 
      FROM film_rating_report 
      WHERE rating = 'GE' 
      LIMIT 0,0

I am relatively green to MYSQL and would appreciate any help on this.

我对 MYSQL 比较陌生，希望得到任何帮助。

Thanks in Advance

提前致谢

Answer 1

采纳答案by SG 86

SELECT * FROM film WHERE id IN 
  (SELECT filmid FROM film_rating_report WHERE rating = 'GE');

should work

应该管用

Answer 2

回答by ypercube??

It seems you want a semi-join, e.g. a join where only data from one of the 2 joined tables are needed. In this case, all rows from filmfor which there is a matching row in film_rating_reportthat has the wanted condition (rating = 'GE').

似乎您想要一个半连接，例如一个连接，其中只需要来自 2 个连接表之一的数据。在这种情况下，film其中有匹配行的所有行都film_rating_report具有所需条件 ( rating = 'GE')。

This is not exactly equivalent to a usual join because even if there are 2 (or more) row in the second table (2 ratings of a film, both with 'GE'), we still want the film to be shown once, not twice (or more times) as it would be shown with a usual join.

这并不完全等同于通常的连接，因为即使第二个表中有 2（或更多）行（一部电影的 2 个评分，都带有'GE'），我们仍然希望电影放映一次，而不是两次（或更多）次），因为它会用通常的连接显示。

There are various ways to write a semi-join and most popular are:

有多种编写半连接的方法，最流行的是：

using an EXISTScorrelated subquery (@Justin's answer):

SELECT t1.* 
FROM film t1 
WHERE EXISTS (SELECT filmid 
              FROM film_rating_report t2
              WHERE t2.rating = 'GE'
              AND t2.filmid = t1.id);

using an IN(uncorrelated) subquery (@SG 86's answer):
(this should be used with extreme care as it may return unexpected results - or none at all - if the joining columns (the two filmidin this case) are Nullable)
```
SELECT * 
FROM film 
WHERE id IN 
  ( SELECT filmid 
    FROM film_rating_report 
    WHERE rating = 'GE'
  );
```
using a usual JOINwith a GROUP BYto avoid the duplicate rows in the results (@Tomas' answer):
(and note that this specific use of GROUP BYworks in MySQL only and in recent versions of Postgres, if you ever want to write a similar query in other DBMS, you'll have to include all columns: GROUP BY f.filmid, f.title, f.director, ...)
```
SELECT f.*
FROM film AS f
    JOIN film_rating_report AS frr
         ON f.filmid = frr.filmid
WHERE frr.rating = 'GE' 
GROUP BY f.filmid ;
```

A variation on @Tomas'es answer, where the GROUP BYis done on a derived table and then the JOIN:

SELECT f.*
FROM film AS f
    JOIN 
        ( SELECT filmid
          FROM film_rating_report
          WHERE rating = 'GE'
          GROUP BY filmid
        ) AS frr
      ON f.filmid = frr.filmid ;

使用EXISTS相关子查询（@Justin 的回答）：

SELECT t1.* 
FROM film t1 
WHERE EXISTS (SELECT filmid 
              FROM film_rating_report t2
              WHERE t2.rating = 'GE'
              AND t2.filmid = t1.id);

使用IN（不相关的）子查询（@SG 86 的回答）：（
应该非常小心地使用它，因为它可能会返回意外的结果 - 或者根本没有 - 如果连接列（filmid在这种情况下是两个）是 Nullable）
```
SELECT * 
FROM film 
WHERE id IN 
  ( SELECT filmid 
    FROM film_rating_report 
    WHERE rating = 'GE'
  );
```
使用通常JOIN的 aGROUP BY来避免结果中的重复行（@Tomas 的回答）：（
请注意GROUP BY，如果您想在其他版本中编写类似的查询，则此特定用法仅适用于 MySQL 和最新版本的 Postgres DBMS，你必须包括所有列：GROUP BY f.filmid, f.title, f.director, ...）
```
SELECT f.*
FROM film AS f
    JOIN film_rating_report AS frr
         ON f.filmid = frr.filmid
WHERE frr.rating = 'GE' 
GROUP BY f.filmid ;
```

@Tomas'es 答案的变体，在GROUP BY派生表上完成，然后JOIN：

SELECT f.*
FROM film AS f
    JOIN 
        ( SELECT filmid
          FROM film_rating_report
          WHERE rating = 'GE'
          GROUP BY filmid
        ) AS frr
      ON f.filmid = frr.filmid ;

Which one to use, depends on the RDBMS and the specific version you are using (for example, INsubqueries should be avoided in most versions of MySQL as they may produce inefficient execution plans), your specific table sizes, distribution, indexes, etc.

使用哪个取决于 RDBMS 和您使用的特定版本（例如，IN在大多数 MySQL 版本中应避免使用子查询，因为它们可能会产生低效的执行计划）、您的特定表大小、分布、索引等。

I usually prefer the EXISTSsolution but it never hurts to first test the various queries with the table sizes you have or expect to have in the future and try to find the best query-indexes combination for your case.

我通常更喜欢该EXISTS解决方案，但首先使用您拥有或期望将来拥有的表大小测试各种查询并尝试为您的情况找到最佳查询索引组合永远不会有什么坏处。

Addition: if there is a unique constraint on the film_rating_report (filmid, rating)combination, which means that no film will ever get two same ratings, or if there is an even stricter (but more plausible) unique constraint on film_rating_report (filmid)that means that every film has at most one rating, you can simplify the JOINsolutions to (and get rid of all the other queries):

另外：如果对film_rating_report (filmid, rating)组合有唯一限制，这意味着没有一部电影会获得两个相同的评分，或者如果有更严格（但更合理）的唯一限制，film_rating_report (filmid)这意味着每部电影最多只有一个评分，您可以简化JOIN解决方案（并摆脱所有其他查询）：

    SELECT f.*
    FROM film AS f
        JOIN film_rating_report AS frr
             ON f.filmid = frr.filmid
    WHERE frr.rating = 'GE' ;

Answer 3

回答by TMS

Preferred solution for this is to use join, and don't forget group byso that you don't have duplicate lines:

对此的首选解决方案是使用join，并且不要忘记group by这样您就没有重复的行：

select film.*
from film
join film_rating_report on film.filmid = film_rating_report.filmid
        and rating = 'GE'
group by film.filmid

EDIT: as correctly noted by @ypercube, I was wrong claiming that the performance of join & group by is better than using subqueries with existsor in- quite the opposite.

编辑：正如@ypercube 正确指出的那样，我错误地声称 join & group by 的性能比使用带有existsor 的子查询更好in- 恰恰相反。

Answer 4

回答by Justin

Query:

询问：

SELECT t1.* 
FROM film t1 
WHERE EXISTS (SELECT filmid 
              FROM film_rating_report t2
              WHERE t2.rating = 'GE'
              AND t2.filmid = t1.id);

Answer 5

回答by Noam Rathaus

I believe this will work, thought without knowing your DB structure (consider giving SHOW CREATE TABLE on your tables), I have no way to know for sure:

我相信这会起作用，在不知道您的数据库结构的情况下思考（考虑在您的表上提供 SHOW CREATE TABLE），我无法确定：

SELECT film.*
FROM (film)
LEFT JOIN film_rating_report ON film.filmid = film_rating_report.filmid AND film_rating_report.rating = 'GE'
WHERE film_rating_report.filmid IS NOT NULL
GROUP BY film.filmid

(The WHERE film_rating_report.filmid IS NOT NULLprevents lines that don't have the rating you are seeking from sneaking in, I added GROUP BYat the end because film_rating_reportmight match more than once - not sure as I have visibility to the data stored in it)

（WHERE film_rating_report.filmid IS NOT NULL防止没有您正在寻找的评级的行偷偷溜进来，我在最后添加了GROUP BY因为film_rating_report可能匹配不止一次 - 不确定，因为我可以看到存储的数据在里面）

使用从另一个表 MYSQL 中获取的值从一个表中选择行

提问by Stanley Ngumo

采纳答案by SG 86

回答by ypercube??

回答by TMS

回答by Justin

回答by Noam Rathaus

相关推荐

最近更新

标签

使用从另一个表 MYSQL 中获取的值从一个表中选择行

提问by Stanley Ngumo

采纳答案by SG 86

回答by ypercube??

回答by TMS

回答by Justin

回答by Noam Rathaus

相关推荐

将 LIKE %..% 与 MySQL 中的字段值一起使用

从SQL中的时间戳，从今天，昨天，本周，本月和两个日期之间选择记录php mysql

MySQL 来自变量的mysql字段名称

MySQL IF 条件执行查询，否则执行其他查询

相关推荐

最近更新

标签