查詢前50%的資料

壹頁書發表於2017-11-15
實驗環境:
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; 


c 等於10,7,9的數量有  11個，超過了半數。
超過半數的 item資訊。

通用方式,不依賴具體資料庫。


  1. select t5.*,t6.*,ifnull(round(t5.sumtotal/t6.total,2),0) result from (
  2.     select t3.rn,t3.total,t3.c,sum(ifnull(t4.total,0)) sumtotal from (
  3.         select
  4.         t1.*,
  5.         (
  6.             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 (
  7.                 select c,count(*) total from t group by c
  8.             ) t2
  9.         ) rn
  10.         from(
  11.             select c,count(*) total from t group by c order by total desc ,c
  12.         ) t1
  13.     ) t3
  14.     left join(
  15.         select
  16.         t1.*,
  17.         (
  18.             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 (
  19.                 select c,count(*) total from t group by c
  20.             ) t2
  21.         ) rn
  22.         from(
  23.             select c,count(*) total from t group by c order by total desc ,c
  24.         ) t1
  25.     ) t4 on (t3.rn> t4.rn)
  26.     group by t3.rn,t3.total,t3.c
  27. ) t5
  28. left join(
  29.     select count(*) total from t
  30. ) t6 on(1=1)
  31. where ifnull(round(t5.sumtotal/t6.total,2),0)<=0.5;
查詢結果:


繫結MySQL的實現

  1. select * from (
  2.     select t3.*,case when result<=0.5 then 1 when result>0.5 and @b=-1 then @b:=1 else null end r from (
  3.         select t1.*,round((@a:=@a+t1.total)/t2.total,2) result from (
  4.             select c,count(*) total from t,(select @a:=0,@b:=-1) vars group by c order by 2 desc
  5.         ) t1
  6.         left join(
  7.             select count(*) total from t
  8.         ) t2 on(1=1)
  9.     ) t3 order by result
  10. ) t4 where r=1;

結果:

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29254281/viewspace-2147318/，如需轉載，請註明出處，否則將追究法律責任。

相關文章