php - 去年这个日期的 MySQL 语句

Question

我对这些 php mysql 语句感到困惑。

我需要从表中的同一列(金额)获得四个不同的结果。

这些函数计算以下各项的(金额)总和:
年初至今
去年给予时间
去年捐赠总额
过去 30 天内的捐赠

它们似乎都是同一事物的变体。我不知道如何让 LastYearNow 显示正确的结果。如何显示从 2016 年初到 2016 年今天的结果？

public function getSum() {
        $query = $this->db->query("SELECT SUM(amount) as amount_sum FROM " . DB_PREFIX . "donate Where date_added >= (CURDATE() - INTERVAL 12 MONTH) ");           
       return $query->row; 
}
    public function lastThirty() {
        $query = $this->db->query("SELECT SUM(amount) as amount_sum FROM " . DB_PREFIX . "donate Where date_added >= (CURDATE() - INTERVAL 1 MONTH)" );            
       return $query->row; 
}
public function lastYearNow() {
        $query = $this->db->query("SELECT SUM(amount) as amount_sum FROM " . DB_PREFIX . "donate Where date_added >= (CURDATE() - INTERVAL 12 MONTH) ");           
       return $query->row; 
}
    public function lastYearTotal() {
        $query = $this->db->query("SELECT SUM(amount) as amount_sum FROM " . DB_PREFIX . "donate Where YEAR(date_added) = YEAR(DATE_SUB(CURDATE(), INTERVAL 1 YEAR))" );           
       return $query->row; 
}

Answer 1

lastYearNow 的查询当前正在计算过去 12 个月的总计。相反，如果您想要计算去年从今年 1 号到当前日期的总计，则需要使用 BETWEEN 子句:

SELECT SUM(amount) as amount_sum 
FROM donate 
WHERE date_added BETWEEN 
MAKEDATE(YEAR(DATE_SUB(CURDATE(), INTERVAL 1 YEAR)), 1, 1)
AND
(CURDATE() - INTERVAL 12 MONTH)

警告:我面前没有安装 MySQL，因此语法可能不太正确。