UNION函数mysql不接受ORDER BY日期

ogq8wdun 于 2022-10-22 发布在 Mysql

关注(0)|答案(1)|浏览(177)

我正在尝试使用此查询提取数据：

select fnl_res.id, fnl_res.business_name, fnl_res.owner_name from (
(select b_infos.id, b_infos.business_name, b_infos.owner_name, orders.date from b_infos 
    inner join orders on b_infos.id = orders.businessId) 
UNION
( select b_infos.id, b_infos.business_name, b_infos.owner_name from b_infos 
    inner join expenses_logs on b_infos.id = expenses_logs.businessId ) ) as fnl_res

这很好，但当我在每个工会上添加列日期和按日期排序时：

select fnl_res.id, fnl_res.business_name, fnl_res.owner_name, orders.date from (
(select b_infos.id, b_infos.business_name, b_infos.owner_name, orders.date from b_infos 
    inner join orders on b_infos.id = orders.businessId ORDER BY orders.date) 
UNION
( select b_infos.id, b_infos.business_name, b_infos.owner_name, expenses_logs.date from b_infos 
    inner join expenses_logs on b_infos.id = expenses_logs.businessId  ORDER BY expenses_logs.date ) ) as fnl_res

我得到了这个错误：错误代码1114：表已满！
已经研究了这个错误：我试图插入，但我只是在表格上选择
有什么我需要考虑的吗？

mysql

来源：https://stackoverflow.com/questions/74151380/union-function-mysql-not-accepting-order-by-date

1条答案

按热度按时间

fwzugrvs1#

在MySQL中，order by可能不会出现在子查询中。将整个查询打包为子查询，并应用order by：

select
  fnl_res.id, fnl_res.business_name, fnl_res.owner_name, orders.date
from (
  select
    b_infos.id, b_infos.business_name, b_infos.owner_name, orders.date
    from b_infos 
    join orders on b_infos.id = orders.businessId 
  union
  select
    b_infos.id, b_infos.business_name, b_infos.owner_name, expenses_logs.date
    from b_infos 
    join expenses_logs on b_infos.id = expenses_logs.businessId
) x
order by 4

请注意，如果没有where子句，您将返回每一行，可能会消耗内存并导致错误。考虑一些过滤，可能是where orders.date > '2022-10-13'

赞(0）回复(0）举报 2022-10-22

我来回答

UNION函数mysql不接受ORDER BY日期

1条答案

相关问题

热门标签

最新问答