sql float保留两位

最新推荐文章于 2024-08-19 03:46:23 发布

harris135

最新推荐文章于 2024-08-19 03:46:23 发布

阅读量5.1k

点赞数

分类专栏： sql Mysql PL-SQL 文章标签： sql round cast

本文链接：https://blog.csdn.net/harris135/article/details/78093888

版权

Mysql 同时被 3 个专栏收录

20 篇文章 0 订阅

订阅专栏

sql

8 篇文章 0 订阅

订阅专栏

PL-SQL

2 篇文章 0 订阅

订阅专栏

people table schema

id
name

job table schema

id
people_id
job_title
salary

resultant table schema

job_title (unique)
average_salary (float, 2 dp)
total_people (int)
total_salary (float, 2 dp)

1.float 保留两位

SELECT 
  j.job_title,
  cast(ROUND(SUM(j.salary) / COUNT(j),2) as float) as average_salary,
  COUNT(j) as total_people,
  cast(ROUND(SUM(j.salary),2) as float) as total_salary
  FROM people p,job j
  where 
    p.id = j.people_id
  GROUP BY j.job_title
  ORDER BY average_salary desc

2.查询结果

job_title        average_salary    total_people    total_salary
police officer       78.53              3             235.59
astronomer           77.46              6             464.78
accountant           74.01              6             444.07
writer               68.58              4             274.32
teacher              67.9               2             135.8