实验环境:
c 等于10,7,9的数量有 11个,超过了半数。
超过半数的 item信息。
通用方式,不依赖具体数据库。
查询结果:
绑定MySQL的实现
结果:
create table t (c int);
insert into t values
(15),(7),(9),(10),(7),(8),(20),(16),(9),(19),
(14),(10),(11),(10),(10),(12),(7),(10),(7),(9);
commit;
insert into t values
(15),(7),(9),(10),(7),(8),(20),(16),(9),(19),
(14),(10),(11),(10),(10),(12),(7),(10),(7),(9);
commit;
c 等于10,7,9的数量有 11个,超过了半数。
超过半数的 item信息。
通用方式,不依赖具体数据库。
- select t5.*,t6.*,ifnull(round(t5.sumtotal/t6.total,2),0) result from (
- select t3.rn,t3.total,t3.c,sum(ifnull(t4.total,0)) sumtotal from (
- select
- t1.*,
- (
- select count(case when t2.total>t1.total then 1 when t2.total=t1.total and t2.c<t1.c then 1 else null end)+1 from (
- select c,count(*) total from t group by c
- ) t2
- ) rn
- from(
- select c,count(*) total from t group by c order by total desc ,c
- ) t1
- ) t3
- left join(
- select
- t1.*,
- (
- select count(case when t2.total>t1.total then 1 when t2.total=t1.total and t2.c<t1.c then 1 else null end)+1 from (
- select c,count(*) total from t group by c
- ) t2
- ) rn
- from(
- select c,count(*) total from t group by c order by total desc ,c
- ) t1
- ) t4 on (t3.rn> t4.rn)
- group by t3.rn,t3.total,t3.c
- ) t5
- left join(
- select count(*) total from t
- ) t6 on(1=1)
- where ifnull(round(t5.sumtotal/t6.total,2),0)<=0.5;
绑定MySQL的实现
- select * from (
- select t3.*,case when result<=0.5 then 1 when result>0.5 and @b=-1 then @b:=1 else null end r from (
- select t1.*,round((@a:=@a+t1.total)/t2.total,2) result from (
- select c,count(*) total from t,(select @a:=0,@b:=-1) vars group by c order by 2 desc
- ) t1
- left join(
- select count(*) total from t
- ) t2 on(1=1)
- ) t3 order by result
- ) t4 where r=1;
结果:
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29254281/viewspace-2147318/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29254281/viewspace-2147318/