mysql - 如何在mysql中创建循环行值

我的桌子上的名字是df：

device_code             gender
Xiaomi Redmi Note 5A    male
samsung SM-G532G        female
Apple iPhone5           female
OPPO A37f               male
LENOVO Lenovo A6000     male
Apple iPhone5           male

我想获取每一行中的值，并将其放入此查询的device_code中的select查询中：

select log10(total_redmi / total_male) as lmp_redmi
from (
         select sum(case
                        when gender = 'male' and device_code = 'Xiaomi Redmi Note 5A' then 1
                        else 0 end)                                                     as total_redmi,
                sum(case when gender = 'male' and gender is not null then 1 else 0 end) as total_male
         from df) x

因此，我没有将device_code中的每一行值手动输入到上述查询中。
在我的查询上面的输出像：

lmp_redmi     lmp_SMG532G   ... etc
-0,602059991    0

但我期望这样的输出：

                                  lmp
Xiaomi Redmi Note 5A    -0,602059991
samsung SM-G532G         0
Apple iPhone5,3         -0,602059991
OPPO A37f               -0,602059991
LENOVO Lenovo A6000     -0,602059991

最佳答案

我想您正在寻找这样的东西：

select *, case when total_device_male > 0
               then log10(total_device_male/total_male)
          end as lmp_redmi
from (
  select device_code, sum(case when gender = 'male' then 1 else 0 end) as total_device_male,
    (select count(*) from df where gender = 'male') as total_male
  from df
  group by device_code
)x

关于mysql - 如何在mysql中创建循环行值，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/57864206/