通过使用mysql的组总和

  • Z时代
  • 2024-01-10
  • 分类：问答

我有三个表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

回答：

试试这个，它会给确切的结果是你想要的，我只是创建一个驱动器表然后总和

SELECT name 
    , house_number 
    , address 
    , SUM(gtax) as gtax 
    , SUM(htax) as htax 
    , SUM(LTAX) LTAX 
FROM (SELECT c.consumer_name as Name 
      , c.house_number 
      , c.address,h.subincome 
      , h.financial_year 
      , CASE WHEN h.subincome = 'Garbage tax' THEN f.garbage_tax - sum(h.rupees)END as gtax 
      , CASE WHEN h.subincome = 'House tax' THEN f.house_tax - sum(h.rupees) END as htax 
      , CASE WHEN h.subincome = 'Light tax' THEN f.light_tax - sum(h.rupees) 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 h.subincome, h.financial_year) as main 
GROUP BY house_number

那么结果是：

以上是 通过使用mysql的组总和 的全部内容， 来源链接： utcz.com/qa/262275.html

回到顶部