mysql - 年龄范围计算，其中每个国家/地区至少有两个不同年龄的个人

Question

对于我这里的数据集，我想创建结果，以便对于那些国家(至少有两个不同的年龄)，我可以总结年龄范围。

CREATE TABLE Employees(
    ID int (3) NOT NULL,
    Name varchar (50) NOT NULL,
    Age int (3) NOT NULL,
    Nationality varchar (50) NOT NULL
);


INSERT INTO Employees 
    (ID, Name, Age, Nationality)
VALUES
    (1, 'CHIN YEN', '19', 'China'),
    (2, 'MIKE PEARL', '21', 'United Kingdom'),
    (3, 'GREEN FIELD', '45', 'Nethernalnds'),
    (4, 'DEWANE PAUL', '57', 'Canada'),
    (5, 'MATTS', '32', 'Australia'),
    (6, 'PLANK OTO', '51', 'France'),
    (7, 'Manish Kumar', '42', 'India'),
    (8, 'Matts', '55', 'USA'),
    (9, 'Mahesh Kumar', '32', 'USA'),
    (10, 'Chin Yen', '21', 'Japan');

我想做的是:

SELECT Nationality,
       Max(Age) - Min(Age) AS Age_Range
FROM Employees;

Answer 1

我想你只需要一个group by:

SELECT Nationality,
       Max(Age) - Min(Age) AS Age_Range
FROM Employees
GROUP BY Nationality;

您可能想要添加 HAVING Age_Range > 0。