问题描述
我需要使用其键 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