MySQL SQL：如何将视图与表连接？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2600597/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
SQL: How to join a view with a table?
提问by Craig Hooghiem
UPDATED:
更新：
I am using MySQL statement to create a view:
我正在使用 MySQL 语句来创建视图：
I need to show Editors First Name, Last Name and the City if they shipped more than 50 books. The three tables I have are:
如果他们运送了 50 多本书，我需要向编辑显示名字、姓氏和城市。我拥有的三个表是：
create table editors (
ed_id char(11),
ed_lname varchar(20),
ed_fname varchar(20),
ed_pos varchar(12),
phone varchar(10),
address varchar(30),
city varchar(20),
state char(2),
zip char(5),
ed_boss char(11));
create table titleditors (
ed_id char(11),
title_id char(6),
ed_ord integer);
create table salesdetails (
sonum integer,
qty_ordered integer,
qty_shipped integer,
title_id char(6),
date_shipped date);
Can anyone tell me what code would be to create this result? I didn't make the tables, I just have to work with what I was given.
谁能告诉我创建这个结果的代码是什么？我没有制作表格，我只需要使用我得到的东西。
回答by Tomalak
Antiquated syntax (note the intermixing of join conditions and filter conditions):
过时的语法（注意连接条件和过滤条件的混合）：
CREATE VIEW qtyorderedview AS
SELECT
salesdetails.title_id, salesdetails.qty_shipped,
editors.ed_id, editors.ed_lname, editors.ed_fname, editors.city
FROM
titleditors, salesdetails, editors
WHERE
titleditors.title_id = salesdetails.title_id
AND editors.ed_id = titleditors.ed_id
AND salesdetails.qty_ordered > 50
Modern syntax (join conditions and filter conditions are separate):
现代语法（连接条件和过滤条件是分开的）：
CREATE VIEW qtyorderedview AS
SELECT
salesdetails.title_id, salesdetails.qty_shipped,
editors.ed_id, editors.ed_lname, editors.ed_fname, editors.city
FROM
titleditors
INNER JOIN salesdetails ON titleditors.title_id = salesdetails.title_id
INNER JOIN editors ON editors.ed_id = titleditors.ed_id
WHERE
salesdetails.qty_ordered > 50
Joins against views work exactly like joins against tables. Just use the view name in place of a regular table name.
对视图的联接与对表的联接完全一样。只需使用视图名称代替常规表名称。
回答by Quassnoi
SELECT e.*
FROM (
SELECT DISTINCT te.ed_id
FROM (
SELECT title_id
FROM sales_details
GROUP BY
title_id
HAVING SUM(qty_shipped) > 50
) t
JOIN titleditors te
ON te.title_id = t.title_id
) te
JOIN editors e
ON e.ed_id = te.ed_id
