我需要使用ModelID键从Models表中选择一个特定的模型.我还需要从Model_Content表中添加一些内容.但是,Models_Content表对于每个模型都有几个内容的blurbs.我只需要选择第一个blurb.
我的表格如下所示：
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
解决方法
SELECT
M.ModelID,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
要么
;WITH sorted_content AS
(
SELECT
ROW_NUMBER() OVER (PARTITION BY ModelID ORDER BY ContentID) AS itemID,*
FROM
Models_Content
)
SELECT
M.ModelID,C.Content
FROM
Models M
LEFT JOIN
sorted_content C
ON C.ModelID = M.ModelID
AND C.itemID = 1
WHERE
M.ModelID = 5
