复杂的多表mysql查询

Question

我正在寻找一些mysql查询的帮助，以便为我的国际电话计费系统从几个不同的表中返回以下数据

在数据库中，我有一个表，其中包含每个呼叫记录，计费费率&我希望在发票上显示的最后一组。

我要做的是迭代通过表tbl_international_rates，对于每一行，将tbl_international_records中的CDR_ChargedDurationMinutes相加，其中CDR_BillerCode=international_rate_prefix，然后* international_rate_price，返回计费分钟的总量和前缀总数或类似于下面的内容。

|--------------------------|--------------------------|--------------------------|
|        Prefix            |      total minutes       | total amount         |
|--------------------------|--------------------------|--------------------------|
|         93               |          14.3            |         $137.28          |
|--------------------------|--------------------------|--------------------------|
|         937              |          31.2            |         $418.08          |
|--------------------------|--------------------------|--------------------------|

一旦完成，我想将它们按父组分组在一起，从mysql返回一些类似于下表的内容。

|--------------------------|--------------------------|--------------------------|
|  CountryDescription      |   CountryTotalMinutes    |      CountryTotalCosts   |
|--------------------------|--------------------------|--------------------------|
|        Afghanistan       |         45.5             |           555.36         |
|--------------------------|--------------------------|--------------------------|

这个是可能的吗？

这是数据库中表的结构

tbl_international_groups

|--------------------------|--------------------------------|
| international_group_idno | international_group_description|
|--------------------------|--------------------------------|
|            1             |         Afghanistan            |
|--------------------------|--------------------------------|
|            2             |         USA                    |
|--------------------------|--------------------------------|

tbl_international_rates

|--------------------------|--------------------------|--------------------------|--------------------------|
| international_rate_idno  | international_rate_prefix| international_rate_group | international_rate_price |
|--------------------------|--------------------------|--------------------------|--------------------------|
|            1             |         93               |          1               |          9.60            |
|--------------------------|--------------------------|--------------------------|--------------------------|
|            1             |         937              |          1               |          13.40           |
|--------------------------|--------------------------|--------------------------|--------------------------|

tbl_international_records

|--------------------------|--------------------------|--------------------------|
|          CDR_ID          |CDR_ChargedDurationMinutes|      CDR_BillerCode      |
|--------------------------|--------------------------|--------------------------|
|            1             |         4.7              |           93             |
|--------------------------|--------------------------|--------------------------|
|            2             |         5.6              |           97             |
|--------------------------|--------------------------|--------------------------|
|            3             |         9.6              |           93             |
|--------------------------|--------------------------|--------------------------|
|            4             |         25.6             |           97             |
|--------------------------|--------------------------|--------------------------|

*编辑*

使用下面的查询，我可以返回如下所示的表，但是如何在tbl_international_groups中按父级对它们进行分组

|--------------------------|--------------------------|--------------------------|
|        Prefix            |      total minutes       | total amount         |
|--------------------------|--------------------------|--------------------------|
|         93               |          14.3            |         $137.28          |
|--------------------------|--------------------------|--------------------------|
|         937              |          31.2            |         $418.08          |
|--------------------------|--------------------------|--------------------------|

查询

select rates.international_rate_prefix as Prefix
 , sum( records.CDR_ChargedDurationMinutes ) as total_minutes
 , sum( records.CDR_ChargedDurationMinutes ) * 
rates.international_rate_retail as total_amount
from tbl_international_rates as rates
join tbl_international_records as records
  on rates.international_rate_prefix = records.CDR_BillerCode
group by rates.international_rate_prefix

Answer 1

修好了。这是我的解决方案：http://sqlfiddle.com/#!9/8eb3b1/23

select international_group_description as CountryDescription,
 sum(total_minutes) as CountryTotalMinutes,
 sum(total_amount) as CountryTotalCosts
from (
  select rates.international_rate_group, 
  rates.international_rate_prefix as Prefix
  , round(sum( records.CDR_ChargedDurationMinutes ), 2) as total_minutes
  , round(sum( records.CDR_ChargedDurationMinutes ) * 
   rates.international_rate_price, 2) as total_amount
  from tbl_international_rates as rates
  join tbl_international_records as records
    on rates.international_rate_prefix = records.CDR_BillerCode
  join tbl_international_groups groups
    on rates.international_rate_group = 
  groups.international_group_idno
  group by rates.international_rate_prefix
 ) as subselect
join tbl_international_groups groups
on groups.international_group_idno = subselect.international_rate_group