mysql值大于值数目的百分比

7rfyedvj 于 2021-06-20 发布在 Mysql

关注(0)|答案(2)|浏览(640)

关闭。这个问题需要细节或清晰。它目前不接受答案。
**想改进这个问题吗？**通过编辑这个帖子来添加细节并澄清问题。

两年前关门了。
改进这个问题
我想找到年龄大于某一年所有值的n%的名字。这是table：

mysql> select * from cust;
+------+------+------+
| name | age  | year |
+------+------+------+
| toni |   21 | 2016 |
| robi |   22 | 2016 |
| deni |   23 | 2016 |
| jeki |   24 | 2016 |
| yodi |   25 | 2016 |
| rino |   26 | 2016 |
| goli |   27 | 2016 |
| tobi |   28 | 2016 |
| lemi |   29 | 2016 |
| lora |   30 | 2016 |
| riko |   25 | 2017 |
| soni |   26 | 2017 |
| bino |   27 | 2017 |
| kola |   28 | 2017 |
| zoki |   29 | 2017 |
| mera |   30 | 2017 |
| noki |   31 | 2017 |
| peni |   32 | 2017 |
| vino |   33 | 2017 |
| heri |   34 | 2017 |
+------+------+------+

ddl地址：

create table tbl ( name varchar(10), age int, year  int);
insert into tbl values
('toni', 21, 2016 ),
('robi', 22, 2016 ),
('deni', 23, 2016 ),
('jeki', 24, 2016 ),
('yodi', 25, 2016 ),
('rino', 26, 2016 ),
('goli', 27, 2016 ),
('tobi', 28, 2016 ),
('lemi', 29, 2016 ),
('lora', 30, 2016 ),
('riko', 25, 2017 ),
('soni', 26, 2017 ),
('bino', 27, 2017 ),
('kola', 28, 2017 ),
('zoki', 29, 2017 ),
('mera', 30, 2017 ),
('noki', 31, 2017 ),
('peni', 32, 2017 ),
('vino', 33, 2017 ),
('heri', 34, 2017 );

我想找到2017年的名字，2016年的年龄超过所有年龄的60%。我做过这样的事

select name from cust where year=2017 and age>
    (SELECT age
    FROM    (
        SELECT cust.*, @counter := @counter +1 AS counter
        FROM (select @counter:=0) AS initvar, cust where year=2016
        ORDER BY age DESC   
    ) AS X
    where counter <= (60/100 * @counter) 
    ORDER BY age DESC);

我知道这是错误的，但你知道我的意思是比较主查询和子查询之间的值，但我希望子查询多于1行。有没有一个解决方案是有效的，但与上述类似？
编辑：这是我想要的输出：

+------+
| name |
+------+
| soni |
| bino |
| kola |
| zoki |
| mera |
| noki |
| peni |
| vino |
| heri |
+------+

sql mysql Percentage

来源：https://stackoverflow.com/questions/51570420/mysql-value-greater-than-percent-of-number-of-values

2条答案

按热度按时间

8i9zcol21#

要在mysql中找到2016年的第60个百分位（8之前）：

select min(age)
from (select c.*, (@rn := @rn + 1) as rn
      from (select c.*
            from cust c
            where year = 2016
            order by age
           ) c cross join
           (select @rn := 0) params
     ) c
where rn >= @rn * 0.6;

要找到2017年的相应年龄，您可以 join 这包括：

select c.* from cust c join
     (select min(age) as age_2016
      from (select c.*, (@rn := @rn + 1) as rn
            from (select c.*
                  from cust c
                  where year = 2016
                  order by age
                 ) c cross join
                 (select @rn := 0) params
           ) c
      where rn >= @rn * 0.6
     ) cc
     on c.age >= cc.age_2016 where year = 2017;

赞(0）回复(0）举报 2021-06-21

vatpfxk52#

尝试以下简单查询：

select name
from tbl t
where (select count(*) from tbl where year = 2016 and age <= t.age) /
      (select count(*) from tbl where year = 2016) >= 0.6 
  and year = 2017;

在 where 你只需将2016年的年轻人数量除以2016年的所有人，就可以得到百分比( 0.0 至 1.0 ). 所以，只需要取那些大于或等于 0.6 .

赞(0）回复(0）举报 2021-06-20

我来回答

mysql值大于值数目的百分比

2条答案

相关问题

热门标签

最新问答