使用连接和分组依据从三个表中获取值 [英] Getting values from three tables using join and group by
本文介绍了使用连接和分组依据从三个表中获取值的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
查询：
SELECT c .consumer_name作为名称
，c.house_number
，c.address
，sum（CASE WHEN h.subincome ='垃圾税'THEN f.garbage_tax else 0 end） -
sum（case when h.subincome ='Garbage tax'THEN h.rupe 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 ='轻税'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
预期结果：
sid |名称| house_no |地址|拖欠
--------------------------------------------- ------------------------
house_tax | light_tax | garbage_tax
--------------------------------------------- ------------------------
22 Bala 22孟买145 710 450
25坎南25浦那704 803 630
2 Arul 2德里60 570 590
current |总计
--------------------------------------------- ---------------------------
house_tax | light_tax |垃圾_tax | house_tax | light_tax | garbage_tax
--------------------------------------------- ---------------------------
300 400 500 445 1110 950
550 567 543 1254 1370 1173
700 800 900 760 1370 1490
receipt_no |收集
--------------------------------------------- ---------------------------
house_tax | light_tax |垃圾_tax | house_tax | light_tax | garbage_tax
--------------------------------------------- ---------------------------
312,313,314 309,310,311 288,304,308 400 540 484
407,408,409 404,405,406 401,402,403 921 915 905
410 - - 500 0 0
余额
------------------------------ ------
house_tax | light_tax | garbage_tax
------------------------------------
45 570 466
333 455 268
260 1370 1490
解决方案
通过Stackoverflow用户的支持，我找到了解决方案。
SELECT
sid，
NAME，
housenu AS house_number，
addr AS地址，
htax AS Arrear_housetax，
ltax AS Arrear_lighttax，
gtax AS Arrear_garbagetax，
chousetax AS Current_housetax，
clighttax AS Current_lighttax，
cgarbage AS Current_garbagetax，
htax + chousetax AS totalhousetax，
ltax + clighttax AS totallighttax，
gtax + cgarbage AS totalgarbagetax，
housetax_Ids，
Garbagetax_ids，
lighttax_Ids，
htax_collected AS Collected_housetax，
LTAX_collected AS Collected_lighttax，
gtax_collected AS Collected_garbagetax，
totalhousetax - htax_collected AS balance_housetax，
totallighttax - LTAX_collected AS balance_Lighttax，
totalgarbage - gtax_collected AS balance_garbagetax
FROM
（
SELECT
sid，
NAME，
housenu，
addr，
gtax，
htax，
ltax，
cgarbage，
chousetax，
clighttax，
gtax + cgarbage AS总垃圾，
htax + chousetax AS totalhousetax，
ltax + clighttax AS totallighttax，
Garbagetax_ids，
lighttax_Ids，
housetax_Ids，
gtax_collected，
htax_collected，
LTAX_collected
FROM
（
SELECT
sid，
c.consumer_name AS名称，
s.house_number AS housenu，
c.address AS地址，
SUM（
CASE当子收入='垃圾税'那么taxdue ELSE 0
END
） - SUM（
案例子收入='垃圾税'THEN纳税支付ELSE 0
END
） AS gtax，
SUM（
CASE当子收入='房屋税'THEN taxdue ELSE 0
END
） - SUM（
子收入='房屋税' THEN taxpaid ELSE 0
END
）AS htax，
SUM（
案例子征收='轻税'THEN taxdue ELSE 0
END
） - SUM（
CASE当子收入='轻税'THEN纳税支付ELSE 0
END
）AS ltax，
cantbage，
chousetax，
clighttax，
Garbagetax_ids，
lighttax_Ids，
housetax_Ids，
gtax_collected，
htax_collected，
LTAX_collected
FROM
（
SELECT
.`house_number`，
``year`，
``_house_tax` taxdue，
F.`createdAt`，
``updatedAt`，
IFNULL（h.subincome，'house_tax'）子收入，
IFNULL（H.RUPEES，0）纳税金
FROM
FINANCIAL_YEARS F
加入联盟house_details H ON
H.HOUSE_ID = F.HOUSE_NUMBER AND H.SUBINCOME ='房税'AND f.year = h.financial_year
＃where f.house_number = 22
UNION ALL
SELECT
.`house_number`，
F.`year`，
F.`light_tax`，
F.`createdAt`，
F.`updatedAt`，
IFNULL（h.subincome，'轻税'），
IFNULL （H.RUPEES，0）
FROM
FINANCIAL_YEARS F
LEFT JOIN house_details H ON
H.HOUSE_ID = F.HOUSE_NUMBER AND H.SUBINCOME ='轻税'AND f。 year = h.financial_year
＃where f.house_number = 2
UNION ALL
SELECT
F.`house_number`，
F.`year`，
.`garbage_tax`，
F.`createdAt`，
F.`updatedAt`，
IFNULL（h.subincome，'垃圾税'），
IFNULL（H .RUPEES，0）
FROM
FINANCIAL_YEARS F
LEFT JOIN house_details H ON
H.HOU SE_ID = F.HOUSE_NUMBER AND H.SUBINCOME ='垃圾税'AND f.year = h.financial_year
＃where f.house_number = 2
）s
LEFT JOIN（
SELECT house_number，
IFNULL（garbage_tax，0）AS cgarbage，
IFNULL（light_tax，0）AS clighttax，
IFNULL（house_tax，0）AS chousetax
FROM
financial_years
WHERE
YEAR =2017-2018
）Y
ON
s.house_number = Y.house_number
LEFT JOIN（
SELECT
house_id，
GROUP_CONCAT（garbagetax_Ids）AS Garbagetax_ids，
GROUP_CONCAT（lighttax_Ids）AS Lighttax_ids，
GROUP_CONCAT（housetax_Ids）AS Housetax_ids
FROM
（
SELECT
house_id，
CASE WHEN subincome ='垃圾税'THEN receipt_id
END AS garbagetax_Ids，
CASE WHEN subincome ='小税'THEN receipt_id
END AS lighttax_Ids，
CASE WHEN subincome ='房屋税'THEN receipt_id
END AS housetax_Ids
FROM
house_details
）AS mm
GROUP BY
house_id
）f
ON
s.house_number = f.house_id
LEFT JOIN（
SELECT
house_id，
SUM（gtax）AS gtax_collected，
SUM（htax）AS htax_collected，
SUM（LTAX）AS LTAX_collected
FROM
（
SELECT
house_id，
CASE WHEN h.subincome ='垃圾税'THEN SUM（ h.rupees）ELSE 0
END as gtax，
CASE WHEN h.subincome ='房税'THEN SUM（h.rupees）ELSE 0
END htax，
CASE当h.subincome ='轻税'THEN SUM（h.rupees）ELSE 0
结束为延期
从
房子详情h
GROUP BY
house_id，
子收入
）主要
GROUP BY
house_id
）cf
ON
s.house_number = cf.house_id
LEFT JOIN（
SELECT house_number AS sid，
消费者_name，
地址
FROM
consumer_details
）c
ON
s.house_number = c.sid
WHERE
s。年份<> '2017-2018'
GROUP BY
c.consumer_name，
s.house_number，
c.address
）az
）aa
I'm having three tables, financial_years, house_details, consumer_details. I have added my schema in Sqlfiddle: http://sqlfiddle.com/#!9/dd527d/2 and my expected result is wrapped because of its length given below. The query I have tried is.
Query:
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
Expected Result:
sid | name | house_no | address | arrears
---------------------------------------------------------------------
house_tax | light_tax | garbage_tax
---------------------------------------------------------------------
22 Bala 22 Mumbai 145 710 450
25 Kannan 25 Pune 704 803 630
2 Arul 2 Delhi 60 570 590
current | total
------------------------------------------------------------------------
house_tax | light_tax | garbage_tax | house_tax | light_tax | garbage_tax
------------------------------------------------------------------------
300 400 500 445 1110 950
550 567 543 1254 1370 1173
700 800 900 760 1370 1490
receipt_no | collection
------------------------------------------------------------------------
house_tax | light_tax | garbage_tax | house_tax | light_tax | garbage_tax
------------------------------------------------------------------------
312,313,314 309,310,311 288,304,308 400 540 484
407,408,409 404,405,406 401,402,403 921 915 905
410 - - 500 0 0
balance
------------------------------------
house_tax | light_tax | garbage_tax
------------------------------------
45 570 466
333 455 268
260 1370 1490
解决方案
With the Support of Stackoverflow users I found the solution for my question.
SELECT
sid,
NAME,
housenu AS house_number,
addr AS Address,
htax AS Arrear_housetax,
ltax AS Arrear_lighttax,
gtax AS Arrear_garbagetax,
chousetax AS Current_housetax,
clighttax AS Current_lighttax,
cgarbage AS Current_garbagetax,
htax + chousetax AS totalhousetax,
ltax + clighttax AS totallighttax,
gtax + cgarbage AS totalgarbagetax,
housetax_Ids,
Garbagetax_ids,
lighttax_Ids,
htax_collected AS Collected_housetax,
LTAX_collected AS Collected_lighttax,
gtax_collected AS Collected_garbagetax,
totalhousetax - htax_collected AS balance_housetax,
totallighttax - LTAX_collected AS balance_Lighttax,
totalgarbage - gtax_collected AS balance_garbagetax
FROM
(
SELECT
sid,
NAME,
housenu,
addr,
gtax,
htax,
ltax,
cgarbage,
chousetax,
clighttax,
gtax + cgarbage AS totalgarbage,
htax + chousetax AS totalhousetax,
ltax + clighttax AS totallighttax,
Garbagetax_ids,
lighttax_Ids,
housetax_Ids,
gtax_collected,
htax_collected,
LTAX_collected
FROM
(
SELECT
sid,
c.consumer_name AS NAME,
s.house_number AS housenu,
c.address AS addr,
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,
cgarbage,
chousetax,
clighttax,
Garbagetax_ids,
lighttax_Ids,
housetax_Ids,
gtax_collected,
htax_collected,
LTAX_collected
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
LEFT JOIN(
SELECT house_number,
IFNULL(garbage_tax, 0) AS cgarbage,
IFNULL(light_tax, 0) AS clighttax,
IFNULL(house_tax, 0) AS chousetax
FROM
financial_years
WHERE
YEAR = "2017-2018"
) Y
ON
s.house_number = Y.house_number
LEFT JOIN(
SELECT
house_id,
GROUP_CONCAT(garbagetax_Ids) AS Garbagetax_ids,
GROUP_CONCAT(lighttax_Ids) AS Lighttax_ids,
GROUP_CONCAT(housetax_Ids) AS Housetax_ids
FROM
(
SELECT
house_id,
CASE WHEN subincome = 'Garbage tax' THEN receipt_id
END AS garbagetax_Ids,
CASE WHEN subincome = 'Light tax' THEN receipt_id
END AS lighttax_Ids,
CASE WHEN subincome = 'House tax' THEN receipt_id
END AS housetax_Ids
FROM
house_details
) AS mm
GROUP BY
house_id
) f
ON
s.house_number = f.house_id
LEFT JOIN(
SELECT
house_id,
SUM(gtax) AS gtax_collected,
SUM(htax) AS htax_collected,
SUM(LTAX) AS LTAX_collected
FROM
(
SELECT
house_id,
CASE WHEN h.subincome = 'Garbage tax' THEN SUM(h.rupees) ELSE 0
END AS gtax,
CASE WHEN h.subincome = 'House tax' THEN SUM(h.rupees) ELSE 0
END AS htax,
CASE WHEN h.subincome = 'Light tax' THEN SUM(h.rupees) ELSE 0
END AS LTAX
FROM
house_details h
GROUP BY
house_id,
subincome
) AS main
GROUP BY
house_id
) cf
ON
s.house_number = cf.house_id
LEFT JOIN(
SELECT house_number AS sid,
consumer_name,
address
FROM
consumer_details
) c
ON
s.house_number = c.sid
WHERE
s.year <> '2017-2018'
GROUP BY
c.consumer_name,
s.house_number,
c.address
) az
) aa
这篇关于使用连接和分组依据从三个表中获取值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
