SQL 如何在左联接中选择单个记录

How to select a single record in a left join

提问by Evik James

I need to select a specific model from the Models table using its key ModelID. I also need to add a blurb of content from the Model_Content table. The Models_Content table, however, has several blurbs of content for each model. I need to select just the first blurb.

我需要使用其键 ModelID 从 Models 表中选择一个特定的模型。我还需要从 Model_Content 表中添加一个内容简介。但是，Models_Content 表中的每个模型都有几个内容简介。我只需要选择第一个简介。

My tables look like this:

我的表是这样的：

 Models // table
 ModelID // pk
 Model // varchar

 Models_Content // table
 ContentID // pk
 ModelID // fk
 Content // varchar

 SELECT M.ModelID, M.Model, C.Content
 FROM   Models M LEFT JOIN Models_Content C ON M.ModelID =  C.ModelID
 WHERE      M.ModelID = 5

How do I adjust my query to select just the very first blurb of content for a specific model?

如何调整我的查询以仅选择特定模型的第一个内容简介？

回答by MatBailie

 SELECT
   M.ModelID, M.Model, C.Content
 FROM
   Models M
 LEFT JOIN
   Models_Content C
     ON C.ContentID = (SELECT MIN(ContentID) FROM Models_Content WHERE ModelID = M.ModelID)
 WHERE
   M.ModelID = 5

Or

或者

;WITH sorted_content AS
(
  SELECT
    ROW_NUMBER() OVER (PARTITION BY ModelID ORDER BY ContentID) AS itemID,
    *
  FROM
    Models_Content
)
 SELECT
   M.ModelID, M.Model, C.Content
 FROM
   Models M
 LEFT JOIN
   sorted_content C
     ON  C.ModelID = M.ModelID
     AND C.itemID  = 1
 WHERE
   M.ModelID = 5

回答by Martin Smith

Sean's answer is the best specific solution but just to add another "generalised" solution

肖恩的答案是最好的具体解决方案，但只是添加另一个“通用”解决方案

SELECT M.ModelID,
       M.Model,
       C.Content
FROM   Models M
       OUTER APPLY (SELECT TOP 1 *
                    FROM   Models_Content C
                    WHERE  M.ModelID = C.ModelID
                    ORDER  BY C.ContentID ASC) C
WHERE  M.ModelID = 5  

回答by Sean

 SELECT TOP 1 M.ModelID, M.Model, C.Content
 FROM   Models M LEFT JOIN Models_Content C ON M.ModelID =  C.ModelID
 WHERE      M.ModelID = 5
 ORDER BY C.ContentID ASC

回答by JNK

Change your JOINto:

将您更改JOIN为：

LEFT JOIN (SELECT ModelID, MAX(Content) as Content FROM Models_Content GROUP BY ModelID)

LEFT JOIN (SELECT ModelID, MAX(Content) as Content FROM Models_Content GROUP BY ModelID)

This is assuming you don't care which Contentyou get.

这是假设你不在乎Content你得到了什么。

回答by mbrakken

This may be an expansion on Randy's answer, but it's not completely clear to me.

这可能是对兰迪答案的扩展，但我并不完全清楚。

For performance reasons I wanted to minimize the number of SELECTstatements in my query, so I used a MINstatement within the primary select instead of within the JOIN:

出于性能原因，我想尽量减少SELECT查询中的语句数量，因此我MIN在主选择中使用了一个语句，而不是在JOIN：

SELECT 
    table_1.id AS id, 
    table_1.name AS name,
    MIN(table_2.id) AS table_2_id 
FROM table_1
LEFT JOIN table_2 ON table_2.table_1_id = table_1.id
-- additional JOINs and WHERE excluded
GROUP BY table_1.id, table_1.name
LIMIT 5

There may be tradeoffs depending on the total amount of data. IDK. My query needs to tunnel into the data from a condition several steps removed.

可能会根据数据总量进行权衡。身。我的查询需要从几个步骤中删除的条件隧道进入数据。

回答by Randy

you could select the MIN ( contentID )

你可以选择 MIN ( contentID )

回答by Meenakshi

SELECT  M.ModelID
      , M.Model
      , (SELECT TOP 1 Content 
         FROM Models_Content 
         WHERE ModelID = M.ModelID 
         ORDER BY ContentId ASC) as Content
FROM    Models M 
WHERE   M.ModelID = 5