我試圖從我的表中選擇具有最新日期的附加值“狀態”，但前提是左連接表物件可見。每個財產和單位只有一次。到目前為止我所做的：
SELECT propertyUnitEnergyStates.id,
propertyUnitEnergyStates.property_id,
propertyUnitEnergyStates.unit_id,
propertyUnitEnergyStates.type,
propertyUnitEnergyStates.state,
propertyUnitEnergyStates.date,
propertyUnits.visible
FROM propertyUnitEnergyStates
LEFT JOIN propertyUnits
ON propertyUnits.property_id = $propertyID
WHERE propertyUnitEnergyStates.property_id = $propertyID
AND propertyUnitEnergyStates.type = '$name'
AND propertyUnits.visible = 1
GROUP BY propertyUnitEnergyStates.unit_id
ORDER BY propertyUnitEnergyStates.date DESC
我現在得到的是每個 propertyUnits.visible 為 1 的結果。即使表中設定為零。
來自 propertyUnits 的物件。如您所見，可見值是 0，但我得到的是 1。
現在我注意到，我想成為最新的“狀態”值并不是最新的。
如您所見，我的結果中 unit_id 5 值為 853，但在表中 propertyUnitEnergyStates 是最新值 400。
uj5u.com熱心網友回復：
好的。讓我們試試這樣。
WITH ranked_state AS (
SELECT us.*, ROW_NUMBER() OVER (PARTITION BY unit_id ORDER BY date DESC) AS rn
FROM propertyUnitEnergyStates us
WHERE us.type = '$name' AND us.property_id = $propertyID
)
SELECT ranked_state.id,
ranked_state.property_id,
ranked_state.unit_id,
ranked_state.type,
ranked_state.state,
ranked_state.date,
pu.visible
FROM ranked_state
LEFT JOIN propertyUnits pu
ON ranked_state.property_id = pu.property_id AND pu.visible = 1
WHERE rn = 1
如果我解釋這個查詢。首先，對于滿足給定條件（例如：）us.type="$name", us.property_id=$propertyID的行，具有相同條件的行unit_id是ordered by date. 然后做LEFT JOIN。
uj5u.com熱心網友回復：
我嘗試了不同的選項，但仍然無法得到結果。它仍然忽略可見屬性并且不按日期回傳最后一條記錄。
這是我的最后一次嘗試：
$query = "SELECT UniEne.id,
UniEne.property_id,
UniEne.unit_id,
UniEne.type,
UniEne.state,
UniEne.date,
propUni.visible
FROM
(SELECT * FROM propertyUnitEnergyStates WHERE type = '$name' AND property_id = $propertyID ORDER BY date DESC) AS UniEne
INNER JOIN (SELECT * FROM propertyUnits WHERE property_id= $propertyID AND visible = 1) as propUni
WHERE UniEne.property_id = $propertyID GROUP BY UniEne.unit_id";
結果是：
但是值“狀態”是不正確的。最后插入的 unit_id(1) 值為 7000..
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/385367.html
上一篇：SQL中的求和非空值塊
下一篇：如何以盡可能短的大小存盤數字？