sqlserver 均值保留小数

最新推荐文章于 2025-01-09 13:30:38 发布

Fergus awsl

最新推荐文章于 2025-01-09 13:30:38 发布

阅读量901

点赞数

CC 4.0 BY-SA版权

分类专栏： SQL

本文链接：https://blog.youkuaiyun.com/weixin_43650411/article/details/101524749

SQL 专栏收录该内容

71 篇文章

订阅专栏

本文介绍如何在SQL Server中编写查询，以计算雇员项目的平均值，并确保结果保留特定的小数位数。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

在这里插入图片描述



use master
go

if exists( select * from sysdatabases where name='LeetCode')
	begin
		drop database LeetCode
		select 1 as deleted_LeetCode
	end
create database LeetCode
use LeetCode
go

if exists(select * from sysobjects where name='Employee')
	drop table Employee
create table Employee
(
	employee_id  int primary key,
	name  varchar(20),
	experience_years  int
)

insert into Employee values
	--(1, 'Khaled', 3), (2, 'Ali', 2), (3, 'John', 1), (4, 'Doe', 2)
	(1,'qk',11),(2,'prqvt',22),(3,'nfeltfjwi',22),(4,'ljyaezj',27),(5,'y',23),(6,'sipmi',18),(7,'xshwcocj',29),(8,'ifpyx',16),(9,'arn',18),(10,'fyqblblx',9),(11,'pqkwek',17),(12,'ufirmczjau',9),(13,'w',28),(14,'hksbhleeen',6),(15,'rnnxr',15),(16,'ymc',4),(17,'hzzpjre',29),(18,'sesmyrkrc',30),(19,'zr',4),(20,'trky',5),(21,'k',28),(22,'chwum',22),(23,'aj',20),(24,'yqrovlxki',0),(25,'juec',17),(26,'kre',15),(27,'y',4),(28,'kmhf',12),(29,'edcj',2),(30,'vv',4),(31,'zysb',14),(32,'dthvenxeqt',7),(33,'yvr',24),(34,'mqxt',11),(35,'yduucuh',25),(36,'a',30),(37,'fftbrtj',22),(38,'jwsdgsaz',7),(39,'pxah',6),(40,'rj',3),(41,'hsrvgjp',15),(42,'ylbbtheeyb',15),(43,'fywjflxlih',26),(44,'lofy',22),(45,'mkrisafjz',8),(46,'gt',2),(47,'hpmasdxevx',0),(48,'jbajov',11),(49,'ibnntts',3),(50,'tbvkbmxjh',5),(51,'zidspwa',10),(52,'giu',11),(53,'upvgk',7),(54,'mkappx',30),(55,'htvri',17),(56,'ofxyvuha',4),(57,'c',24),(58,'bctxixy',5),(59,'ut',16)
go


if exists(select * from sysobjects where name='Project')
	drop table Project
create table Project
(
	project_id int,
	employee_id int,
	constraint pk_project primary key(project_id, employee_id),
	constraint fk_employee foreign key(employee_id) references Employee(employee_id)
)
insert into Project values
	--(1, 1),(1, 2),(1, 3),(2, 1),(2, 4)
	(1,2),(1,37),(1,3),(1,38),(1,4),(1,20),(1,14),(1,44),(1,29),(1,41),(1,51),(1,56),(1,34),(1,12),(1,31),(1,13),(1,48),(1,15),(1,23),(1,45),(1,5),(1,11),(1,25),(1,54),(1,8),(1,7),(1,36),(1,52),(1,6),(1,18),(1,57),(1,26),(1,43),(1,32),(1,55),(1,1),(1,16),(1,39),(1,50),(1,49),(1,28),(1,53),(1,17),(1,10),(1,40),(1,27),(1,22),(1,21),(2,45),(2,40),(2,21),(2,57),(2,53),(2,47),(2,33),(2,36),(2,55),(2,58),(2,49),(2,24),(2,16),(2,23),(2,18),(2,6),(2,46),(2,22),(2,41),(2,4),(2,28),(2,37),(2,10),(2,11),(2,44),(2,31),(2,5),(2,48),(2,43),(2,34),(2,12),(2,30),(2,42),(2,39),(2,35),(2,29),(2,54),(2,7),(2,50),(2,9),(3,36),(3,2),(3,40),(3,23),(3,57),(3,17),(3,11),(3,43),(3,21),(3,51),(3,3),(3,32),(3,19),(3,10),(3,56),(3,16),(3,55),(3,39),(3,50),(3,29),(3,45),(3,44),(3,41),(3,1),(3,48),(3,26),(3,38),(3,12),(3,33),(3,13),(3,37),(3,35),(3,49),(3,8),(3,15),(3,42),(3,34),(3,24),(3,4),(3,25),(3,30),(3,58),(3,53),(3,54),(3,22),(3,52),(3,59),(3,31),(3,5),(3,20),(3,14),(3,46),(4,53),(4,14),(4,40),(4,34),(4,25),(4,54),(4,50),(4,9),(4,33),(4,5),(4,3),(4,39),(4,31),(4,27),(4,12),(4,35),(4,22),(4,47),(4,41),(4,20),(4,4),(4,24),(4,26),(4,38),(4,49),(4,15),(4,59),(4,16),(5,40),(6,36),(7,40),(7,2),(7,28),(7,13),(7,46),(7,1),(7,17),(7,41),(7,33),(7,9),(7,25),(7,50),(7,37),(7,39),(7,21),(7,36),(7,56),(7,42),(7,27),(7,34),(7,4),(7,52),(7,26),(7,53),(7,20),(7,14),(7,55),(7,6),(7,31),(7,23),(7,11),(7,7),(7,22),(7,12),(8,38),(8,22),(8,42),(8,24),(8,41),(8,32),(8,59),(8,11),(8,31),(8,54),(8,13),(8,55),(8,43),(8,14),(8,26),(8,49),(8,17),(8,46),(8,21),(8,29),(8,12),(8,8),(8,25),(8,53),(8,34),(8,1),(8,9),(8,28),(8,15),(8,40),(8,7),(8,52),(8,3),(8,36),(8,51),(8,58),(8,48),(8,27),(8,30),(8,16),(8,4),(8,57),(8,23),(8,37),(8,20),(8,2),(8,45),(8,18),(8,33),(8,39),(8,6),(8,50),(8,44),(8,10),(8,5),(8,19),(9,51),(9,32),(9,39),(9,21),(10,4),(10,5),(10,16),(10,57),(10,41),(10,49),(10,21),(10,25),(10,38),(10,48),(11,22),(11,59),(11,54),(11,29),(11,56),(11,37),(11,7),(11,18),(11,48),(11,31),(11,42),(11,16),(11,27),(11,4),(11,23),(11,47),(11,52),(11,49),(11,32),(11,33),(11,38),(11,36),(11,21),(11,40),(11,24),(11,34),(11,53),(11,35),(11,45),(11,5),(11,44),(11,39),(11,51),(11,25),(11,58),(11,3),(11,30),(11,50),(11,1),(11,12),(11,14),(11,8),(11,19),(11,20),(11,57),(11,55),(11,46),(11,2),(11,43),(11,11),(11,41),(11,13),(11,10),(11,6),(11,17),(11,9),(11,26),(11,15),(12,39),(12,19),(12,50),(12,6),(12,48),(12,15),(12,8),(12,49),(13,15),(13,9),(13,48),(13,58),(13,42),(13,11),(13,22),(13,45),(13,18),(13,40),(13,16),(13,6),(13,46),(13,5),(13,36),(13,10),(13,54),(13,52),(13,25),(13,53),(13,26),(13,17),(13,7),(13,49),(13,55),(13,35),(13,37),(13,31),(13,32),(14,53),(14,40),(14,23),(14,25),(14,12),(14,26),(14,57),(14,54),(14,49),(14,42),(14,16),(14,44),(14,41),(14,58),(14,5),(14,9),(14,29),(14,3),(14,38),(14,34),(14,45),(14,24),(14,21),(14,14),(14,11),(14,10),(14,8),(14,37),(14,17),(14,47),(14,22),(14,7),(14,1),(14,28),(14,15),(14,55),(14,48),(14,50),(14,52),(14,30),(14,31),(14,56),(14,33),(14,4),(14,43),(14,19),(14,13),(14,2),(14,20),(14,18),(14,46),(14,59),(14,32),(14,39),(14,51),(14,35),(14,6),(14,36),(15,35),(15,43),(15,2),(15,53),(15,8),(15,57),(15,30),(15,13),(15,21),(15,59),(15,40),(15,44),(15,1),(15,42),(15,37),(15,12),(15,33),(15,45),(15,47),(15,51),(15,7),(15,34),(15,29),(15,23),(15,27),(15,20),(15,22),(15,46),(15,5),(15,41),(15,50),(15,17),(15,55),(15,4),(15,56),(15,6),(15,11),(15,15),(15,10),(16,37),(16,57),(16,56),(16,4),(16,17),(16,24),(16,49),(16,31),(16,16),(16,30),(16,8),(16,12),(16,14),(16,34),(16,27),(16,2),(16,46),(16,39),(16,59),(16,38),(16,6),(16,22),(16,41),(16,42),(16,50),(16,33),(16,21),(16,13),(16,23),(16,3),(16,48),(16,54),(16,43),(16,40),(16,53),(16,9),(16,25),(16,10),(16,58),(16,18),(16,35),(16,36),(16,32),(16,45),(16,47),(16,55),(16,5),(16,15),(16,26),(16,11),(16,52),(16,29),(16,44),(17,19),(17,57),(17,31),(17,42),(17,49),(17,15),(17,6),(17,38),(17,26),(17,23),(17,21),(17,14),(17,37),(17,45),(17,35),(17,43),(17,44),(17,28),(18,8),(18,9),(18,31),(18,14),(18,58),(18,20),(18,56),(18,21),(18,32),(18,42),(18,3),(18,5),(18,18),(18,30),(18,29),(18,52),(18,28),(18,53),(18,57),(18,26),(18,22),(18,17),(18,23),(18,7),(18,55),(18,44),(18,46),(18,11),(18,39),(18,40),(18,41),(18,4),(18,25),(18,35),(18,24),(18,16),(18,27),(18,47),(18,34),(19,41),(19,32),(19,40),(19,25),(19,57),(19,15),(19,27),(19,42),(20,39),(20,25),(20,32),(20,20),(20,43),(20,40),(20,45),(20,55),(21,59),(21,9),(21,23),(21,32),(21,21),(21,30),(21,39),(21,47),(22,28),(22,5),(22,40),(22,8),(22,29),(22,52),(22,19),(22,13),(22,17),(22,44),(22,30),(22,48),(22,4),(22,27),(22,16),(22,45),(22,6),(22,32),(22,50),(22,24),(22,42),(22,23),(22,14),(22,49),(22,36),(22,21),(22,59),(22,54),(22,9),(22,34),(22,43),(22,7),(22,46),(22,47),(22,26),(22,20),(22,15),(22,35),(22,58),(22,51),(22,31),(22,2),(22,25),(22,18),(22,10),(22,56),(22,12),(23,18),(23,4),(23,24),(23,39),(23,32),(23,38),(23,9),(23,40),(23,46),(23,52),(23,53),(23,26),(23,5),(23,3),(23,22),(23,57),(23,29),(23,31),(23,11),(23,49),(23,12),(23,59),(23,55),(23,27),(23,10),(23,23),(23,1),(23,41),(23,35),(23,6),(23,13),(23,36),(23,51),(24,25),(24,59),(24,52),(24,44),(24,19),(24,43),(24,42),(24,41),(24,48),(24,23),(24,40),(24,3),(24,39),(24,36),(24,54),(24,56),(24,49),(24,10),(24,22),(24,37),(24,14),(24,16),(25,2),(25,30),(25,4),(25,7),(25,46),(25,41),(25,36),(25,11),(25,12),(25,3),(25,43),(25,24),(25,26),(25,27),(25,57),(25,5),(25,6),(25,25),(25,48),(25,49),(25,28),(25,17),(25,31),(25,34),(25,52),(25,35),(25,45),(25,18),(25,40),(25,44),(25,13),(25,19),(25,20),(25,1),(25,32),(25,50),(25,14),(25,37),(25,39),(25,29),(26,22),(26,55),(26,2),(26,3),(26,11),(26,16),(26,40),(26,33),(26,9),(26,54),(26,58),(26,27),(26,50),(26,18),(26,14),(26,39),(26,7),(26,47),(26,38),(26,35),(26,46),(26,1),(26,29),(26,4),(26,10),(26,37),(26,23),(26,15),(26,26),(26,24),(26,44),(26,41),(26,17),(26,20),(26,56),(26,45),(26,59),(26,36),(26,25),(26,31),(27,19),(27,10),(27,25),(27,4),(27,15),(27,47),(27,14),(27,38),(27,46),(27,2),(27,8),(27,3),(27,37),(27,5),(27,42),(27,6),(27,35),(27,27),(27,39),(27,36),(27,55),(27,33),(27,28),(27,24),(27,41),(27,49),(27,9),(27,53),(27,16),(27,23),(27,11),(27,20),(27,29),(27,43),(27,18),(27,13),(27,58),(27,50)
go

insert into Project values
	(27,56),(27,1),(27,31),(27,32),(27,59),(27,40),(27,54),(27,57),(27,17),(27,21),(27,45),(27,52),(27,48),(27,51),(27,12),(28,28),(28,47),(28,15),(28,14),(28,25),(28,3),(28,5),(28,38),(28,13),(28,6),(28,30),(28,44),(28,35),(28,21),(28,55),(28,27),(28,45),(28,17),(28,41),(28,37),(28,42),(28,34),(28,40),(28,54),(28,23),(28,19),(28,39),(28,16),(28,57),(28,50),(28,7),(28,29),(28,4),(28,56),(28,22),(28,53),(28,2),(28,26),(28,58),(28,31),(28,48),(28,8),(28,12),(28,32),(29,35),(29,22),(29,58),(29,48),(29,26),(29,1),(29,20),(29,52),(29,24),(29,25),(29,10),(29,32),(29,28),(29,45),(29,31),(29,39),(29,53),(29,37),(29,13),(29,54),(29,34),(29,7),(29,57),(29,56),(29,49),(29,8),(29,55),(29,23),(29,44),(29,21),(29,47),(29,2),(29,33),(29,41),(29,11),(29,17),(29,43),(29,36),(29,3),(29,51),(29,16),(29,50),(29,42),(29,6),(29,18),(29,19),(29,30),(29,46),(29,9),(29,5),(29,59),(30,36),(30,57),(30,6),(30,37),(30,50),(30,20),(30,18),(30,3),(30,53),(30,19),(30,14),(30,42),(30,22),(30,56),(30,21),(30,55),(30,17),(30,31),(30,10),(30,46),(30,26),(30,4),(30,1),(30,39),(30,28),(30,12),(30,5),(30,43),(30,24),(30,7),(30,45),(30,9),(30,44),(30,40),(30,35),(30,23),(30,41),(30,8),(30,33),(30,32),(30,15),(30,47),(30,59),(30,27),(30,25),(30,29),(30,48),(30,30),(30,54),(30,11),(31,41),(31,29),(31,6),(31,49),(31,39),(31,27),(32,45),(32,44),(32,29),(32,36),(32,21),(32,59),(32,6),(32,12),(32,8),(32,47),(32,11),(32,30),(32,54),(32,56),(32,49),(32,40),(32,52),(32,16),(32,32),(32,39),(32,53),(32,9),(32,27),(32,25),(32,13),(32,48),(32,20),(32,26),(32,41),(32,23),(32,14),(32,10),(32,15),(32,7),(32,58),(32,38),(32,17),(32,33),(32,3),(32,57),(32,19),(32,55),(32,18),(32,24),(32,22),(32,31),(32,50),(32,4),(32,42),(32,35),(32,28),(33,21),(33,15),(33,3),(33,33),(33,11),(33,23),(33,32),(33,25),(33,48),(33,52),(33,13),(33,51),(33,26),(33,45),(33,49),(33,2),(33,24),(33,54),(33,46),(33,28),(33,37),(33,18),(33,14),(33,22),(33,17),(33,53),(33,59),(33,12),(33,34),(33,44),(33,31),(33,20),(33,58),(33,4),(33,43),(33,5),(33,39),(33,40),(33,35),(33,9),(33,42),(33,1),(33,8),(33,6),(33,47),(34,30),(34,29),(34,9),(34,48),(34,55),(34,24),(34,10),(34,44),(34,3),(34,20),(34,32),(34,5),(34,47),(34,53),(34,49),(34,7),(34,33),(34,18),(35,10),(35,40),(35,50),(35,47),(35,33),(35,6),(35,18),(35,29),(35,13),(35,45),(35,31),(36,39),(36,21),(36,45),(36,5),(36,40),(36,7),(36,48),(36,9),(36,33),(36,16),(36,54),(36,26),(36,36),(36,28),(36,13),(36,56),(36,20),(36,24),(36,47),(36,17),(36,37),(36,52),(36,34),(36,1),(36,15),(36,53),(36,38),(36,4),(36,11),(36,6),(37,18),(37,4),(37,11),(37,35),(37,54),(37,12),(37,45),(37,31),(37,39),(37,16),(37,33),(37,53),(37,9),(37,23),(37,19),(37,34),(37,49),(37,37),(37,36),(37,25),(37,59),(37,13),(37,26),(37,55),(37,6),(37,44),(37,30),(37,47),(37,8),(37,1),(37,41),(37,32),(37,10),(37,57),(37,51),(37,42),(37,7),(37,2),(37,24),(38,40),(38,7),(38,41),(38,17),(38,54),(38,58),(38,44),(38,49),(39,56),(39,5),(39,1),(39,39),(39,36),(39,31),(39,11),(39,26),(39,2),(39,29),(39,30),(39,44),(39,4),(39,22),(39,15),(39,17),(39,6),(39,59),(39,35),(39,32),(39,53),(40,33),(40,26),(40,55),(40,13),(40,32),(40,56),(40,58),(40,17),(40,23),(40,52),(40,44),(40,46),(40,43),(40,28),(40,31),(40,5),(40,27),(40,24),(40,25),(40,6),(40,20),(40,16),(40,40),(40,57),(40,9),(40,19),(40,42),(40,11),(40,41),(40,39),(41,40),(41,32),(41,24),(41,34),(41,28),(41,8),(41,11),(41,56),(41,25),(41,4),(41,51),(41,10),(41,23),(41,27),(41,5),(41,43),(42,43),(42,44),(42,3),(42,40),(42,17),(42,16),(42,38),(42,26),(42,9),(42,54),(42,48),(42,7),(42,14),(42,5),(42,46),(42,4),(42,27),(42,45),(42,2),(42,30),(42,13),(42,23),(42,55),(42,8),(42,41),(42,50),(42,35),(42,19),(42,10),(42,34),(42,29),(42,20),(42,15),(42,6),(42,24),(42,31),(42,11),(42,57),(42,52),(42,32),(42,28),(42,21),(42,25),(42,22),(42,53),(42,58),(42,12),(42,39),(42,18),(42,36),(43,45),(43,43),(43,55),(43,10),(43,22),(43,6),(43,36),(43,4),(43,34),(43,29),(43,27),(43,49),(43,41),(43,52),(43,59),(43,3),(43,42),(43,57),(43,53),(43,1),(43,46),(43,58),(43,26),(43,48),(43,11),(43,28),(43,32),(43,25),(43,33),(43,2),(43,15),(43,40),(43,18),(43,14),(43,23),(43,5),(43,35),(43,31),(43,8),(43,51),(43,50),(43,16),(43,13),(43,30),(43,44),(43,7),(43,21),(43,17),(43,24),(43,9),(43,56),(43,19),(44,18),(44,20),(44,25),(44,11),(44,55),(44,21),(44,19),(44,7),(44,49),(45,6),(45,36),(45,27),(45,21),(45,26),(45,56),(45,8),(45,5),(45,25),(45,34),(45,32),(45,49),(45,3),(45,46),(45,35),(45,48),(45,18),(45,28),(45,55),(45,12),(45,40),(45,20),(45,22),(45,19),(45,57),(45,31),(45,14),(45,42),(45,10),(45,41),(45,29),(45,52),(45,47),(45,50),(45,4),(45,11),(45,17),(45,15),(45,24),(45,59),(45,43),(45,1),(45,33),(45,13),(45,53),(45,9),(45,58),(46,41),(46,53),(46,19),(46,22),(46,14),(46,54),(46,26),(46,56),(46,50),(46,7),(46,13),(46,15),(46,52),(46,59),(46,42),(46,11),(46,1),(46,48),(46,28),(46,23),(46,40),(46,27),(46,2),(46,17),(46,18),(46,45),(46,6),(46,3),(46,55),(46,4),(46,47),(46,43),(46,36),(46,8),(46,37),(46,21),(46,31),(46,34),(46,9),(46,20),(46,24),(46,38),(46,49),(46,12),(46,29),(46,57),(46,10),(46,46),(46,44),(46,35),(46,25),(46,33),(46,32),(46,39),(46,16),(46,51),(46,30),(46,58),(47,28),(47,41),(47,18),(47,26),(47,40),(47,3),(47,24),(47,54),(47,48),(47,47),(47,59),(47,56),(47,1),(47,29),(47,58),(47,34),(47,15),(47,7),(47,46),(47,35),(47,57),(47,53),(47,37),(47,19),(47,22),(47,25),(47,2),(47,43),(47,17),(47,4),(47,20),(47,51),(47,55),(47,16),(47,49),(47,10),(47,50),(47,13),(47,6),(47,32),(47,44),(47,30),(47,21),(47,52),(47,39),(47,8),(47,36),(47,23),(48,5),(48,7),(48,23),(48,49),(48,25),(49,59),(49,30),(49,52),(49,53),(49,46),(49,51),(49,14),(49,34),(49,57),(49,44),(49,17),(49,8),(49,33),(49,27),(49,42),(49,10),(49,9),(49,37),(49,56),(49,24),(49,55),(49,45),(49,28),(49,47),(49,50),(49,21),(49,29),(49,12),(49,36),(49,2),(49,7),(49,40),(49,43),(49,38),(49,5),(49,3),(49,25),(49,26),(49,32),(49,22),(49,41),(50,33),(50,8),(50,4),(50,49),(50,13),(50,44),(50,28),(50,26),(50,31),(50,46),(50,30),(50,25),(50,40),(50,39),(50,54),(50,58),(50,18),(50,12),(50,16),(50,35),(50,5),(50,47),(50,20),(50,57),(50,22),(50,59),(50,9),(50,23),(50,6)
go

select * 
	from Employee  -- select convert(decimal(5, 2), avg((employee_id)*1.0)) from Employee

select * 
	from Project

-- 每个项目中员工    平均工作年限   小数点后两位
select *
	from Project

select p.project_id, p.employee_id, e.experience_years
	from Project p
	join Employee e
	on p.employee_id=e.employee_id

-- 法1
select p.project_id, cast(AVG((e.experience_years)*1.0) as decimal(18,2)) as average_years  -- 均值小数保留
	from Project p
	join Employee e
	on p.employee_id=e.employee_id
	-- where p.project_id=2
	group by p.project_id
	
-- 法2
select p.project_id, convert(decimal(5,2), AVG((e.experience_years)*1.0)) as average_years
	from Project p
	join Employee e
	on p.employee_id=e.employee_id
	group by p.project_id

eg.2 编写一个SQL查询，报告所有雇员最多的项目。



-- top 1    solution 1  当最大值有多个时？
select p.project_id, count(p.employee_id)  -- 
	from Project p
	group by p.project_id
	order by count(p.employee_id) desc

select top 1 p.project_id, count(p.employee_id) -- , count(p.employee_id)  -- top 1  当满足最大值有多个时？
	from Project p
	group by p.project_id
	order by count(p.employee_id) desc

-- solution 2
select project_id  --, count(*) as cnt_employee
	from Project
	group by project_id
	having count(*)=
	(
	select max(cnt_employee) as max_employee
	from 
	(
	select p.project_id, count(*) as cnt_employee
	from Project p
	group by p.project_id
	) a 
	)