mysql - 为什么在 "JOIN (SELECT)"语句中使用别名会导致错误？

Question

我正在使用 MySQL 5.5.16

我有以下查询，它本身工作得很好。

SELECT DISTINCT i.id, 
CASE WHEN e.date >  '2012-10-16'
THEN e.date
ELSE  '9999-99-99'
END AS date, 
CASE WHEN e.date >  '2012-10-16'
THEN time
ELSE  '99-99-99'
END AS time
FROM items AS i
LEFT JOIN expiration AS e ON ( e.item_id = i.id ) 
WHERE (
(
data >=  '2012-10-16'
AND e.valid=1
)
OR i.never_expires=1
)
AND i.valid=1
ORDER BY date ASC , time ASC 
LIMIT 0 , 10

但是，当我将它包含在一个更大的查询中时，我收到错误消息 Column 'date' in where clause is ambiguous。下面是一个示例，其中上面的查询位于 JOIN 中:

SELECT i.id, i.title, i.never_expires, 
CASE WHEN e.date>  '2012-10-16'
THEN e.date
ELSE  '9999-99-99'
END AS date, 
CASE WHEN e.date >  '2012-10-16'
THEN e.time
ELSE  '99-99-99'
END AS time, i.item_price AS price
FROM items AS i
LEFT JOIN expiration AS e ON ( e.item_id = i.id )
JOIN (
  SELECT DISTINCT i.id, 
  CASE WHEN e.date >  '2012-10-16'
  THEN e.date
  ELSE  '9999-99-99'
  END AS date, 
  CASE WHEN e.date >  '2012-10-16'
  THEN time
  ELSE  '99-99-99'
  END AS time
  FROM items AS i
  LEFT JOIN expiration AS e ON ( e.item_id = i.id ) 
  WHERE (
  (
  data>=  '2012-10-16'
  AND e.valid=1
  )
  OR i.never_expires=1
  )
  AND i.valid=1
  ORDER BY date ASC , time ASC 
  LIMIT 0 , 10
) AS ilist ON (i.id=ilist.id) 
WHERE (
(
date >=  '2012-10-16'
AND e.valid=1
)
OR i.never_expires=1
)
AND i.valid=1
ORDER BY dateASC , time ASC

为什么它声称 date 不明确？

附言我尝试用 inner_date 替换内部查询的 AS date 部分中的 date，但这只会引发另一个错误 Unknown “where 子句”中的列“inner_date”...

Answer 1

您正在将具有名为 date 的列的 expiration 表连接到本身具有一列的物化表(来自 CASE 表达式)命名为 date。您应该在 WHERE 子句中使用您打算引用的任何表的别名来限定 date 的使用。

或者:

WHERE (
(
ilist.date >=  '2012-10-16'
AND e.valid=1
)

或者:

WHERE (
(
e.date >=  '2012-10-16'
AND e.valid=1
)