mysql - SQL查询问题[内连接和多重选择]

Question

这是我的查询:

SELECT * FROM 
   (SELECT messages.*, 
   users.* 
   FROM `afo_messages` as messages 
   INNER JOIN `key_users` as users ON messages.user_id = users.id 
   WHERE messages.category_id=5 
   ORDER BY messages.id DESC LIMIT 30) ilv 
ORDER BY messages.id ASC

错误是“#1060 - 列名称‘id’重复”。

表 afo_messages 列:

id | user_id | category_id | parent_id | message | create_date | update_date | status

表 key_users 列:

id | name | email | phone | pwd | active | role | invite_id | download_link | date

我做错了什么？

谢谢!

Answer 1

可以肯定的是，当您运行内部查询时，结果集有两个名为 ID 的列(来自消息，一个来自用户)；然后您对该子查询运行查询，而解析器不理解它。

尝试:

SELECT * FROM 
(SELECT messages.id as message_id,
       messages.user_id, 
       ....all other columns from messages

       users.id as real_user_id, 
       users.name, 
       ..... all other columns from users
FROM `afo_messages` as messages 
INNER JOIN `key_users` as users ON messages.user_id = users.id 
WHERE messages.category_id=5 
ORDER BY messages.id DESC LIMIT 30) ilv 
ORDER BY message_id ASC