日期:2014-05-17  浏览次数:20522 次

小数问题
我想通过下面的语句求结果为1.5的记录,即红色部分的值等于1.5,请大虾们指点一下,先谢谢

SELECT e,f,g FROM 

(SELECT e,f,g,COUNT(*) fc FROM T1 GROUP BY e,f,g) a 

WHERE

(
(SELECT MAX(fc) FROM (SELECT COUNT(*) fc FROM T1 GROUP BY e,f,g) b)+
(SELECT MIN(fc) FROM (SELECT COUNT(*) fc FROM T1 GROUP BY e,f,g) b)
)/2-fc=1.5


------解决方案--------------------
SQL code

declare @T1 table (e int,f int,g int)
insert into @T1
select 1,2,3 union all
select 1,2,3 union all
select 2,3,4 union all
select 2,3,5 union all
select 2,3,5 union all
select 2,3,6 union all
select 2,3,6 union all
select 2,3,6 union all
select 2,3,7 union all
select 2,3,7 union all
select 2,3,7 union all
select 2,3,7 union all
select 2,3,8 union all
select 2,3,8 union all
select 2,3,8 union all
select 2,3,8 union all
select 2,3,8 union all
select 2,3,8

select 
    e,f,g 
from  (select e,f,g,count(*) fc from @t1 group by e,f,g) a  
where
((select max(fc) from (select count(*) fc from @t1 group by e,f,g) b)+
(select min(fc) from (select count(*) fc from @t1 group by e,f,g) b))/2.0-fc=1.5

--测试数据中最大值是2、3、8有6条。
--测试数据中最小值是2、3、4有1条。
--平均就是3.5 ,减去fc=1.5的话,fc=2
--得到结果为:
/*
e           f           g
----------- ----------- -----------
1           2           3
2           3           5
*/

------解决方案--------------------
探讨

好象不对

------解决方案--------------------
这是聚合函数求出来的都是 整型数值 你的代码改两个地方就ok了

SELECT e,f,g FROM 

(SELECT e,f,g,COUNT(*) fc FROM T1 GROUP BY e,f,g) a 

WHERE

(
(SELECT MAX(fc) FROM (SELECT COUNT(*) fc FROM T1 GROUP BY e,f,g) b)+
(SELECT MIN(fc) FROM (SELECT COUNT(*) fc FROM T1 GROUP BY e,f,g) b)
)/2.0-fc*1.0=1.5

红色的部分, 这样就可以把聚合函数的数值类型带一位小数,当你你也可以乘除1.00带两位小数,或者用cast转成decimal(18,2)之类的