SQL 语句技巧--聚合函数的灵活使用
在CSDN看到一篇贴子,是讨论一个SQL语句的写法:要求如下
表:tblDept(ID,DeptName)
表:tblSalary(ID,DeptID,Name,Salary)
create table tblDept(ID int,DeptName nvarchar(20))
create table tblSalary(ID int,DeptID int,Name nvarchar(20),Salary decimal(10,2))
请用SQL语句写出:
超过半数的部门员工工资大于3000元的部门
表:tblSalary(ID,DeptID,Name,Salary)
create table tblDept(ID int,DeptName nvarchar(20))
create table tblSalary(ID int,DeptID int,Name nvarchar(20),Salary decimal(10,2))
请用SQL语句写出:
超过半数的部门员工工资大于3000元的部门
插入测试数据:
insert into tblDept
select 1,'DotNet' union all select 2,'Java'
insert into tblSalary
select 1,1,'张三',2000 union all
select 2,1,'李四',2800 union all
select 3,2,'王五',2900 union all
select 4,2,'找刘',3200 union all
select 5,2,'王启',3400
select 1,'DotNet' union all select 2,'Java'
insert into tblSalary
select 1,1,'张三',2000 union all
select 2,1,'李四',2800 union all
select 3,2,'王五',2900 union all
select 4,2,'找刘',3200 union all
select 5,2,'王启',3400
我的写法是如下:
select * from tblDept
where id in(select DeptID from tblSalary group by DeptID having
count(case when Salary>3000 then 1 else 0 end)/count(*)>0.5)
where id in(select DeptID from tblSalary group by DeptID having
count(case when Salary>3000 then 1 else 0 end)/count(*)>0.5)
后来发现这里有点不对:
1,后面相除数据不能为小数。
2,count函数理解错误。
修改最后的结果是:
select * from tblDept
where id in(select DeptID from tblSalary group by DeptID having
sum(case when Salary>3000 then 1 else 0 end)*1.0/count(*)>0.5)
where id in(select DeptID from tblSalary group by DeptID having
sum(case when Salary>3000 then 1 else 0 end)*1.0/count(*)>0.5)
原因:
这里有两点概念没有弄清楚:count,整数相除
1,COUNT : 和表达式的值无关
COUNT(*) 计算数量,包括 NULL 值。
COUNT(ALL expression) 计算数量,不包括 NULL 值 。
COUNT(DISTINCT expression) 计算唯一值数量,不包括 NULL 值
COUNT(ALL expression) 计算数量,不包括 NULL 值 。
COUNT(DISTINCT expression) 计算唯一值数量,不包括 NULL 值
测试数据:
select count(t)
from (
select null t union select 1 t union select 6 t
) t
from (
select null t union select 1 t union select 6 t
) t
2,整数相除,转换为数据必须*1.0转化
总结
这样这个语句有两个写法:
select * from tblDept
where id in(select DeptID from tblSalary group by DeptID having
count(case when Salary>3000 then 1 else null end)*1.0/count(*)>0.5)
where id in(select DeptID from tblSalary group by DeptID having
count(case when Salary>3000 then 1 else null end)*1.0/count(*)>0.5)
或:
select * from tblDept
where id in(select DeptID from tblSalary group by DeptID having
sum(case when Salary>3000 then 1 else 0 end)*1.0/count(*)>0.5)
where id in(select DeptID from tblSalary group by DeptID having
sum(case when Salary>3000 then 1 else 0 end)*1.0/count(*)>0.5)
分类:
SQL 技巧和优化
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 开发者必知的日志记录最佳实践
· SQL Server 2025 AI相关能力初探
· Linux系列:如何用 C#调用 C方法造成内存泄露
· AI与.NET技术实操系列(二):开始使用ML.NET
· 记一次.NET内存居高不下排查解决与启示
· 阿里最新开源QwQ-32B,效果媲美deepseek-r1满血版,部署成本又又又降低了!
· 开源Multi-agent AI智能体框架aevatar.ai,欢迎大家贡献代码
· Manus重磅发布:全球首款通用AI代理技术深度解析与实战指南
· 被坑几百块钱后,我竟然真的恢复了删除的微信聊天记录!
· AI技术革命,工作效率10个最佳AI工具