mysql - 从我的数据库中获取营业额、收入、总费用并按年分组
问题描述
我有 4 张桌子:
Products - to record products
spending - to record expenses
directv - to record subscription payments to TV channels
sales - to record sales
产品有 4 列:idProd
是主键
+--------+-----------+--------------+-------------+
| idProd | nameProd | sellingPrice | buyingPrice |
+--------+-----------+--------------+-------------+
| 1 | Iphone 8 | 500 | 400 |
| 2 | Samsung 2 | 600 | 400 |
+--------+-----------+--------------+-------------+
支出 - 3 列,idSpd
是主键
+-------+--------+------------+
| idSpd | amount | dateSpd |
+-------+--------+------------+
| 1 | 1000 | 2018-11-01 |
| 2 | 1000 | 2018-11-01 |
| 3 | 1000 | 2018-10-01 |
| 4 | 4000 | 2018-10-01 |
+-------+--------+------------+
销售 - 5 列idSale
作为主键并将idProd
其链接到产品表
+--------+--------+--------------+----------+------------+
| idSale | idProd | sellingPrice | quantity | dateSale |
+--------+--------+--------------+----------+------------+
| 1 | 1 | 700 | 2 | 2018-11-01 |
| 2 | 1 | 700 | 5 | 2018-11-15 |
| 3 | 2 | 800 | 2 | 2018-11-16 |
+--------+--------+--------------+----------+------------+
和directv:
+-------+-----------------+-------+------------+
| idDtv | brand | price | dateDtv |
+-------+-----------------+-------+------------+
| 1 | channel decoder | 150 | 2018-11-09 |
+-------+-----------------+-------+------------+
我希望得到例如:
income|gain of product | turnover | Spending | DirecTv | month | year
1000 | 400 | 5500 | 3000 | 50 | 10 |2018
500 | 200 | 1000 | 2000 | 0 | 11 |2018
我的查询:
--for directv
select sum(dv.price) , month(dv.dateDtv), year(dv.dateDtv) from directv dv GROUP by year(dv.dateDtv) , month(dv.dateDtv)
--for turnover
select sum(sl.quantity*sl.sellingPrice) , month(sl.dateSale) , year(sl.dateSale) from sales sl GROUP by year(sl.dateSale) , month(sl.dateSale)
--for spending
select sum(spd.amount) , month(spd.dateSpd) , year(spd.dateSpd) from spending spd GROUP by year(spd.dateSpd) , month(spd.dateSpd)
--for gain of product
SELECT sum(s.quantity*(s.sellingPrice-p.buyingPrice)) from sales s JOIN products p on s.idProd = p.idProd GROUP by year(s.dateSale) , month(s.dateSale)
收入 = 产品收益 + 直接价值 - 支出
我想加入所有这些查询并计算income
. 我昨天和@Gordon Linoff 一起尝试不使用餐桌产品,这没关系,但我今天无法与我的新餐桌产品结合使用。
解决方案
将所有已经起作用的查询(并且具有月/年的共同主题)结合在一起:
SELECT *
FROM
(select sum(dv.price) directv, month(dv.dateDtv) as mo, year(dv.dateDtv) as yr from directv dv GROUP by year(dv.dateDtv) , month(dv.dateDtv)) as directv
INNER JOIN
(select sum(sl.quantity*sl.sellingPrice) as turnover, month(sl.dateSale) as mo, year(sl.dateSale) as yr from sales sl GROUP by year(sl.dateSale) , month(sl.dateSale)) as turnover
ON directv.mo = turnover.mo and directv.yr = turnover.yr
INNER JOIN
(select sum(spd.amount) as spending, month(spd.dateSpd) as mo, year(spd.dateSpd) as yr from spending spd GROUP by year(spd.dateSpd) , month(spd.dateSpd)) as spending
ON directv.mo = spending.mo and directv.yr = spending.yr
INNER JOIN
(SELECT sum(s.quantity*(s.sellingPrice-p.buyingPrice)) as gain, year(s.dateSale) as yr, month(s.dateSale) as mo from sales s JOIN products p on s.idProd = p.idProd GROUP by year(s.dateSale) , month(s.dateSale) ) as gain
ON directv.mo = gain.mo and directv.yr = gain.yr
这是写相同内容的另一种方式:
WITH
directv as (select sum(dv.price) directv, month(dv.dateDtv) as mo, year(dv.dateDtv) as yr from directv dv GROUP by year(dv.dateDtv) , month(dv.dateDtv)),
turnover as (select sum(sl.quantity*sl.sellingPrice) as turnover, month(sl.dateSale) as mo, year(sl.dateSale) as yr from sales sl GROUP by year(sl.dateSale), month(sl.dateSale)),
spending as (select sum(spd.amount) as spending, month(spd.dateSpd) as mo, year(spd.dateSpd) as yr from spending spd GROUP by year(spd.dateSpd) , month(spd.dateSpd)),
gain as (SELECT sum(s.quantity*(s.sellingPrice-p.buyingPrice)) as gain, year(s.dateSale) as yr, month(s.dateSale) as mo from sales s JOIN products p on s.idProd = p.idProd GROUP by year(s.dateSale) , month(s.dateSale) )
SELECT * FROM
directv
INNER JOIN turnover ON directv.mo = turnover.mo and directv.yr = turnover.yr
INNER JOIN spending ON directv.mo = spending.mo and directv.yr = spending.yr
INNER JOIN ON directv.mo = gain.mo and directv.yr = gain.yr
使用您喜欢的外观;)
现在,这些查询只是 SELECT * 来自所有相关子查询的数据 - 您仍然需要做一些工作来 a) 删除mo
和yr
b) 对任何列进行数学运算,例如,select gain.gain + directv.directv - spend.spend as income..
但这应该很简单一旦您可以在同一行上看到所有数据就足够了
重要提示:如果这些查询中的任何一个在一个月内都没有返回任何结果,请不要使用内部联接,因为它会导致所有月份的统计信息消失。选择绝对永远不会缺少月份的查询,然后将其他子查询 LEFT JOIN 改为它
如果永远不会有这样的一个月,即这些查询中的任何一个都有可能有一天在一个月内没有返回任何结果,那么通过算法建立另一个月份列表并将所有子查询留在上面。这是一个这样的问题,涉及如何做到这一点:
推荐阅读
- android - Android中的缩放动画会降低图像质量
- woocommerce - Woocommerce wc_get_products() $args - 如何显示指定“所有”标签的帖子?
- linq - 如何通过 API 在中间表中插入多个值
- amazon-web-services - 使用 AWS CLI 为其他类型(明文)创建 AWS SecretsManager
- wordpress - 在 wordpress 的 lerandash 问题页面中添加自定义文件
- python - 收到错误:ValueError:要解包的值太多(预期为 2)
- webhooks - 已创建/更新地址的 BigCommerce API Webhook 事件有效负载缺少 customer_id
- apache-kafka - 卡夫卡消费者从一开始就没有消费
- android - 广播接收器在三星设备中抛出错误接收意图
- python - Multiprocessing.Process 没有启动而没有任何错误