如何在查询中使用别名以在mysql中创建条件 [英] How to use aliases in query for making conditions in mysql

问题描述

我有 user 表，如下所示

user_id    name    gender    age
--------------------------------
1          AAA      Male      45
2          BBB      Female    22
3          CCC      Male      47
.................................
..............................

我想获取用户总数和男性用户总数以及男性和女性用户的百分比

I want to get total no of users and total no of male users ans percent for male and female users

select count(*) as total , SUM(IF(v.gender = 'Male',1,0)) as totalMale ,
   SUM(IF(v.gender = 'Female',1,0)) as totalFemale , totalMale/total *100 ,
   totalFeMale/total *100 from user;

使用别名计算男性和女性百分比时，此查询不起作用。
我遇到类似 unknown column ..........

This query is not working when using aliases to calculate male and female percent. I am getting the error like unknown columns..........

select count(*) as total , SUM(IF(v.gender = 'Male',1,0)) as totalMale ,
SUM(IF(v.gender = 'Female',1,0)) as totalFemale , SUM(IF(v.gender = 'Male',1,0))/count(*) *100 ,SUM(IF(v.gender = 'Female',1,0))/count(*) *100 from user;

但这是可行的。

但是这是我使用 SUM（IF（v.gender ='Female'，1,0）） 2次。我认为这会降低性能。

But is this i used SUM(IF(v.gender = 'Female',1,0)) 2 times.I think it will degrade the performance.

我不能在我的情况下使用别名吗？

Can't i use aliases in my situation ?

预先感谢...

推荐答案

您的查询就可以了。您不能在选定级别使用别名。您唯一的选择是拥有一个派生表，但这确实会降低性能。

Your query is just fine. You can't use an alias at a select level. Your only option would be to have a derived table but that will indeed degrade performance.

提高查询性能的一件事就是将Gender列更改为一个提示：布尔值或char列。

Just one thing to improve the query performance would be to change the Gender column into just a boolean or char column.

提示：MySQL中所有非空布尔值比较都解析为1（true）和0（false），因此您可以通过这种方式简化查询：

Tip: All non-null boolean comparisons in MySQL resolve to 1 (true) and 0 (false), so you could simplify your query this way:

select
    count(*) total,
    SUM(gender = 'Male') totalMale,
    SUM(gender = 'Female') totalFemale,
    SUM(gender = 'Male') / count(*) * 100 percentageMale,
    SUM(gender = 'Female') / count(*) * 100 percentageFemale
from user

这篇关于如何在查询中使用别名以在mysql中创建条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！