如何从 SQL Server 中包含多行数据的 2 个表中选择 4 个不同的值? [英] How can I select 4 distinct values from 2 tables containing many rows of data in SQL Server?

问题描述

我有 2 个表，我试图从 GalleryID DESC 的排序中选择 4 个结果.我希望返回的字段是 Gallery 表中的 GalleryID、GalleryTitle、GalleryDate 和 Media 表中的 MediaThumb.

I have 2 tables that Im trying to select 4 results from ordered by GalleryID DESC. The fields Im looking to return are GalleryID, GalleryTitle, GalleryDate from the Galleries table, and MediaThumb from the Media Table.

现在问题是 Media 表具有 GalleryID 的 10 的倍数.因此，Media 表中可能有 10 行具有相同的 GalleryID.我所需要的只是一个 MediaThumb 来配合galleryid.

Now the catch is that the Media table has multiples of 10 of GalleryID's. So, there could be 10 rows in the Media table with the same GalleryID. All I need is one MediaThumb to go along with the galleryid.

我不确定如何制定查询以从画廊表中返回 4 个不同的画廊 ID，并从媒体表中返回一个 MediaThumb.

Im not sure how to formulate a query to return 4 distinct galleryid's from the galleries table and a MediaThumb from the media table.

基本上，我想要做的是返回 4 个独特的照片画廊，其中包含一张来自媒体表的封面照片.

Essentially what im trying to do is return 4 unique photo galleries with a cover photo from the media table.

任何帮助将不胜感激.我通过 sqlserver 中的查询设计器提供了我的 2 个表的快照.

Any help would be appreciated. I provided a snapshot via query designer in sqlserver of my 2 tables.

推荐答案

类似的事情?

SELECT TOP 4 
    g.GalleryID
    ,g.GalleryTitle
    ,g.GalleryDate
    ,MAX(m.MediaThumb) AS MaxMediaThumb
FROM Galleries g
    INNER JOIN Media m
        ON g.GalleryID = m.GalleryID
GROUP BY g.GalleryID, g.GalleryTitle, g.GalleryDate

这篇关于如何从 SQL Server 中包含多行数据的 2 个表中选择 4 个不同的值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！