mysql - 为什么我无法将这个查询与最大日期连接起来？

Question

以下 mySQL 查询存在问题，当引入最大日期时，查询会失败，如下所示。我收到以下错误

Error Code: 1054. Unknown column 'order_items.ORDER_ITEM_ID' in 'where clause'

SET @UserID = 160;
SET @OrderDateTime = '2018-11-13 09:23:45';

SELECT 
order_items.ORDER_ID,
listing_region.LIST_REGION_REGION_ID,
listings.LISTING_ID,
order_items.ORDER_REQUIRED_DATE_TIME,
listings.LISTING_NICK_NAME,
order_items.ORDER_QUANTITY,
order_price.ORDER_PRICE_ID,
order_items.ORDER_PORTION_SIZE,
t.LATEST_DATE,
t.ORDER_STATUS


FROM order_status_change, order_items 

INNER JOIN listings ON listings.LISTING_ID = order_items.ORDER_LISTING_ID
INNER JOIN listing_region ON listing_region.LIST_REGION_LISTING_ID = listings.LISTING_ID
INNER JOIN order_price ON order_price.ORDERP_ITEM_ID = order_items.ORDER_ITEM_ID
INNER JOIN 
  (
     SELECT MAX(order_status_change.ORDER_STATUS_CHANGE_DATETIME) AS LATEST_DATE, order_status_change.ORDER_ITEM_ID, order_status_change.ORDER_STATUS
     FROM order_status_change
     WHERE order_status_change.ORDER_ITEM_ID = order_items.ORDER_ITEM_ID
  ) AS t ON order_status_change.ORDER_ITEM_ID = t.ORDER_ITEM_ID AND order_status_change.ORDER_STATUS_CHANGE_DATETIME = t.LATEST_DATE 

WHERE ((order_items.ORDER_USER_ID = @UserID) AND DATE(order_items.ORDER_REQUIRED_DATE_TIME) = DATE(@OrderDateTime))

有什么帮助吗？

Answer 1

我假设您可以在 order_items.ID = order_status_change.ORDER_ITEM_ID 上加入 order_status_change

如果这是有效的，那么我认为这将实现您所追求的目标:

SET @UserID = 160;
SET @OrderDateTime = '2018-11-13 09:23:45';


SELECT
    order_items.ORDER_ID
  , listing_region.LIST_REGION_REGION_ID
  , listings.LISTING_ID
  , order_items.ORDER_REQUIRED_DATE_TIME
  , listings.LISTING_NICK_NAME
  , order_items.ORDER_QUANTITY
  , order_price.ORDER_PRICE_ID
  , order_items.ORDER_PORTION_SIZE
  , t.LATEST_DATE
  , order_status_change.ORDER_STATUS
FROM order_items
INNER JOIN listings ON listings.LISTING_ID = order_items.ORDER_LISTING_ID
INNER JOIN listing_region ON listing_region.LIST_REGION_LISTING_ID = listings.LISTING_ID
INNER JOIN order_price ON order_price.ORDERP_ITEM_ID = order_items.ORDER_ITEM_ID

INNER JOIN order_status_change ON order_items.ID = order_status_change.ORDER_ITEM_ID
INNER JOIN (
        SELECT
            MAX( mc.ORDER_STATUS_CHANGE_DATETIME ) AS LATEST_DATE
          , mc.ORDER_ITEM_ID
        FROM order_status_change AS mc
        GROUP BY
            mc.ORDER_ITEM_ID
    ) AS t
             ON order_status_change.ORDER_ITEM_ID = t.ORDER_ITEM_ID
            AND order_status_change.ORDER_STATUS_CHANGE_DATETIME = t.LATEST_DATE 

WHERE order_items.ORDER_USER_ID = @UserID
AND DATE( order_items.ORDER_REQUIRED_DATE_TIME ) = DATE( @OrderDateTime )

您将来需要避免这种情况:

FROM order_status_change  , order_items 

两个表名之间的逗号IS是一个联接，但它来自较旧的语法，并且优先级低于查询的其他联接。另外，默认情况下，这种基于逗号的联接相当于交叉联接，它使行数成倍增加。简而言之，请不要在表名之间使用逗号。

另一个问题是您缺少 group by 子句，我相信您只想从此聚合中获取“最新”日期，一旦确定链接回该表即可获取与该日期相关的状态。 (即，您不能在子查询中按状态进行分组，否则您将获得最新日期(每种状态一个)。