您好,我正在尝试制作一个想要相册的网站。
问题是这个网站将有2个表,1个表存储相册名称(TBLalbum
(和该相册的唯一ID(id
,albumname
(。第二个表(TBLphotos
(存储有关上传的照片以及每张照片所属的相册的信息(albumid
,photoname
和更多列(。现在我想列出所有相册名称,这并不难做到,而且在同一个sql查询中,我希望它显示每个相册中的第一张照片。
我试过这个:
SELECT DISTINCT albumid
FROM TBLphotos INNER JOIN TBLalbum
ON TBLalbum.id=TBLphotos.albumid
这使我无法打印出列photoname
如果我像这样包含它:
SELECT DISTINCT albumid,photoname
FROM TBLphotos INNER JOIN TBLalbum
ON TBLalbum.id=TBLphotos.albumid
然后它不会只albumid
distinct
.
我有点难以解释,但谁能告诉我如何解决这个问题?
请求的正确查询是:
SELECT a.albumid, f.photoname
FROM TBLalbum a
INNER JOIN TBLphotos f # "f" from "first" photo
ON f.albumid = a.id # of this album
LEFT JOIN TBLphotos o # "o" from "other" photo
ON o.albumid = f.albumid # of the same album
AND o.photoid < f.photoid # "o" is before "f"
WHERE o.photoid IS NULL # no such "o" exists
解释
此查询将表 TBLalbum
(别名为"相册"中的 a
"(与TBLphotos
(别名为"此相册的第一张照片"中的 f
(联接。由于INNER JOIN
,没有照片的相册将不包括在内。如果要包含它们,请改用LEFT JOIN
。
接下来,它再次TBLalbum
连接表(别名为"其他照片"中的o
(。第一个连接条件(o.albumid = f.albumid
(确保从o
中选择的照片与f
中的匹配照片位于同一相册中。第二个条件(o.photoid < f.photoid
(仅将f
1 中的行与具有较小photoid
(即较早创建的(的行配对o
。将其替换为您自己之前的定义,以便按预期获得第一张照片。
因为第二个JOIN
是 LEFT JOIN
,它会将 f
1 中的所有行添加到结果集中,包括那些在o
中没有任何匹配的行,因为o
中没有照片早于当前从f
中选择的照片。对于这些行,将使用充满 NULL
s 的行,而不是 o
中缺少的行。
WHERE
子句从连接产生的结果集中过滤掉o.photoid
中具有NULL
值的对(a
、f
、o
(,即当同一相册中没有">其他照片"在o
中被发现早于f
的某张照片时。这些是您期望的每个相册的第一张照片。
笔记
1 LEFT JOIN
将已连接的a
表和f
表(左(与表o
(右(合并在一起。结果集将包含由 a
和 f
INNER JOIN
生成的所有行组合,至少一次(而不是如上所述f
中的所有行;我更喜欢这样写以使其更简单(。
2 我假设表 TBLphotos
中有一个名为 photoid
的字段,它是表的PK
(具有不同的值很重要(。如果更改排序条件以定义表中的第一张照片(例如,使用上传时间(,并且您使用的字段没有不同的值,则在 tie 上,查询将返回所选列具有相同最小值的所有照片。您可以通过在 ON
子句中保留/添加使用 photoid
(或其他可以解决平局的字段(的条件来轻松解决此问题。
3 您可以将您想要/需要的任何字段放入 SELECT
子句中,但只能从表 a
和 f
中输入。没有什么能阻止你放置o
字段,但所有这些都是NULL
的。如果您不明白原因,请再次阅读整个解释。
4 如果您想了解此查询的工作原理,请删除 WHERE
子句,使用 MySQL 客户端运行它并分析结果集。
5 您会在 SO 标记的每组最大 n 上找到很多类似问题的答案。如果他们使用GROUP BY
避免使用它们,因为它们是错误的并且不符合SQL标准(它们中的大多数都是偶然工作的(。如果他们使用子查询(在内部查询中带有或不带有GROUP BY
(,它们可能是正确的,但大多数时候它们很慢。子查询很难优化,很多时候甚至是不可能的。如果他们使用MySQL变量,那么它们也很慢。我不知道为什么MySQL变量很慢,我想这是因为在这种情况下查询计划器无法充分利用索引。
尝试:
SELECT
a.albumid,
a.albumname,
(SELECT photoname FROM TBLphotos p WHERE p.albumid = a.id LIMIT 1) AS photoname
FROM TBLalbum a
子查询将每个唱片集的结果集限制为仅一个带有 LIMIT
子句的记录。 如果你想从TBLphotos
获得特定的记录,你可以修改子查询以按一定的顺序排列东西,或者做你需要做的任何事情。
请注意,DISTINCT
用于过滤掉重复的行,这不是(我认为(您在这里真正要做的事情。