mysql select 语句别名_mysql-在SELECT语句的其他列中使用别名

我想对SELECT查询和列做一些基本的数学运算.

SELECT Rider,

COUNT(if(Result = 1, Result, NULL)) AS "Firsts",

COUNT(if(Result = 2, Result, NULL)) AS "Seconds",

COUNT(if(Result = 3, Result, NULL)) AS "Thirds",

COUNT(if(Result = " ", Result, NULL)) AS "NP",

COUNT(*) AS "Total",

"Firsts"/"Total"*100 AS "S/R"

FROM meeting_master

WHERE RaceDayDate>="2012-01-01"

GROUP BY meeting_master.Rider

ORDER BY Firsts DESC

LIMIT 100

我的问题(和缺乏MySQL知识)是让S / R列在第7行中计算数学…

解决方法:

您不应该在SELECT中使用别名：

"Firsts"/"Total"*100 AS "S/R"

而是直接像这样计算：

COUNT(if(Result = 1, Result, NULL))/COUNT(*) * 100 AS "S/R"

因此,您的查询应为：

SELECT Rider,

COUNT(if(Result = 1, Result, NULL)) AS "Firsts",

COUNT(if(Result = 2, Result, NULL)) AS "Seconds",

COUNT(if(Result = 3, Result, NULL)) AS "Thirds",

COUNT(if(Result = " ", Result, NULL)) AS "NP",

COUNT(*) AS "Total",

COUNT(if(Result = 1, Result, NULL))/COUNT(*) * 100 AS "S/R"

FROM meeting_master

WHERE RaceDayDate>="2012-01-01"

GROUP BY meeting_master.Rider

ORDER BY Firsts DESC

LIMIT 100

标签：select,alias,mysql

来源： https://codeday.me/bug/20191127/2076396.html