如何修复 mysql 错误号 #1250 无法在字段列表中使用来自其中一个 SELECT 的表“a1"? [英] how to fix mysql error number #1250 Table 'a1' from one of the SELECTs cannot be used in field list?

问题描述

我正在按照以下方式按日期和时间对数据进行排序

I am doing following way to sorting data by date and time

select b.app_user_id,b.username,a.message_content,a.message_to,a.message_date 
from app_messages a 
left join app_users b 
on a.message_from = b.app_user_id
where a.message_to=1 and b.app_user_id= 4
UNION 
select b1.app_user_id,b1.username,a1.message_content,a1.message_to,a1.message_date 
from app_messages a1 
left join app_users b1 
on a1.message_from = b1.app_user_id 
where a1.message_to=4 and b.app_user_id= 1 
order by a1.message_date,a.message_date

这里是我的表结构我得到 mysql 错误号 #1250 来自其中一个 SELECT 的表 'a1' 不能在字段列表中使用

here my table structure I get mysql error number #1250 Table 'a1' from one of the SELECTs cannot be used in field list

如何根据我的情况正确使用 unioun 子句对数据进行排序?

how to sorting data with unioun clause correctly as my case?

推荐答案

你可以试试下面的——你需要在外层查询中使用order by子句

You can try below - you need to use order by clause in outer query

select * from
(
select b.app_user_id,b.username,a.message_content,a.message_to,a.message_date 
from app_messages a 
left join app_users b 
on a.message_from = b.app_user_id
where a.message_to=1 and b.app_user_id= 4
UNION 
select b1.app_user_id,b1.username,a1.message_content,a1.message_to,a1.message_date 
from app_messages a1 
left join app_users b1 
on a1.message_from = b1.app_user_id 
where a1.message_to=4 and b1.app_user_id= 1 
)A order by message_date

这篇关于如何修复 mysql 错误号 #1250 无法在字段列表中使用来自其中一个 SELECT 的表“a1"?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！