如何查找范围来自另一个表的dyamically的数字的范围？

Question

如果我有两个表：

PersonID | Count 
----------------- 
1  | 45 
2  | 5 
3  | 120 
4  | 87 
5  | 60 
6  | 200 
7  | 31 

SizeName | LowerLimit 
----------------- 
Small | 0 
Medium | 50 
Large | 100 

我试图找出如何做一个查询以获得类似的结果：

PersonID | SizeName 
----------------- 
1  | Small 
2  | Small 
3  | Large 
4  | Medium 
5  | Medium 
6  | Large 
7  | Small 

基本上，一个表指定了一个未知号码“范围名称”及其整数范围相关联。因此，人员表中0到49的计数范围得到一个“小”的称号。 50-99获得'中等'等，但我需要它是动态的，因为我不知道范围名称或整数值。我可以在单个查询中做到这一点，还是必须编写一个单独的函数来循环遍历可能性？

Answer 1

尝试了这一点：

SELECT PersonID, SizeName 
FROM 
    (
    SELECT 
     PersonID, 
     (SELECT MAX([LowerLimit]) FROM dbo.[Size] WHERE [LowerLimit] < [COUNT]) As LowerLimit 
    FROM dbo.Person 
    ) A 
    INNER JOIN dbo.[SIZE] B ON A.LowerLimit = B.LowerLimit 

Answer 2

With Ranges As 
    (
    Select 'Small' As Name, 0 As LowerLimit 
    Union All Select 'Medium', 50 
    Union All Select 'Large', 100 
    ) 
    , Person As 
    (
    Select 1 As PersonId, 45 As [Count] 
    Union All Select 2, 5 
    Union All Select 3, 120 
    Union All Select 4, 87 
    Union All Select 5, 60 
    Union All Select 6, 200 
    Union All Select 7, 31 
    ) 
    , RangeStartEnd As 
    (
    Select R1.Name 
     , Case When Min(R1.LowerLimit) = 0 Then -1 Else MIN(R1.LowerLimit) End As StartValue 
     , Coalesce(MIN(R2.LowerLimit), 2147483647) As EndValue 
    From Ranges As R1 
     Left Join Ranges As R2 
      On R2.LowerLimit > R1.LowerLimit 
    Group By R1.Name 
    ) 
Select P.PersonId, P.[Count], RSE.Name 
From Person As P 
    Join RangeStartEnd As RSE 
     On P.[Count] > RSE.StartValue 
      And P.[Count] <= RSE.EndValue 

虽然我使用共表表达式（CTE的简称）只在SQL Server 2005+存在，这可以用多个查询来完成，你创建一个临时表来存储相当于RangeStartEnd cte。诀窍是创建一个具有起始列和结束列的视图。

Answer 3

SELECT p.PersonID, Ranges.SizeName 
FROM People P 
JOIN 
    (
    SELECT SizeName, LowerLimit, MIN(COALESCE(upperlimit, 2000000)) AS upperlimit 
    FROM (
     SELECT rl.SizeName, rl.LowerLimit, ru.LowerLimit AS UpperLimit 
     FROM Ranges rl 
     LEFT OUTER JOIN Ranges ru ON rl.LowerLimit < ru.LowerLimit 
     ) r 
    WHERE r.LowerLimit < COALESCE(r.UpperLimit, 2000000) 
    GROUP BY SizeName, LowerLimit 
    ) Ranges ON p.Count >= Ranges.LowerLimit AND p.Count < Ranges.upperlimit 
ORDER BY PersonID