php - 如果当前年份的数据不可用，则显示上一年

Question

我创建了一个查询来显示当年的数据，并且不存在当年数据的行显示上一年的数据，但现在的问题是，拥有当前年份数据的公司将上一年的数据显示为嗯

数据库company_annual_data

 +--id--+---company_id----+----year-----+----number_of_shares_issued----+
+   1  +     20          +    2011     +              425              +
+   2  +     21          +    2011     +              425              +
+   3  +     22          +    2011     +              425              +
+   4  +     23          +    2011     +              425              +
+   5  +     21          +    2012     +              425              +
+   6  +     20          +    2012     +              425              +
+   7  +     23          +    2012     +              425              +
+   8  +     20          +    2013     +              425              +

公司

+--id--+---company_name----+----category-----+
+  20  +     Company 1     +       2         +
+  21  +     Company 2     +       2         +
+  22  +     Company 3     +       2         +
+  23  +     Company 4     +       5         + 

这是我的查询

$sql            = mysqli_query($connect, "SELECT * FROM companies WHERE category = '36'");
    $total_count    = mysqli_num_rows($sql);
    $companies = array();


    while($row = mysqli_fetch_assoc($sql)) {
            $companies[] = $row['id'];
    }

    $ids = join(', ', $companies);

    $sql1         = mysqli_query($connect, "SELECT year FROM companies_annual_data WHERE company_id IN ($ids) ORDER BY year DESC LIMIT 0, $total_count");
    $total_count1 = mysqli_num_rows($sql1);
    $year     = array();

    $prev_val = null;       
    while($total_year = mysqli_fetch_assoc($sql1)) {
        if($total_year['year'] != $prev_val) {
            $year[] =  $total_year['year'];
        }
        $prev_val = $total_year['year'];    
    }

    $years      = join(', ', $year);
    $sql2       = mysqli_query($connect, "SELECT DISTINCT number_of_shares_issued, year, company_id FROM companies_annual_data WHERE company_id IN ($ids) AND year IN ($years)");
    $prev_val_y = null;
    while($share_num = mysqli_fetch_assoc($sql2)) {
        if($share_num['year'] != date('Y')) {
            echo "Number Of Shares : " . $share_num['number_of_shares_issued'];
            echo " Year : " . $share_num['year'];
            echo " Company Id : " . $share_num['company_id'];
            echo "<br />";
        }
    }

这是输出

Number Of Shares : 284125000 Year : 2015 Company Id : 348
Number Of Shares : 100000000 Year : 2015 Company Id : 351
Number Of Shares : 152098344 Year : 2015 Company Id : 356
Number Of Shares : 152098334 Year : 2016 Company Id : 356
Number Of Shares : 283500000 Year : 2015 Company Id : 366
Number Of Shares : 283500000 Year : 2016 Company Id : 366

这是错误的，输出应该是这样的

Number Of Shares : 284125000 Year : 2015 Company Id : 348
Number Of Shares : 100000000 Year : 2015 Company Id : 351
Number Of Shares : 152098334 Year : 2016 Company Id : 356
Number Of Shares : 283500000 Year : 2016 Company Id : 366

谁能帮我解决这个问题

Answer 1

如果我理解这个问题...

这将返回所提供的每家公司的最近一年的记录数据类别为 36

SELECT CAD.num_of_Shares_Issued, CAD.`Year`, CAD.Company_ID
FROM Companies C
INNER JOIN (SELECT max(`year`) MYear, Company_ID 
            FROM company_Annual_data 
            GROUP BY Company_ID) SUB
 on SUB.CompanyID = C.Company_ID
LEFT JOIN Company_Annual_data CAD
 on C.ID = CAD.Company_ID
and SUB.MYear = CAD.`year`
WHERE  C.Category = 36