在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 )