mysql - sql聚合函数上的多个条件

Question

如何在 sql 聚合函数上按多个条件进行分组。例如，我有两个表 Sales 和 Accounts

销售

ID  |  identifier_one  |  identifier_two  | quantity | date
1   |       1          |       A1         |    1     |  2017-01-01
2   |       1          |       B1         |    1     |  2017-01-01
3   |       1          |       A1         |    1     |  2017-01-02

查询应按如下方式填充帐户表

帐户

ID  |  identifier_one  |  identifier_two  |  current_month  |  current_year
1   |       1          |       A1         |    2            |     2
2   |       1          |       B1         |    1            |     1 

我有此查询，但它只接受帐户 identifier_one，因此在此示例中，帐户中的两个条目都将具有 3 和 3

$sales = DB::select('SELECT
                            identifier_one,
                            SUM(IF(YEAR(`date`) = YEAR(CURDATE()), quantity, 0)) AS current_year,
                            SUM(IF(YEAR(`date`) = YEAR(CURDATE()) AND MONTH(`date`) = MONTH(CURDATE() ), quantity, 0)) AS current_month,
                            SUM(IF(YEAR(`date`) = YEAR(CURDATE()) - 1, quantity, 0)) AS last_year,
                            SUM(IF(YEAR(`date`) = YEAR(CURDATE()) AND MONTH(`date`) = MONTH(CURDATE()) - 1, quantity, 0)) AS last_month
                        FROM sales
                        GROUP BY identifier_one'); 

foreach ($sales as $sale) {

                    DB::table('accounts')
                        ->where([
                            ['identifier_one', '=', $sale->identifier_one]
                        ])
                        ->update([
                            'current_year'  => $sale->current_year,
                            'current_month' => $sale->current_month,
                            'last_month'    => $sale->last_month,
                            'last_year'     => $sale->last_year
                        ]);


                }

Answer 1

似乎您只需要添加 , 和字段名称到分组依据。您可能还想将identifier_two 添加到选择中，以防需要更新where 子句以考虑这两个标识符。 (有可能)

SELECT identifier_one
     , identifier_two  --not required but may be useful as you seem to filter on identifier_one later; maybe you also need the 2nd one...
     , SUM(IF(YEAR(`date`) = YEAR(CURDATE()), quantity, 0)) AS current_year,
     , SUM(IF(YEAR(`date`) = YEAR(CURDATE()) AND MONTH(`date`) = MONTH(CURDATE() ), quantity, 0)) AS current_month
     , SUM(IF(YEAR(`date`) = YEAR(CURDATE()) - 1, quantity, 0)) AS last_year
     , SUM(IF(YEAR(`date`) = YEAR(CURDATE()) AND MONTH(`date`) = MONTH(CURDATE()) - 1, quantity, 0)) AS last_month
FROM sales
GROUP BY identifier_one, identifier_two --this is what you're missing