我有下表。我正在嘗試檢索資料并按名稱對它們進行分組，然后我只想要根據 created_at 列的最新行。我運行了下面的查詢，似乎得到了我想要的。但是，當我添加更多權重時，查詢有時會省略一些資料。到底是怎么回事？？？
"id" "name" "value" "created_at" "animal_id"
"6" "body_condition_score" "9" "2022-02-26" "1"
"9" "body_condition_score" "8" "2022-02-27" "1"
"7" "dental_score" "4" "2022-02-25" "1"
"10" "dental_score" "1" "2022-03-25" "1"
"11" "dental_score" "5" "2022-03-25" "2"
"4" "heart_rate" "1500""2022-02-25" "1"
"5" "respiratory_rate" "20" "2022-02-25" "1"
"2" "weight" "90.52" "2022-02-22" "1"
"3" "weight" "99" "2022-02-23" "1"
"13" "weight" "0" "2022-02-21" "1"
"12" "weight" "1" "2022-02-25" "2"
SELECT vitals.*
FROM vitals
INNER JOIN
(
SELECT `name`, `value`, MAX(created_at) created_at
FROM vitals
GROUP BY `name`
) grouped
ON grouped.`name` = vitals.`name`
AND grouped.created_at = vitals.created_at
WHERE animal_id = 1
"id" "name" "value" "created_at" "animal_id"
"4" "heart_rate" "1500" "2022-02-25" "1"
"5" "respiratory_rate" "20" "2022-02-25" "1"
"9" "body_condition_score" "8" "2022-02-27" "1"
"10" "dental_score" "1" "2022-03-25" "1"
uj5u.com熱心網友回復：
如果您有MySQL 8 版本，您可以使用：
with cte as (
select *,ROW_NUMBER() OVER ( partition by name ORDER BY created_at desc ) row_num
from vitals where animal_id=1
)
select id,name,value,created_at,animal_id
from cte
where row_num=1;
如果您有不支持 Windows 功能的舊版本，請嘗試：
SELECT v.id,v.name,v.value,v.created_at,v.animal_id
FROM vitals v
INNER JOIN (
SELECT name,animal_id, MAX(created_at) as created_at
FROM vitals
where animal_id=1
GROUP BY name,animal_id
) v1 ON v.name = v1.name AND v.created_at = v1.created_at
where v.animal_id=1;
演示
uj5u.com熱心網友回復：
你最后一個資料的 animal_id 值是多少？如果您為 animal_id = 2 添加“權重”并創建了最大值，那么您的查詢將不回傳“權重”行
因為：
- 在子查詢“分組”中不使用“當animal_id = 1”時，查詢回傳max created_at in animal_id = 2 是可能的
- 您在主查詢中使用內部聯接和“when animal_id = 1”，因此當子查詢回傳 created_at 且 animal_id = 2 時，它們不會鏈接。
我的建議：如果您只想選擇 animal_id = 1，請將您的子查詢更改為
INNER JOIN
(
SELECT `name`, `value`, MAX(created_at) created_at
FROM vitals
WHERE animal_id = 1
GROUP BY `name`
) grouped
如果您不關心animal_id，請在animal_id = 1 時洗掉
SELECT vitals.*
FROM vitals
INNER JOIN
(
SELECT `name`, `value`, MAX(created_at) created_at
FROM vitals
GROUP BY `name`
) grouped
ON grouped.`name` = vitals.`name`
AND grouped.created_at = vitals.created_at
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/433460.html
標籤：mysql
上一篇：空列不回傳NULL或空