- 建立测试表
testdb=# CREATE TEMP TABLE team ( id serial, name text, birth_year integer, salary integer );
- 插入记录
testdb=# INSERT INTO team (name,birth_year,salary) VALUES ('Gabriel',1970,44000), ('Tom',1972,36000), ('Bill',1978,39500), ('Bob',1980,29000), ('Roger',1976,26800), ('Lucas',1965,56900), ('Jerome',1984,33500), ('Andrew',1992,41600), ('John',1991,40000), ('Paul',1964,39400), ('Richard',1986,23000), ('Joseph',1988,87000), ('Jason',1990,55000);
- 查询结果
testdb=# WITH series AS ( SELECT generate_series(1950,2000,10) AS time_start -- 1950 = min,2010 = max,10 = 10 year interval ),range AS ( SELECT time_start,(time_start + 9) AS time_end FROM series -- 9 = interval (10 years) minus 1 ) SELECT time_start,time_end, (SELECT count(*) FROM team WHERE birth_year BETWEEN time_start AND time_end) as team_members, round((SELECT AVG(salary) FROM team WHERE birth_year BETWEEN time_start AND time_end),2) as salary_avg, (SELECT MIN(salary) FROM team WHERE birth_year BETWEEN time_start AND time_end) as salary_min, (SELECT MAX(salary) FROM team WHERE birth_year BETWEEN time_start AND time_end) as salary_max FROM range;
输出结果:
time_start | time_end | team_members | salary_avg | salary_min | salary_max ------------+----------+--------------+------------+------------+------------ 1950 | 1959 | 0 | | | 1960 | 1969 | 2 | 48150.00 | 39400 | 56900 1970 | 1979 | 4 | 36575.00 | 26800 | 44000 1980 | 1989 | 4 | 43125.00 | 23000 | 87000 1990 | 1999 | 3 | 45533.33 | 40000 | 55000 2000 | 2009 | 0 | | | (6 rows)
testdb=# (编辑:李大同)
【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!
|