具有最大值和特定值的 MySQL 行

Question

我必须编写一个查询来使用所选日期和所选类别从表中检索数据。我已经编写了一个查询，但它没有按预期提供正确的数据。如果 action 列的值为 'AD'，它应该只查询具有最大 actiondate 的行。

INSERT INTO goldstockvaluation SELECT sh.stockid, sh.description, sh.branch, sh.grossweight, sh.pureweight, sh.purity, sh.goldcarat, sh.mcpergram, sh.goldpergram, sh.fixgold, CURDATE( )
FROM stock_history sh
JOIN (

SELECT stockid, branch,ACTION , MAX( actiondate ) AS MaxDateTime
FROM stock_history
GROUP BY stockid,branch,ACTION 
)groupedsh ON sh.stockid = groupedsh.stockid
AND sh.actiondate = groupedsh.MaxDateTime
AND sh.branch = groupedsh.branch
AND sh.action = groupedsh.action 
AND sh.branch = '8'
AND sh.categoryid = 'G'
AND sh.action = 'AD'
AND sh.actiondate <= '2016-03-28 23:59:59'

这是查询 action 为 'AD' 且具有 max(actiondate) 的行。

Answer 1

要使用max(action_date)查询，需要使用子查询，例如:

select field1, field2
from table
where 
sh.action = 'AD'
and sh.actiondate = (
    select max(sh.actiondate)
        from table 
        where sh.action = 'AD'
);