本文介绍了如何在左联接中选择单个记录的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我需要使用其键 ModelID 从 Models 表中选择一个特定的模型.我还需要从 Model_Content 表中添加一个内容简介.但是，Models_Content 表中的每个模型都有几个内容简介.我只需要选择第一个简介.
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.
我的表格如下所示:
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?
推荐答案
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
或
;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
这篇关于如何在左联接中选择单个记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！
本站部分内容来源互联网,如果有图片或者内容侵犯您的权益请联系我们删除！