从具有顺序和组的多个表中进行选择 - selecting from multiple tables with order and group

selecting from multiple tables with order and group

本文关键字：选择行选顺序 | 更新日期: 2023-09-27

我一直有这个问题，找不到解决方案，我需要使用 Union 从多个表中进行选择以选择 ID，然后按日期对结果进行排序，我已经进行了此查询（它曾经在较旧的 MySQL 版本中工作）

(SELECT MAX(id),UNIX_TIMESTAMP(date) AS date,stock_id, 'Purchase' as type FROM stock_entries WHERE type='entry' GROUP BY stock_id)
                UNION
                (SELECT MAX(id),UNIX_TIMESTAMP(date) AS date,stock_id, 'Sales' as type FROM stock_entries WHERE type='sales' GROUP BY stock_id)
                UNION (select id,date, `order` AS stock_id, 'Inventory' as type FROM inventory_trans)
                ORDER BY date ASC

我

想要的是，我每次生成一个ID，并且还生成一种类型，类型是购买或销售或库存，并且所有结果都应按两个表中的日期（较旧-->较新）排序，每当我运行上述查询时，我都有此错误：

1140: In aggregated query without GROUP BY, expression #3 of SELECT list contains nonaggregated column 'system.inventory_trans.order'; this is incompatible with sql_mode=only_full_group_by

如何根据日期范围（日期 ASC）表达此 SQL 查询以准确获得我需要的内容 - 较旧的结果 ->较新的结果

谢谢

请尝试此查询。

SELECT * FROM
  (SELECT s.id,UNIX_TIMESTAMP(s.date) as date,s.stock_id,
       M.newType AS type
   FROM stock_entries s
   INNER JOIN
     (SELECT MAX(id) as maxid, stock_id,IF(type='entry','Purchase','Sales') as newType
      FROM stock_entries
      WHERE type='entry' OR type='sales'
      GROUP BY stock_id,newtype) as M
   ON M.maxid = s.id 
   )T1   
UNION ALL
  (select id,`date`, `order` AS stock_id, 'Inventory' as type FROM inventory_trans)
ORDER BY date ASC

SQLfiddle