如何连接两个表，然后显示一个表从另一个表收集的每个数据的总和值

Question

首先，抱歉，如果我的问题标题听起来很愚蠢......我有下表。第一个表包含我的费用类型的名称，第二个表包含这些费用的金额和日期信息。如果您注意到下面的第二个表，有一个名为“ e_id ”的字段，它是第一个表中我的费用的相应 id 值。

第一个表名称：expense_type

  id     expense_name 

  1        Insurance
  2        Interest Payment
  3        Rent
  4        Electricity

 ... and goes on like this (unlimited perhaps :))

第二个表名称：expense_details

 id       e_id    amount       date

  1        3      1000        2011-12-11 
  2        1       500        2011-12-19 
  3        4        10        2011-11-21
  4        3      1000        2012-01-12

... and goes on like this )

现在我的计划是从这两个表中获取所有值并生成如下结果。但由于第一个表有未知数量的信息（对我来说），我不知道如何编写查询（php 和 mysql）来生成这样的结果。

请您帮助我如何实现这一目标？

提前致谢:)

PS 仅供您参考，我正在使用 Codeigniter。

                                 **For the Month: January 2011**


           Insurance    :  1000

           Rent         :  3453

           Electricity  :   546

      Interest Payment  :   546    
  ---------------------------------
      **Total Expenses**: 938949 /// this value is just for an example

Answer 1

您可以

SELECT expense_name, SUM(amount) as monthly_total
FROM expense_details, expense_type
WHERE expense_type.id=expense_details.e_id
  AND `date` >= '2011-01-01' 
  AND `date` <  '2011-02-01'  
GROUP BY expense_details.e_id;

根据需要使用“然后”格式。注意 date 是用反引号括起来的，因为它也是 MySQL 中的一个函数。

要更改月份，您只需执行 `date >= 'yyyy-mm-01' AND date < 'yyyy-(mm+1)-01'（除非mm` 是 12 月...）。

您也可以选择

WHERE MONTH(`date`)=1 AND YEAR(`date`)=2011, 

或

WHERE `date`>='yyyy-mm-dd' AND `date`<DATE_ADD('yyyy-mm-dd',INTERVAL 1 MONTH)

2011 年 1 月（看看 这里），但我认为如果没有日期索引，速度会慢一些（当然，选择哪种方法取决于您要筛选的预期记录数量、数据库的设置方式，等等等等——但为什么不尝试几个版本，看看哪个适合您呢？）

Answer 2

您可以在控制器或模型中执行此操作：

$sql = "SELECT a.*, b.expense_name FROM expense_details AS a, expense_name AS b WHERE a.e_id = b.id ORDER BY a.id ASC";
$data = $this->db->query($sql)->result_array();
$totals = array();

echo '**For the Month: January 2011**';
echo "\n\n";

foreach ($data as $item){
  $totals[$item['expense_name']] = isset($totals[$item['expense_name']]) ? $totals[$item['expense_name']] + $item['amount'] : $item['amount']: 
}

foreach ($totals as $name => $val){
  echo $name.': '.$val;
  echo "\n\n";
}

不是最优雅或最稳定的方法，但我希望这有助于您了解需要做什么。我想您可能还想按月细分总数。

Answer 3

SELECT ed.e_id, DATE_FORMAT(ed.date, '%M %Y') AS `month`, et.expense_name, SUM(ed.amount) AS `amount`
FROM expense_details ed
  INNER JOIN expense_types et ON (et.id = ed.e_id)
WHERE (ed.date BETWEEN '2011-11-01' AND DATE_ADD('2011-11-01', INTERVAL 3 MONTH))
GROUP BY `month`, ed.e_id
WITH ROLLUP

当然，您可以设置任何日期范围，甚至可能是一个月，或者根本没有日期条件，以获得完整的报告。
它将每月汇总每种类型的费用（您将填写 month 且 ed.e_id = NULL - 这样您就知道这是该月的摘要），然后全职（月 和 ed.e_id = NULL）。

输出将如下所示：

e_id    month           expense_name    amount  
1       December 2011   Insurance       500
3       December 2011   Rent            1000
NULL    December 2011   Rent            1500
3       January 2012    Rent            1000
NULL    January 2012    Rent            1000
4       November 2011   Electricity     10
NULL    November 2011   Electricity     10
NULL    NULL            Electricity     2510

当然，当您看到 e_id = NULL 时，您必须忽略粘性 expense_name

有些人可能建议您只获取结果，然后使用 PHP 将它们求和，但它对于大型报告来说，这将是一个性能消耗。让 SQL 做它最擅长的事情（挖掘数据计数、求和等），让 PHP 做它最擅长的事情（渲染 HTML）。

当然，如果您正确设置了外键（这就是为什么 JOIN 比仅从两个表中选择并在 WHERE 子句中添加 id=e_id 更快）并且具有 < code>date 字段已索引。