問題描述
將範圍分組到範圍 (Group a range towards a range)
I have a table:
tblUnit (ID, Name, PriceFrom, PriceTo)
1, Audi, 170, 340
2, BMW, 250, 290
3, Ford, 275, 500
4, Kia, 110, 250
5, VW, 135, 460
And then I have predefined price ranges like this:
tblPriceRange(ID, PriceFrom, PriceTo)
1, 0, 100
2, 100, 200
3, 200, 300
4, 300, 400
5, 400, 1000
I am trying to count the number of vehicles that exists within one or more price ranges. BMW is only in 1 range, while Audi is in 3 ranges and Ford is in 3.
The result I am after should look something like this:
VehiclesPerRange:
(RangeFrom, RangeTo, NoVehicles)
0, 100, 0
100, 200, 3
200, 300, 5
300, 400, 3
400, 1000, 2
I have read through lots of posts on this forum and elsewhere about grouping by a range. But those examples are focused on 1 single price that should be grouped towards a range. I understand how to do this via a join etc., but I cant figure out how to write SQL to group a range towards a range.
Any suggestions is highly appreciated!
‑‑‑‑‑
參考解法
方法 1:
You want to join tblUnit with tblPriceRange matching rows where the ranges overlap. Two ranges overlap if the begin of the second is before the end of the first and the begin of the first before the end of the second, so your join condition would look like this:
SELECT *
FROM dbo.tblUnit u
JOIN dbo.tblPriceRange p
ON u.PriceFrom < p.PriceTo
AND p.PriceFrom < u.PriceTo
After that you just have to group and count:
SELECT PriceFrom, PriceTo, COUNT(1)
FROM(
SELECT p.PriceFrom, p.PriceTo
FROM dbo.tblUnit u
JOIN dbo.tblPriceRange p
ON u.PriceFrom < p.PriceTo
AND p.PriceFrom < u.PriceTo
)X
GROUP BY PriceFrom, PriceTo;
(by TwisterX、Sebastian Meine)