Tables:
people(id, name)
job (id, people_id, job_title, salary)
目标:显示每个独特的工作,平均工资总额(FLOAT 并四舍五入到小数点后两位), 总数
人员及工资总额(浮点数并四舍五入至小数点后两位)并按最高平均工资排序。
因此,挑战在于将转换类型保持为 float,同时将其四舍五入到小数点后两位。
我已经将其四舍五入到小数点后两位,但它不是浮动的。我已经把它弄到了浮动的位置,但我无法将其四舍五入到小数点后两位。
我的尝试:
尝试1:
SELECT
distinct(j.job_title) as job_title,
to_char(AVG(j.salary)::FLOAT, 'FM999999990.00') as average_salary,
COUNT(p.id) as total_people,
CAST (SUM(j.salary) AS FLOAT) as total_salary
FROM people p
JOIN job j on p.id = j.people_id
GROUP BY j.job_title
ORDER BY total_salary
问题:仍然说它不是浮动的
尝试2:
SELECT
distinct(j.job_title) as job_title,
CAST (AVG(j.salary) AS FLOAT) as average_salary,
COUNT(p.id) as total_people,
CAST (SUM(j.salary) AS FLOAT) as total_salary
FROM people p
JOIN job j on p.id = j.people_id
GROUP BY j.job_title
ORDER BY total_salary
问题:未四舍五入到小数点后两位
尝试3:
SELECT
distinct(j.job_title) as job_title,
ROUND (AVG(CAST(j.salary as FLOAT)), 2)) as average_salary,
COUNT(p.id),
ROUND (SUM(CAST(j.salary as FLOAT)), 2)) as total_salary
FROM people p
JOIN job j on p.id = j.people_id
GROUP BY j.job_title
ORDER BY total_salary
我收到一条错误消息,说我需要添加显式转换类型,这导致我尝试第 1 种方法。