从SQL表中选择行的百分比? [英] Select percentage of rows from SQL table?
问题描述
我有一个带有PHP脚本的网站,该脚本内部有一个SQL查询,返回由JavaScript文件访问的数据.该数据是一个庞大的航班数据列表,我需要能够在指定的任何给定日期中随机选择(比如说)全部航班的40%.为了论证,让我们这样说:
I've got a site with a PHP script, this script has an SQL query inside returning data that is accessed by a JavaScript file. The data is a huge list of flight data, and I need to be able to select (let's say) a random 40% of the total flights for any given day specified. For arguments sake lets put it like this:
$query = "SELECT * FROM `Flight_Data` WHERE DepDateTimeUTC LIKE '%1/1/14%' ";
我知道要获得随机的行数,您只需使用ORDER BY RAND() LIMIT 40'
,理想情况下我想说LIMIT 40%
,但这行不通.
I understand that to get a random number of rows you simply use ORDER BY RAND() LIMIT 40'
and ideally I want to say LIMIT 40%
but that doesn't work.
$query = "SELECT * FROM `Flight_Data` WHERE DepDateTimeUTC LIKE '%1/1/14%' ";
$row = mysqli_fetch_row($result);
$total = $row[0];
$percent = $total * 0.40;
$query = "SELECT * FROM `Flight_Data` WHERE DepDateTimeUTC LIKE '%1/1/14%' LIMIT . $percent ";
推荐答案
您可以COUNT
所有记录,然后像这样计算所需的%
:
You can COUNT
all records and then calculate the %
you need like this:
$query = "SELECT COUNT(*) FROM `Flight_Data` WHERE DepDateTimeUTC LIKE '%1/1/14%' ";
$result = mysqli_query($connection,$query);
$row = mysqli_fetch_row($result));
$total = $row[0];
$percent = intval($total * 0.40);
$query = "SELECT * FROM `Flight_Data` WHERE DepDateTimeUTC LIKE '%1/1/14%' LIMIT ". $percent;
//execute your query....
这篇关于从SQL表中选择行的百分比?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!