mySQL 未选择具有 MAX 日期的行的值

本文介绍了mySQL 未选择具有 MAX 日期的行的值的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有以下数据:

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.