mysql - ＃1054-“ where子句”中的未知列“ tt.date”(#1054 - Unknown column 'tt.date' in 'where clause')

Question

I've googled all over the place and tried for hours, I give up... I can't for the life of me find a workaround for this problem... #1054 - Unknown column 'bb.date' in 'where clause' .

(我到处搜寻Google，试了几个小时，我放弃了。。。我一生无法找到解决此问题的方法... #1054 - Unknown column 'bb.date' in 'where clause' 。)

I definitely need access to bb.date but I cannot access the alias "bb" in the nested LEFT JOIN but I need to access it.

(我当然需要访问bb.date，但是我无法访问嵌套的LEFT JOIN中的别名“ bb”，但是我需要访问它。)

How to pass the alias into that scope?

(如何将别名传递到该范围？)

SELECT
    bb.date,
    PP.max_sell_price_asofdate,
    i.item_id,
    bb.transaction_id
FROM items_transactions bb

LEFT JOIN items_transactions_details t
    ON bb.transaction_id = t.transaction_id

LEFT JOIN items i
    ON i.item_id = t.item_id

LEFT JOIN (
    SELECT
        pp.item_id,
        MAX(pp.sell_price_asofdate) max_sell_price_asofdate
    FROM items_prices pp
    WHERE bb.date >= ss.sell_price_asofdate -- #1054 - Unknown column 'bb.date' in 'where clause'
    GROUP BY pp.item_id
) PP ON PP.item_id = i.item_id

Probably it is better if i provide more details of what i am trying to accomplish with the tables and some of their relevant fields... It seems my query is not correct even if i get access to the bb alias.

(如果我提供我想用这些表及其某些相关字段完成的工作的更多详细信息，可能会更好。即使我可以访问bb别名，我的查询似乎也不正确。)

Basically what i am trying to do is join all of the following tables and make sure that each of the transactions' items get the correct sell_price.

(基本上我想做的就是加入所有下表，并确保每个交易项都获得正确的sell_price。)

So for example for transaction 611 which has a purchase of item_id 148 dated 2019-11-29 should be joined with the correct sell_price of 13 since the latest price history change for item_id 148 in table items_prices is dated 2019-11-28 which is sell_price 13. Currently it incorrectly joins all the prices for each item

(因此，例如，对于具有购买日期为2019-11-29的item_id 148的交易611，应以正确的sell_price 13进行合并，因为表item_prices中item_id 148的最新价格历史记录的更改日期为2019-11-28，即sell_price 13.当前，它错误地合并了每个项目的所有价格)

table items_transactions

(表items_transactions)

table items_transactions_details

(表items_transactions_details)

table items_prices

(表items_prices)

table items

(表项)

  ask by Harry McKenzie translate from so

Answer 1

You can use references to a outer table but not to a table that is in the same from clause that surrogate query.

(您可以使用对外部表的引用，但不能使用与替代查询相同的from子句中的表。)

For example, you can move surrogate query to SELECT clause:

(例如，您可以将代理查询移至SELECT子句：)

SELECT
    bb.date,
    i.item_id,
    bb.transaction_id,
    (
    SELECT
        MAX(pp.sell_price_asofdate) max_sell_price_asofdate
    FROM items_prices pp
    WHERE 
          bb.date >= pp.sell_price_asofdate 
          and pp.item_id = i.item_id
    GROUP BY pp.item_id
    ) max_sell_price_asofdate

FROM items_transactions bb

LEFT JOIN items_transactions_details t
    ON bb.transaction_id = t.transaction_id

LEFT JOIN items i
    ON i.item_id = t.item_id

Notice that a surrogate query is not so good for performance.

(请注意，代理查询的性能不是很好。)

Without surrogate query:

(没有代理查询：)

SELECT 
    A.date, A.item_id, A.transaction_id  ,
    MAX(B.sell_price_asofdate) as max_sell_price_asofdate
FROM
(
  SELECT
    bb.date,
    i.item_id,
    bb.transaction_id

  FROM items_transactions bb

  LEFT JOIN items_transactions_details t
    ON bb.transaction_id = t.transaction_id

  LEFT JOIN items i
    ON i.item_id = t.item_id
) A
LEFT OUTER JOIN
(
    SELECT
        pp.item_id,
        pp.sell_price_asofdate
    FROM items_prices pp
) B
ON 
   A.date >= B.sell_price_asofdate 
   and B.item_id = A.item_id
GROUP BY
    A.date, A.item_id, A.transaction_id