MySQL语句拉出太多行 - MySQL statement pulling too many rows

MySQL statement pulling too many rows

本文关键字：太多语句 MySQL | 更新日期: 2023-09-27

我有两个数据表，我要使用Inner Join语句查询它们。其中一个表提取每天的公司股票信息，因此在市场开放的每一天，每个公司都有一行。另一个表保存公司的定性数据，每个公司只有一行。

我正在尝试拉出最新的股票信息，并将其与HTML表中的定性公司信息配对。我的一切都在工作，除了它仍然为公司提取每一行的日常信息，而不是最近的。

有人能帮助解决这个问题吗?:

$query = "SELECT daily_info.Day, 
daily_info.Prev_close, 
stocks.Symbol, 
stocks.Company, 
stocks.Description 
FROM daily_info 
INNER JOIN stocks ON daily_info.Symbol = stocks.Symbol 
ORDER BY daily_info.Day, stocks.Company LIMIT 43
"; 
Example: 
Table 1: Daily_info
Day       |  Symbol  |  Company    |  Prev Close
06/15/14  |  CRM     |  Salesforce |  $52.34
06/15/14  |  AMZN    |  Amazon     |  $342.65
06/16/15  |  CRM     |  Salesforce |  $55.24
06/16/14  |  AMZN    |  Amazon     |  $349.64

Table 2: Stock
Symbol  |  Company           | Description
CRM     |  Salesforce.com    | This is a cloud based CRM company
AMZN    |  Amazon.com        | This is an ecommerce company

Output:
Company        |  Symbol   |  Prev Close    |   Description
Amazon.com     |  AMZN     |  $349.64       | This is an ecommerce company 
Salesforce.com |  CRM      |  $55.24        | This is a cloud based CRM company

我认为MySQL不支持LAG函数，所以你需要做一个子查询来获取每个Symbol的最后日期，然后对该结果进行INNER join。像这样的代码应该可以工作:

 $query = "
    SELECT l.Company, l.Symbol, l.Prev_Close, r2.Description
    FROM Daily_info as l
    INNER JOIN
    (SELECT Symbol, MAX(Prev_close) as last_date FROM Daily_info GROUP BY Symbol) AS r
    ON (l.Symbol=r.Symbol AND l.Prev_close=r.last_date)
    INNER JOIN Stock as r2
    ON (l.Symbol=r2.Symbol)
    ";