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

First of all Sorry if my question title sounds stupid.... I have the following tables. The first table contains the name of my expenses type and the second table contains the information of amount and date of those expenses. If you notice the second table below there is a field called " e_id " which is the corresponding id value of my expenses from the first table.

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

First Table Name: expense_type

第一个表名:expense_type

  id     expense_name 

  1        Insurance
  2        Interest Payment
  3        Rent
  4        Electricity

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

Second Table Name: expense_details

第二个表名: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 )

Now my plan is to get all the values from these two tables and generate a result like following . But since the first table has unknown number of information (to me) I don't know how to write the query(both php and mysql) to generate such result.

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

Woud you please kindly help me on how to achieve this?

你能帮我完成这个吗?

Thanks in Advance :)

提前谢谢:)

P.S just for you information I am using Codeigniter.

P。这只是为了告诉你我在用可待因点火器。

                                 **For the Month: January 2011**


           Insurance    :  1000

           Rent         :  3453

           Electricity  :   546

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

3 个解决方案

#1

You can do this in a controller, or model:

你可以在控制器或模型中这样做:

$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";
}

Not the most elegant, or stable method but I hope this helps give you the idea of what you need to do. You'll also probably want to break totals up by month I suppose.

虽然不是最优雅、最稳定的方法，但我希望这能帮助您了解需要做什么。我想，你也可能想要在一个月前把总数打破。

#2

You can use the

您可以使用

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;

And then format as you wish. Note date is in backticks since it's also a function in MySQL.

然后按你的意愿格式化。注意日期在回勾中，因为它也是MySQL中的函数。

To change the month you just do `date >= 'yyyy-mm-01' AND date < 'yyyy-(mm+1)-01'(unlessmm` is December...).

要更改月份，只需执行“date >=”yyyy-mm-01”和“date < yyyyy -(mm+1)-01”(unlessmm ' is December…)。

You could alternatively do

你可以另外做

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

或

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

for Jan 2011 (have a look here), but I reckon it's a bit slower if you don't have date indexed (of course which method you pick depends on the expected number of records you're sifting through, how the database is set up, etc etc -- but why not give a few versions a go and see what suits you?)

2011年1月(看一看),但我认为这是有点慢如果你没有日期索引(当然你选择哪个方法取决于预期数量的记录你筛选,数据库是如何设置的,等等,但为什么不给几个版本去看什么适合你?)

#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

You can set any date range of course, maybe even one month, or no date condition at all, to get full report. It will sum expenses for each type with summary every month (you will have month filled and ed.e_id = NULL - that way you know it's summury for the month) and then for full time (both month and ed.e_id = NULL).

当然，你可以设置任何日期范围，甚至一个月，或者根本没有日期条件，以获得完整的报告。它将每月对每种类型的开销进行汇总(您将每月填充并使用.e_id = NULL -这样您就知道这是一个月的summury)，然后是全职(包括月和.e_id = NULL)。

Output will be like this:

输出如下:

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

Of course you must ignore sticky expense_name when you see e_id = NULL

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

Some may suggest you just get results and then SUM them up with PHP, but it'll be a perfomance drain for large reports. Let SQL do what it does best (dig through data counting, summing, whatsoevering), and let PHP do what it does best (render HTML).

有些人可能会建议您只获取结果，然后使用PHP进行总结，但是对于大型报告来说，这将是一个性能消耗。让SQL做它最擅长的事情(挖掘数据计数、求和、什么)，让PHP做它最擅长的事情(呈现HTML)。

Of course SQl will reward you with even better performance if you have foreign keys set up correctly (that's why JOIN would work faster than just selecting from two tables and adding id=e_id in WHERE clause) and have date field indexed.

当然，如果您设置了正确的外键(这就是为什么JOIN要比只从两个表中选择并在WHERE子句中添加id=e_id更快)并具有日期字段索引，那么SQl将会给您带来更好的性能。

#1