从同一列中选择多个最大值

I have a user database where users are assigned an ID number in a certain range according to their type. For example, board members get an ID between 1 and 100, children get an ID between 1001 and 3000, parents get an ID between 3001 and 7000 etc.*

我有一个用户数据库，根据用户的类型为用户分配一定范围内的ID号。例如，董事会成员获得1到100之间的ID，孩子获得100到3000之间的ID，父母获得300到7000之间的ID等。*

I'd like to get a list of the highest number in use for each "segment" of my IDs.

我想得到一个列表，列出我的ID的每个“段”使用的最高数字。

I can of course get the highest number of all by doing

我当然可以通过这样做获得最多的数量

SELECT MAX(Persons.Number) as Maximum FROM Persons

and get the highest number below 3000 like this:

并获得3000以下的最高数字：

SELECT MAX(Persons.Number) as MaxChild FROM Persons WHERE Persons.Number<=3000

...but how could I get the highest number below 100 AND the highest number below 1000 AND the highest number below 3000 etc. etc. with a single SELECT statement?

...但是如何使用单个SELECT语句获得100以下的最高数字和1000以下的最高数字以及3000以下的最高数字等？

_{*I do have these characteristics stored in the database elsewhere; the "bucketing" of ID numbers is just for making it easier to spot at first glance where a certain user belongs}

*我确实将这些特征存储在其他地方的数据库中; ID号的“分组”只是为了让乍一看更容易发现某个用户所属的位置

2 个解决方案

#1

Just use IF():

只需使用IF（）：

SELECT 
MAX(IF(Persons.Number BETWEEN x AND y, Persons.Number, NULL)) AS max_range_x_y, 
MAX(IF(Persons.Number BETWEEN i AND j, Persons.Number, NULL)) AS max_range_i_j, ...
FROM Persons;

Above is MySQL syntax. In SQL Server you might use IIF() instead. What should work in every RDBMS (because it's ANSI-SQL Standard) is

以上是MySQL语法。在SQL Server中，您可以使用IIF（）代替。什么应该在每个RDBMS中工作（因为它是ANSI-SQL标准）

SELECT 
MAX(CASE WHEN Persons.Number BETWEEN x AND y THEN Persons.Number ELSE NULL END) AS max_range_x_y, 
MAX(CASE WHEN Persons.Number BETWEEN i AND j THEN Persons.Number ELSE NULL END) AS max_range_i_j, ...
FROM Persons;

#2

SELECT 
MAX(CASE WHEN id BETWEEN 1 and 100 THEN Number ELSE NULL END) as BoardMax,
MAX(CASE WHEN ID BETWEEN 1001 and 3000 THEN Number ELSE null END) as ChildMax,
MAX(CASE WHEN ID BETWEEN 3001 and 7000 THEN Number ELSE null END) as ParentMax
from 
Persons

#1