如何选择一种颜色以及与该颜色关联的第一张图像?

问题描述

我有2个名为Colors和ColorImages的表. ColorImages具有用于单一颜色的多个图像


我想做的是:我想从我的数据库中的ColorImages表中获取一张带有一张图片的Colors网格.因此，我将使用此SQL命令提取所有颜色.

I have 2 tables named Colors and ColorImages. ColorImages have multiple images for a single color


What I''m trying to do: I want a grid of Colors with one picture from ColorImages table in my database. So I''m pulling all of the colors with this SQL command.

SELECT * FROM Colors

但是我也想要一张来自ColorImages的图片

But I also want a picture from ColorImages

SELECT * FROM Colors
INNER JOIN ColorImages ON Colors.ColorID = ColorImages.ColorID
(SELECT TOP 1 ColorImages.ColorImage FROM ColorImages
 WHERE Colors.ColorID = ColorImages.ColorID)

当我尝试测试查询时，它告诉我"SELECT"和)"附近的语法不正确.

When I try to test the query it tells me I have incorrect syntax near "SELECT" and ")"

推荐答案

SELECT * FROM Colors
INNER JOIN (SELECT TOP 1 ColorImages.ColorImage FROM ColorImages
 WHERE Colors.ColorID = ColorImages.ColorID) ColorImages ON Colors.ColorID = ColorImages.ColorID

尝试嵌套查询

Try nested query

SELECT *,(SELECT TOP 1 ColorImages.ColorImage FROM ColorImages WHERE Colors.ColorID = ColorImages.ColorID) ColorImages FROM Colors

with TEMP_TABLE  (ID, Color,IMAGEID,TEMP_ID,ColorImage,Ranking)
as (
SELECT     Colors.ColorID, Colors.Color, ColorImages.ImageID, ColorImages.ColorID AS TEMP_ID, ColorImages.ColorImage
,rank() over (Partition by Colors.Color Order by ColorImages.ImageID) MyRank
FROM         Colors INNER JOIN
                      ColorImages ON Colors.ColorID = ColorImages.ColorID
)

SELECT * from TEMP_TABLE where
Ranking   = 1 ORDER BY TEMP_TABLE.ID

这篇关于如何选择一种颜色以及与该颜色关联的第一张图像?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！