问题描述
所以我有3个表:
支出-记录支出
Directv -记录电视频道的订阅付款
销售-记录销售
而且我必须计算每个月的营业额,总销售额,总费用和Directv
So I have 3 tables called :
Spending - to record expenses
Directv - to record subscription payments to TV channels
Sales - to record sales
and I have to calculate the turnover ,total amount of sales , total expenses and Directv for each month
Spending
id|Amount|dateSpd
1 |2000 |2018-10-05
3 |3000 |2018-11-06
Directv
id|Amount|dateTv
1 |50 |2018-10-05
Sales
id|customer|quantity|price|dateSales
1 |Marc |2 |500 |2018-10-05
2 |Kevin |3 |1500 |2018-10-05
5 |Angel |2 |500 |2018-11-07
我想举个例子
turnover | Spending | sales | DirecTv | month | year
5500 | 3000 | 2000 | 50 | 10 |2018
1000 | 2000 | 500 | 0 | 11 |2018
我有一些问题要解决,我的查询:
i have some problems to get it ,my queries :
--to get total amount of sales
select sum(sl.price) , month(sl.date) , year(sl.date) from sales sl GROUP by year(sl.date) , month(sl.date)
--for directtv
select sum(dv.amount) , month(dv.date) , year(dv.date) from directtv dv GROUP by year(dv.date) , month(dv.date)
--for turnover
SELECT sum(sl.quantity*sl.price) , month(sl.date) FROM sales sl GROUP by year(sl.date), month(sl.date)
但是如何将所有带有联接的SQL查询按日期进行分组
有人可以帮助我或给我任何提示吗?预先谢谢你
but how how to group by date all SQL queries with joins
someone could help me or give me any hints ? thank you in advance
推荐答案
您可以使用union all
合并表中的数据,然后进行汇总.
You can combine the data from the tables using union all
and then aggregate.
我怀疑您想要这样的东西:
I suspect you want something like this:
select year(dte), month(dte),
sum(spending) as spending, sum(directtv) as directtv,
sum(price*quantity) as turnover
from ((select datesp as dte, amount as spending, 0 as directtv, 0 as price, 0 as quantity
from spending
) union all
(select datetv as dte, 0 as spending, amount as directtv, 0 as price, 0 as quantity
from directtv
) union all
(select datesales as dte, 0 as spending, 0 as directtv, price, quantity
from sales
)
) x
group by year(dte), month(dte) ;
这并不是您查询中的确切内容,但考虑到您提供的数据,这是有道理的.
This is not exactly what is in your queries, but it makes sense given the data you have provided.
这篇关于按日期月份多个表分组的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!