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

How to select a single record in a left join(如何在左联接中选择单个记录)
本文介绍了如何在左联接中选择单个记录的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着跟版网的小编来一起学习吧!

问题描述

我需要使用其键 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

这篇关于如何在左联接中选择单个记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!

本站部分内容来源互联网,如果有图片或者内容侵犯您的权益请联系我们删除!

相关文档推荐

Number of working days between two dates(两个日期之间的工作日数)
How do I use dateadd to get the first day of last year?(如何使用 dateadd 获取去年的第一天?)
SQL- Count occurrences of a specific word within all stored procedures(SQL- 计算所有存储过程中特定单词的出现次数)
SQL query to make a column of numbers a string(使一列数字成为字符串的 SQL 查询)
T-SQL: Best way to replace NULL with most recent non-null value?(T-SQL:用最新的非空值替换 NULL 的最佳方法?)
Count days in date range with set of exclusions which may overlap(使用一组可能重叠的排除项计算日期范围内的天数)