mySQL 未选择具有 MAX 日期的行的值 [英] mySQL is not selecting the values of the row with the MAX date

问题描述

我有以下数据:

SYMBOL     | TRADE_DATE | LAST_10_DAYS
----------------------------------
ADI        | 2016-01-08 | NULL
ADI        | 2016-01-07 | NULL
ADI        | 2016-01-06 | NULL
ADI        | 2016-01-05 | NULL
ADI        | 2016-01-04 | NULL
ADI        | 2015-07-06 | 4.5

然后我编写了以下查询来尝试获取 LAST_10_DAYS 列中 MAX 日期的值:

I then wrote the following query to try and get the value in the LAST_10_DAYS column for the MAX date:

SELECT SYMBOL, MAX(TRADE_DATE) as MAX_DATE, LAST_10_DAYS 
FROM FF_HISTORICAL_STOCK_PRICE 
WHERE SYMBOL='ADI' 
GROUP BY SYMBOL

当我这样做时，我希望得到以下输出:

When I do this I would expect the following output:

SYMBOL     | TRADE_DATE | LAST_10_DAYS
----------------------------------
ADI        | 2016-01-08 | NULL

但是，我得到以下输出:

However, I get the following output:

SYMBOL     | TRADE_DATE | LAST_10_DAYS
----------------------------------
ADI        | 2016-01-08 | 4.5

为什么会发生这种情况?

Why would this be happening?

这是在 JOIN 内，但那部分不起作用.这是完整的查询:

This is within a JOIN but that part wasn't working. Here is the full query:

 SELECT AR_SQ.SYMBOL, AR_SQ.NAME, AR_SQ.LAST, FF_PS.SECTOR, FF_PS.SECTOR_TAGS, RS.LAST_10_DAYS, RS.YTD_PERF, RS.MaxDate 
 FROM AR_STOCK_QUOTE AR_SQ 
 INNER JOIN (
 SELECT HP.SYMBOL, max(TRADE_DATE) as MaxDate, HP.LAST_10_DAYS, HP.YTD_PERF 
 FROM FF_HISTORICAL_STOCK_PRICE HP 
 GROUP BY HP.SYMBOL 
 ) RS on RS.SYMBOL = AR_SQ.SYMBOL 
 JOIN FF_PRIMARY_STOCK_TABLE FF_PS on FF_PS.SYMBOL = AR_SQ.SYMBOL 
 WHERE RS.SYMBOL = 'ADI'

推荐答案

聚合函数并不像您认为的那样工作.MAX() 不选择出现最大值的行.它从命名列返回最大的值.该值可能出现在每一行！

Aggregate functions don't work how you are thinking they work. MAX() doesn't select the row where the max value occurs. It returns the greatest value from the named column. This value might occur on every row!

考虑以下查询:

SELECT SYMBOL, 
  MAX(TRADE_DATE) as MAX_DATE, 
  MIN(TRADE_DATE) as MIN_DATE, 
  AVG(TRADE_DATE) as AVG_DATE, 
  LAST_10_DAYS 
FROM FF_HISTORICAL_STOCK_PRICE 
WHERE SYMBOL='ADI' 
GROUP BY SYMBOL

MySQL 应该从每组中的哪一行获取 LAST_10_DAYS 的值?日期最大的行?日期最低的行?如果超过一行与最大日期相关联，即两行或更多行具有相同的日期，但 LAST_10_DAYS 的值不同，该怎么办?

From which row in each group should MySQL take the value of LAST_10_DAYS? The row that has the greatest date? The row that has the lowest date? What if more than one row is tied for the greatest date, i.e. two or more rows have the same date, but different values for LAST_10_DAYS?

AVG_DATE 怎么样?也许平均日期不会出现在表中的任何行上.那么你期望 LAST_10_DAYS 的值是什么?

What about the AVG_DATE? Perhaps the average date doesn't occur on any row in the table. What then do you expect to be the value of LAST_10_DAYS?

您想要获取的不仅是最大值，还有出现该值的行.

What you are trying to get is not only the max value, but the row where that value occurs.

SELECT AR_SQ.SYMBOL, AR_SQ.NAME, AR_SQ.LAST, FF_PS.SECTOR,
  FF_PS.SECTOR_TAGS, RS.LAST_10_DAYS, RS.YTD_PERF, RS.TRADE_DATE 
FROM AR_STOCK_QUOTE AR_SQ 
INNER JOIN FF_HISTORICAL_STOCK_PRICE RS
  ON RS.SYMBOL = AR_SQ.SYMBOL
INNER JOIN (
  SYMBOL, MAX(TRADE_DATE) AS TRADE_DATE
  FROM FF_HISTORICAL_STOCK_PRICE
  GROUP BY SYMBOL 
) MAXRS 
  ON RS.SYMBOL = MAXRS.SYMBOL AND RS.TRADE_DATE = MAXRS.TRADE_DATE
JOIN FF_PRIMARY_STOCK_TABLE FF_PS on FF_PS.SYMBOL = AR_SQ.SYMBOL 
WHERE RS.SYMBOL = 'ADI'

子查询返回交易品种，以及每个交易品种的最大交易日期.然后您需要再次将该值加入到股票价格表中，以找到该日期所在的行.然后您可以从该行获取其他列.

The subquery returns the symbols, and the max trade date for each symbol. Then you need to join that value to the stock price table again, to find the row where that date occurs. Then you can get other columns from that row.

这篇关于mySQL 未选择具有 MAX 日期的行的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！