ORDER BY 子句 不适用于联合,别名(as(出现一些错误
信使表
mid senderid receiverid message
---------------------------------------
4 100 200 hi
3 200 100 hello
2 100 200 hi
1 100 200 hi
选择声明
select senderid as new_id from messenger where receiverid=200
union
select receiverid from messenger where senderid =200
order by mid desc
错误
#1054 - Unknown column 'mid' in 'order clause'
我不知道我犯了什么错误,请帮助我提前感谢
在你的联合结果中,你没有中间列
你只有new_id
你应该添加
select mid, senderid as new_id
from messenger where receiverid=200
union
select mid, receiverid
from messenger where senderid =200
order by mid desc
或避免仅使用单个查询进行联合
select case when senderid = 200 then reciverid else senderid as new_id
from messenger
where receiverid=200 or senderid = 200
order by mid
你为什么要用union
?
select (case when receiverid = 200 then senderid else receiverid end) as new_id
from messenger m
where 200 in (receiverid, senderid)
group by new_id
order by min(mid) desc;
您的联合查询中没有中间字段,因此请将其设为
select senderid as new_id from messenger where receiverid=200
union
select receiverid as new_id from messenger where senderid =200
order by new_id desc
或到
select mid,senderid as new_id from messenger where receiverid=200
union
select mid,receiverid as new_id from messenger where senderid =200
order by mid desc