如何从一个数据库表MySQL中的组返回第一个值

I have a table called photos in a MySQL database that contains photo filenames and other related info. Structure as follows:

我在MySQL数据库中有一张名为照片的表,其中包含照片文件名和其他相关信息。结构如下:

+----------+------------+-------------+
| photo_id | gallery_id | photo_fname |
|----------|------------|-------------|
|     1    |      1     |  DSC2001    |
|     2    |      1     |  DSC2002    |
|     3    |      1     |  DSC2003    |
|     4    |      2     |  DSC2004    |
|     5    |      2     |  DSC2005    |
|     6    |      2     |  DSC2006    |
|     7    |      3     |  DSC2007    |
|     8    |      3     |  DSC2008    |
|     9    |      3     |  DSC2009    |
+----------+------------+-------------+

What I want to do is write a query that returns the first photo filename for each gallery, as follows:

我想要做的是编写一个返回每个图库的第一个照片文件名的查询,如下所示:

+----------+------------+-------------+
| photo_id | gallery_id | photo_fname |
|----------|------------|-------------|
|     1    |      1     |  DSC2001    |
|     4    |      2     |  DSC2004    |
|     7    |      3     |  DSC2007    |
+----------+------------+-------------+

I researched this until my headache overcame me last night and although I found similar questions, they didn't seem to me to be looking to do the exact same thing. I tried subqueries, I tried the sample code from other posts here but couldn't make it work. Either due to brain fog or just not finding the right solution.

我研究了这个,直到昨晚我的头痛克服了我,虽然我发现了类似的问题,但他们似乎并不想做同样的事情。我尝试了子查询,我在这里尝试了其他帖子的示例代码,但无法使其工作。无论是由于脑雾还是没有找到正确的解决方案。

I just want to return the results as shown above, nothing more.

我只想返回如上所示的结果,仅此而已。

Many thanks in advance!

提前谢谢了!

UPDATE: My apologies, the table name is photos, FWIW.

更新:道歉,表名是照片,FWIW。

Secondly, all I really want is the filename, not the photo or gallery Id, I just included those for visual clarity. I apologize if that caused confusion.

其次,我真正想要的是文件名,而不是照片或图库ID,我只是为了视觉清晰而包含了这些文件。如果这引起混乱,我道歉。

4 个解决方案

#1

Try this:

SELECT *
FROM YourTable
WHERE photo_id IN ( SELECT MIN(photo_id) 
                    FROM YourTable
                    GROUP BY gallery_id)

See a demo

For your updated question:

对于您更新的问题:

SELECT photo_fname
FROM photos
WHERE photo_id IN ( SELECT MIN(photo_id) 
                    FROM photos
                    GROUP BY gallery_id)

#2

Here is an easy way using inner join

这是使用内连接的简单方法

SELECT
  m.*
FROM sparkles as m
  INNER JOIN (SELECT
        photo_id,
        MIN(photo_id)
          FROM sparkles
          GROUP BY gallery_id) as l
    on l.photo_id = m.photo_id

Demo

#3

SELECT photo_id, gallery_id, photo_frame
FROM ...
GROUP BY photo_id, gallery_id

by default, mysql will return the FIRST non-grouped value it encounters in the DB for any non-aggregated fields in the field list.

默认情况下,对于字段列表中的任何非聚合字段,mysql将返回它在DB中遇到的FIRST非分组值。

#4

Select * from tablename group by gallery_id;       ?