PHP MySql 百分比 [英] PHP MySql percentage

问题描述

我的问题是关于百分比的，我不是专家，所以我会尽量以更好的方式解释.

My question is about percentages, I'm not an expert so I will try to explain in the better possible way.

我的 mysql 服务器中有一张表，其中有 700 条记录，就像这样

I have a table with, let say 700 records, in my mysql server, something like this

+-------+---------+----------+-------+
| Name  | country | language | Birth |
+-------+---------+----------+-------+
| Lucy  | UK      | EN       | 1980  |
| Mari  | Canada  | FR       | 1990  |
| Gary  | Canada  | EN       | 1982  |
| Stacy | Jamaica | EN       | 1986  |
| Joao  | Brasil  | PT       | 1984  |
+-------+---------+----------+-------+

所以我查询了 1980 年到 1985 年之间的所有记录，结果将是:

So I query all the records that are between 1980 and 1985 and the result will be:

+------+---------+----------+-------+
| Name | country | language | Birth |
+------+---------+----------+-------+
| Lucy | UK      | EN       | 1980  |
| Gary | Canada  | EN       | 1982  |
| Joao | Brasil  | PT       | 1984  |
+------+---------+----------+-------+

我想从这个结果中得到:

and from this result I would like to obtain:

1. 这些年之间每种语言出现的百分比

1. the percentage of appearance of every languages between those years

EN = 75% (3 is the total in this case)
PT = 25%

在结果表中看到的每个国家/地区的出现百分比

the percentage of appearance of every country that is seen in the resulting table

UK = 33%
Canada = 33%
Brasil = 33%

我的意思是如何将结果转换为变量以在最终函数中使用它们.

I mean how can I convert the results in variables to use them in the final function.

推荐答案

这可能有效，但大致如下:

This may work, but something along the line of:

set @total_rows = (SELECT COUNT(*) FROM table WHERE Birth between 1980 and 1985);

SELECT language, percentage
FROM (
    SELECT language, concat(count(language)/@total_rows, "%") AS percentage 
    FROM table WHERE Birth between 1980 and 1985
)

这篇关于PHP MySql 百分比的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！