复杂的MySQL与GROUP BY联接 [英] Complex MySQL JOIN with GROUP BY
问题描述
我想返回一个类似如下的报告：
unit |天|停留|收入|收费|管理费| bal
1001 20 6 775.00 1500.00 310.00 0.00
1002 40 14 5000.00 200.00 2100.00 2700.00
1003 50 20 6000.00 10.00 2500.00 3490.00
我有这样的表格：
单位
id |名称| mgmt_fee
1001花卉30
1002 charlie 25
1003 deniro 30
1004 archie 20
1005 lilly 25
The mgmt费用用作百分比（％）
预订
id | unit | arrival | departure | total_price
10111 1001 2014-02-09 2014-02-13 400.00
10012 1001 2014-03-10 2014-03-15 300.00
10145 1002 2014-04-01 2014-04-05 600.00
10043 1003 2014-05-30 2014-06-03 350.00
注意：这些不是实际的数据这是一种表示我的领域和他们的数据可能看起来像什么。
费用
id |单位|数额| charged_dtm
1 1001 40.00 2014-03-24 19:04:31
2 1001 30.00 2014-03-25 20:51:08
3 1002 100.00 2014-04-05 12:52 ：25
**某些月份某些单位可能没有收费。
我试过以下查询：
SELECT u.name AS单位，
（r.departure-r.arrival）AS天，
COUNT（r.id）AS留，
SUM（r.total_price）AS收入，
SUM （c.amount）AS费用
（SUM（r.total_price）*（。01 * u.mgmt_fee））AS管理，
（（SUM（r.total_price） - （SUM（r。总计价格）*
（.01 * u.mgmt_fee））） - SUM（c.amount））AS bal
FROM保留r
JOIN单位u ON r.unit = u.id
JOIN收费c ON u.id = c.unit
GROUP BY u.id
<它会返回一个单位和所有其他数据汇总在一起。这真的很奇怪。
现在，我尝试删除所有内容并添加一次，以找到罪魁祸首。
我发现直到这个查询，我很好。
选择CONCAT（u.unit_name，'，''，u.unit_nickname，'''）AS单位，
SUM r.departure-r.arrival）AS days，
COUNT（r.id）AS保留，
SUM（r.total_price）AS收入
FROM保留r
JOIN单位u ON r.unit = u.id
GROUP BY u.id
收费是查询误入歧途时。
关于如何完成我想要的结果的任何想法？ 我认为你不能一气呵成，因为你想同时对不同表格中的项目进行分组/汇总。试试这个：
$ p $选择单位，名称，住宿，天数，total_price，收费，mgmt_fee，
（total_price *。 01 * mgmt_fee）AS管理，
（total_price-（total_price * .01 * mgmt_fee）-charges）AS bal
from（
select
x.unit，
名称，
计数（*）作为停留，
作为天数的总和（天数），
总计（total_price）作为total_price，
费用，
mgmt_fee
从
（选择
单位，
datediff（出发，到达）为天数，
total_price
从
预订
）为x
join（
select unit，sum（amount）as charges
from charge
by unit
）as y
on x.unit = y.unit
作为u
在u.id = x.unit
中加入单位
）as inner_result
不是很整齐或优雅，但我认为它的工作原理。
请注意，如果每单位收费> 1行，则必须小心。
下面是一个演示它正在运行的小提琴： http://sqlfiddle.com/#！ 2 / f05ba / 18
I want to return a report that looks like the following:
unit | days | stays | income | charges | mgmt fee | bal
1001 20 6 775.00 1500.00 310.00 0.00
1002 40 14 5000.00 200.00 2100.00 2700.00
1003 50 20 6000.00 10.00 2500.00 3490.00
So the bal is (income - (charges+mgmt fee).
I have tables that look like this:
Unit
id | name | mgmt_fee
1001 blossom 30
1002 charlie 25
1003 deniro 30
1004 archie 20
1005 lilly 25
The mgmt fee is used as a percentage (%)
Reservations
id | unit | arrival | depart | total_price
10111 1001 2014-02-09 2014-02-13 400.00
10012 1001 2014-03-10 2014-03-15 300.00
10145 1002 2014-04-01 2014-04-05 600.00
10043 1003 2014-05-30 2014-06-03 350.00
NOTE: these are not actual data. It is a representation of my fields and what their data may look like, though.
Charges
id | unit | amount | charged_dtm
1 1001 40.00 2014-03-24 19:04:31
2 1001 30.00 2014-03-25 20:51:08
3 1002 100.00 2014-04-05 12:52:25
**There are cases where there may not be charges for a unit in a given month.
I have tried the following query:
SELECT u.name AS unit,
(r.departure-r.arrival) AS days,
COUNT(r.id) AS stays,
SUM(r.total_price) AS income,
SUM(c.amount) AS charges,
(SUM(r.total_price)*(.01*u.mgmt_fee)) AS management,
((SUM(r.total_price)-(SUM(r.total_price)*
(.01*u.mgmt_fee)))-SUM(c.amount)) AS bal
FROM reservations r
JOIN units u ON r.unit = u.id
JOIN charges c ON u.id = c.unit
GROUP BY u.id
It will return one unit and all the other data is totaled together. It is really odd. Now, I tried removing everything and adding on one a time to find the culprit.
I found that up to this query I am good.
SELECT CONCAT(u.unit_name,', "',u.unit_nickname,'"') AS unit,
SUM(r.departure-r.arrival) AS days,
COUNT(r.id) AS stays,
SUM(r.total_price) AS income
FROM reservations r
JOIN units u ON r.unit = u.id
GROUP BY u.id
Once I add in the charges is when the query goes astray.
Any ideas on how to accomplish my desired result?
I think you can't do it all in one go as you are trying to because you want to group/sum items from different tables at the same time. Try this:
select unit, name, stays, days, total_price, charges, mgmt_fee,
(total_price*.01*mgmt_fee) AS management,
(total_price-(total_price*.01*mgmt_fee)-charges) AS bal
from (
select
x.unit,
name,
count(*) as stays,
sum(days) as days,
sum(total_price) as total_price,
charges,
mgmt_fee
from
(select
unit ,
datediff(depart,arrival) as days,
total_price
from
Reservations
) as x
join (
select unit, sum(amount) as charges
from Charges
group by unit
) as y
on x.unit = y.unit
join Unit as u
on u.id = x.unit
group by unit
) as inner_result
Not very tidy or elegant, but I think it works. Note that you will have to be careful if there can be > 1 row in charges per unit. Here is a fiddle to show it running: http://sqlfiddle.com/#!2/f05ba/18
这篇关于复杂的MySQL与GROUP BY联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
