SQL 连接三个表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8016978/
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 Join three tables
提问by Robert Robinette
I'm learning advanced SQL queries little by little and I'm fairly stumped with a problem:
我正在一点一点地学习高级 SQL 查询，但遇到了一个问题：
I have three tables: news, author, and images. Each field in the newstable (newsID) is a news story, which then has an associated author in the authortable (authorID) and can have any number of images associated in the imagestable. Each image has and associated (newsID). So each story has one author but can have several images.
我有三个表：news，author，和images。news表 ( newsID)中的每个字段都是一个新闻报道，然后在author表 ( authorID) 中有一个关联的作者，并且可以在images表中关联任意数量的图像。每个图像都有关联 ( newsID)。因此，每个故事都有一个作者，但可以有多个图像。
I want to make a list of all news stories and use just oneof the images as a thumbnail image. The problem is that any sql query I try to list the newsitems with gives me results equal to the number of images in the imagestable rather than the number of newsitems.
我想列出所有新闻故事，并仅使用其中一张图像作为缩略图。问题是，我尝试列出news项目的任何 sql 查询都会给我的结果等于images表中的图像数而不是news项目数。
I don't know where to go from here. Any help would be greatly appreciated.
我不知道从这里去哪里。任何帮助将不胜感激。
回答by Kash
If the 3 tables in question are [news], [author] and [image] with appropriate columns, then
如果有问题的 3 个表是 [ news]、[ author] 和 [ image] 并带有适当的列，则
Derived Table approach
派生表方法
you can have a derived image table to get one image per news and then join it with the newsand authortable as shown. This has been written and tested in SQL Server.
您可以使用派生图像表为每个新闻获取一张图像，然后将其与新闻和作者表连接，如图所示。这已在 SQL Server 中编写和测试。
SELECT
N.[newsStoryTitle]
,A.authorName
,I.imageData1
FROM [news] N
LEFT OUTER JOIN author A ON A.newsID = N.newsID
LEFT OUTER JOIN
(
SELECT newsID, MAX(imageData) AS imageData1 FROM [image]
GROUP BY newsID
) AS I ON I.newsID = N.newsID
ORDER BY N.newsID
You could replace the LEFT OUTER JOINs with INNER JOINs if you do not need news without any images.
如果您不需要没有任何图像的新闻，您可以将 LEFT OUTER JOIN 替换为 INNER JOIN。
Correlated Subquery approach(as suggested by Marcelo Cantos)
相关子查询方法（由 Marcelo Cantos 建议）
If the imageData is stored as a text or image, then the MAX in the derived table wouldn't work. In that case, you can use a correlated subquery like this:
如果 imageData 存储为文本或图像，则派生表中的 MAX 将不起作用。在这种情况下，您可以使用这样的相关子查询：
SELECT N.newsStoryTitle ,
A.authorName ,
I.imageData
FROM dbo.news N
INNER JOIN dbo.author A ON N.newsID = A.newsID
INNER JOIN dbo.image I ON N.newsID = I.newsID
WHERE imageID = ( SELECT MAX(imageID)
FROM dbo.image
WHERE newsID = N.newsID
)
ORDER BY n.newsID
回答by Marcelo Cantos
One option is to add the following predicate:
一种选择是添加以下谓词：
FROM news JOIN images ...
...
WHERE imageID = (SELECT MAX(imageID)
FROM image
WHERE newsID = news.newsID)
Note that this excludes news items without an image. If you don't want this, you'll need a left join on images and an additional condition on the WHERE:
请注意，这不包括没有图像的新闻项目。如果你不想要这个，你需要对图像进行左连接，并在 WHERE 上附加一个条件：
FROM news LEFT JOIN images ...
...
WHERE imageID IS NULL
OR imageID = (SELECT MAX(imageID)
FROM image
WHERE newsID = news.newsID)
回答by dotjoe
You can to modify the order by on the subselect to get the 1 image per news row you are looking for...
您可以在子选择上修改顺序，以获得您正在寻找的每个新闻行的 1 个图像...
select
....
from news n
left outer join images i on i.imageID = (
select top 1 i2.imageID
from images i2
where i2.newsID = n.newsID
order by --??
)
回答by user2147772
If you have 3 table in mysql and you want to join it together. for example I have 3 table 1 student 2 subject 3 score now want to join student with subject and score. so we you this syntax : select * from student inner join subject inner join score;
如果您在 mysql 中有 3 个表并且您想将它们连接在一起。例如我有 3 表 1 学生 2 科目 3 分数现在想加入学生的科目和分数。所以我们你这个语法： select * from student inner join subject inner join score;
