從選擇子查詢中獲取多列

SELECT 
  *, 
  p.name AS name, 
  p.image, 
  p.price,
  ( 
      SELECT ps.price 
      FROM product_special ps 
      WHERE p.id = ps.id
        AND ps.date < NOW() 
      ORDER BY ps.priority ASC, LIMIT 1
  ) AS special_price,
  ( 
      SELECT ps.date 
      FROM product_special ps 
      WHERE p.id = ps.id
        AND ps.date < NOW() 
      ORDER BY ps.priority ASC, LIMIT 1
  ) AS date
FROM product p LEFT JOIN product_special ps ON (p.id = ps.id)

如您所見，我重複相同的子查詢只是為了得到另一列。我想知道有沒有更好的方法來做到這一點？

id是兩個表中的主鍵。如果有幫助的話，我可以讓product_special.priority獨一無二。

假設組合product_special.id, product_special.priority是唯一的

SELECT p.*, special_price,special_date
FROM product p
LEFT JOIN 
(
    SELECT ps.id, ps.price as special_price, ps.`date` as special_date
    FROM product_special ps
    INNER JOIN 
    (
      SELECT id, MIN(priority) as min_priority 
      FROM product_special
      GROUP BY id
    ) ps2 
    ON (ps2.id = ps.id)
)a ON (a.id=p.id)

除非您打算將欄位作為 special_price.price 和 date.date 返回，否則為什麼不在子查詢中使用別名？例如

SELECT p.*, p.name AS  name, p.image, p.price, ps.*
FROM product p
LEFT JOIN
  (SELECT
     psi.price as special_price, psi.date as my_date 
   FROM product_special psi
   WHERE 
     p.id = psi.id AND
     psi.date < NOW()
   ORDER BY psi.priority ASC, LIMIT 1
  ) AS ps ON
 p.id = ps.id

您的查詢語言是否有 FIRST() 聚合函式？不確定是否可以將 product_special 的 PK 設為 id 和優先級（均為 ASC 排序）之間的組合，並將 ORDER 子句更改為GROUP BY id, psi.priority

您可能能夠完全刪除 ORDER BY 子句並使用HAVING MIN(psi.priority)

引用自：https://dba.stackexchange.com/questions/15976