• 如果您觉得本站非常有看点,那么赶紧使用Ctrl+D 收藏吧

通过使用mysql的组总和

mysql 来源:Kannan K 5次浏览

我有三个表financial_year,house_details,consumer_details。我需要按年份和子收入获得每个税收组的总和。我的表,查询是在这个环节:sqlfiddle通过使用mysql的组总和

获得结果:

Name house_number address subincome financial_year gtax htax LTAX 
-------------------------------------------------------------------------- 
Bala 22   Mumbai Garbage tax 2015-2016  200 NULL NULL 
Bala 22   Mumbai Garbage tax 2016-2017  250 NULL NULL 
Bala 22   Mumbai House tax 2015-2016  NULL 0  NULL 
Bala 22   Mumbai House tax 2016-2017  NULL 145 NULL 
Bala 22   Mumbai Light tax 2015-2016  NULL NULL 510 
Bala 22   Mumbai Light tax 2016-2017  NULL NULL 200 

期待结果:

Name house_number address gtax htax LTAX 
-------------------------------------------------------- 
Bala 22    Mumbai  450  145  710 


===========解决方案如下:

您正在寻找条件汇总备注此解决方案仅适用于每个财政年度的房屋详细信息中的每个子分类(税类型)的条目。

SELECT c.consumer_name as Name, c.house_number, c.address, 
sum(CASE WHEN h.subincome = 'Garbage tax' THEN f.garbage_tax else 0 end) - 
sum(CASE WHEN h.subincome = 'Garbage tax' THEN h.rupees else 0 END) as gtax, 
sum(CASE WHEN h.subincome = 'House tax' THEN f.house_tax else 0 end) - 
sum(CASE WHEN h.subincome = 'House tax' THEN h.rupees else 0 END) as htax, 
sum(CASE WHEN h.subincome = 'Light tax' THEN f.light_tax else 0 end) - 
sum(CASE WHEN h.subincome = 'Light tax' THEN h.rupees else 0 END) as LTAX 
from house_details h 
INNER JOIN financial_year f ON h.financial_year = f.year AND h.house_id = f.house_number 
INNER JOIN consumer_details c ON h.house_id = c.house_number AND h.financial_year != '2017-2018' 
group by c.consumer_name , c.house_number, c.address 

结果

+------+--------------+---------+------+------+------+ 
| Name | house_number | address | gtax | htax | LTAX | 
+------+--------------+---------+------+------+------+ 
| Bala | 22   | Mumbai | 450 | 145 | 710 | 
+------+--------------+---------+------+------+------+ 
1 row in set (0.03 sec) 

如果不能保证会有在每一个财政年度,则该解决方案已经从应付税款表驱动,每subincome一个条目(财政年度)这在我看来是设计得很差,不灵活并且强制次优解决方案

select c.consumer_name as Name, s.house_number, c.address, 
     sum(case when subincome = 'garbage tax' then taxdue else 0 end) - sum(case when subincome = 'garbage tax' then taxpaid else 0 end) as gtax, 
     sum(case when subincome = 'house tax' then taxdue else 0 end) - sum(case when subincome = 'house tax' then taxpaid else 0 end) as htax, 
     sum(case when subincome = 'light tax' then taxdue else 0 end) - sum(case when subincome = 'light tax' then taxpaid else 0 end) as ltax 
from 
(
SELECT F.`house_number`, F.`year`, F.`house_tax` taxdue, F.`createdAt`, F.`updatedAt`,ifnull(h.subincome,'house_tax') subincome,ifnull(H.RUPEES,0) taxpaid 
FROM FINANCIAL_YEARS F 
LEFT JOIN house_details H ON H.HOUSE_ID = F.HOUSE_NUMBER AND H.SUBINCOME = 'house tax' and f.year = h.financial_year 
#where f.house_number = 22 
union all 
SELECT F.`house_number`, F.`year`, F.`light_tax`, F.`createdAt`, F.`updatedAt`,ifnull(h.subincome,'light tax'),ifnull(H.RUPEES,0) 
FROM FINANCIAL_YEARS F 
LEFT JOIN house_details H ON H.HOUSE_ID = F.HOUSE_NUMBER AND H.SUBINCOME = 'light tax' and f.year = h.financial_year 
#where f.house_number = 2 
union all 
SELECT F.`house_number`, F.`year`, F.`garbage_tax`, F.`createdAt`, F.`updatedAt`,ifnull(h.subincome,'garbage tax'),ifnull(H.RUPEES,0) 
FROM FINANCIAL_YEARS F 
LEFT JOIN house_details H ON H.HOUSE_ID = F.HOUSE_NUMBER AND H.SUBINCOME = 'garbage tax' and f.year = h.financial_year 
#where f.house_number = 2 
) s 
join consumer_details c on s.house_number = c.house_number 
where s.year <> '2017-2018' 
group by c.consumer_name , s.house_number, c.address 

版权声明:本文转自网络文章,转载此文章仅为分享知识,如有侵权,请联系管理员进行删除。
喜欢 (0)