ORDER BY 'createdAt'歧义及子查询表别名引用失败的解决方法

执行以下SQL可正常返回结果（结果包含createdAt字段）：

SELECT *FROM
(SELECT * FROM diaries AS d, users AS u, images AS i WHERE d."userId" = u."userId" and d."diaryId" = i."diaryId") AS a
WHERE "phone" IN ('010-3334-7167', '010-5628-2214') AND "secretType" <> '비공개'
LIMIT 5 OFFSET (1 - 1) * 5

但添加ORDER BY "createdAt" DESC后触发错误：

ERROR: 42702: ORDER BY "createdAt" is ambiguous LINE 4: ORDER BY "createdAt" DESC

猜测createdAt字段存在于多张表中，尝试用子查询内的表别名d指定d."createdAt"排序，执行以下SQL：

SELECT * FROM
(SELECT * FROM diaries AS d, users AS u, images AS i WHERE d."userId" = u."userId" and d."diaryId" = i."diaryId") AS a
WHERE "phone" IN ('010-3334-7167', '010-5628-2214') AND "secretType" <> '비공개'
ORDER BY d."createdAt" DESC
LIMIT 5 OFFSET (1 - 1) * 5

又出现新错误：

ERROR: 42P01: missing FROM-clause entry for table "d" LINE 4: ORDER BY d."createdAt" DESC

请问为何会出现字段歧义？如何正确指定使用diaries表（别名d）的createdAt字段进行排序？

字段歧义的原因 #

你的子查询用SELECT *同时查询了diaries、users、images三张表，而这三张表中至少有两张包含createdAt字段。当外层SQL执行ORDER BY "createdAt"时，数据库无法判断你要使用哪一张表的createdAt字段，因此抛出歧义错误。

而用d."createdAt"排序失败，是因为表别名d仅在子查询内部有效，外层SQL无法识别子查询里的别名，所以提示找不到表d的FROM子句条目。

正确的解决方式 #

方式1：子查询中明确指定目标字段并设置唯一别名 #

避免使用SELECT *，在子查询里明确选出需要的字段，给diaries的createdAt指定一个唯一别名，外层SQL直接用这个别名排序：

SELECT * FROM
(SELECT d."createdAt" AS diary_created_at, d.*, u.*, i.* 
 FROM diaries AS d, users AS u, images AS i 
 WHERE d."userId" = u."userId" and d."diaryId" = i."diaryId") AS a
WHERE "phone" IN ('010-3334-7167', '010-5628-2214') AND "secretType" <> '비공개'
ORDER BY diary_created_at DESC
LIMIT 5 OFFSET (1 - 1) * 5

注：如果d.*已经包含createdAt，结果集会出现两个相关字段，可根据需求调整为只选取必要字段，而非用*全量查询。

方式2：将排序逻辑移至子查询内部 #

把ORDER BY放到子查询里，这样可以直接使用子查询内的表别名d：

SELECT * FROM
(SELECT * 
 FROM diaries AS d, users AS u, images AS i 
 WHERE d."userId" = u."userId" and d."diaryId" = i."diaryId"
 ORDER BY d."createdAt" DESC) AS a
WHERE "phone" IN ('010-3334-7167', '010-5628-2214') AND "secretType" <> '비공개'
LIMIT 5 OFFSET (1 - 1) * 5

注：这种方式是先排序再过滤，数据量较大时可能影响性能，可根据实际数据规模和索引情况选择。

方式3：外层SQL使用子查询别名+字段名（需确保字段唯一） #

如果能确定子查询结果集中的createdAt来自diaries表，也可以直接用a."createdAt"排序：

SELECT * FROM
(SELECT * FROM diaries AS d, users AS u, images AS i WHERE d."userId" = u."userId" and d."diaryId" = i."diaryId") AS a
WHERE "phone" IN ('010-3334-7167', '010-5628-2214') AND "secretType" <> '비공개'
ORDER BY a."createdAt" DESC
LIMIT 5 OFFSET (1 - 1) * 5

注：如果三张表都有createdAt，这种方式仍可能存在歧义，因此更推荐前两种方式。

另外，日常开发中建议尽量避免使用SELECT *，明确指定需要的字段，既能避免字段歧义，也能减少不必要的数据传输，提升查询性能。

内容的提问来源于stack exchange，提问作者Hyejung