当前位置: 动力学知识库 > 问答 > 编程问答 >

sql server - SQL -- Join on a range of decimals

问题描述:

I am using Microsoft SQL Server and I have the following tables:

dbo.student:

+-----------+------------+

| StudentID | Percentage |

+-----------+------------+

| 100 | 0.75 |

| 101 | 0.82 |

| 102 | 0.95 |

| 103 | 0.59 |

+-----------+------------+

dbo.GradeMaster:

+---------+-------+-----------------------+--------------------+

| GradeID | Grade | PercentageRangeBottom | PercentageRangeTop |

+---------+-------+-----------------------+--------------------+

| 1 | A | 0.91 | 1 |

| 2 | B | 0.81 | 0.9 |

| 3 | C | 0.71 | 0.8 |

| 4 | D | 0.61 | 0.7 |

| 5 | F | 0 | 0.6 |

+---------+-------+-----------------------+--------------------+

I am trying to generate a report which will spit out a letter grade and a student ID. I don't want to use a giant case statement with hardcoded ranges in case anyone ever decides to update the ranges in dbo.GradeMaster (for example, an A becomes from .85 - 1). What is the best way to join on these two tables? I thought about also creating a function to figure out letter grade but I can't think of the smartest way to go about that. Any hints would be greatly appreciated. This is what I would expect the report to look like.

+-----------+-------+

| StudentID | Grade |

+-----------+-------+

| 100 | C |

| 101 | B |

| 102 | A |

| 103 | F |

+-----------+-------+

网友答案:

Something like the following should do the trick:

SELECT Student.StudentId, GradeMaster.Grade 
FROM Student 
    INNER JOIN GradeMaster ON
        Student.Percentage BETWEEN GradeMaster.PercentageRangeBottom AND GradeMaster.PercentageRangeTop

Instead of joining directly field-to-field you can use BETWEEN or similar logic to perform the join

分享给朋友:
您可能感兴趣的文章:
随机阅读: