将查询结果限制为mysql中的前10位

问题描述：

我试图将以下查询结果限制为仅前10个响应。到目前为止，查询完全符合我的要求 - 即按count排列按*系统列表排序的*系统。我获得了所有31个回复，但我只想要前10个回复。我试图创建一个虽然DO /结束While循环，但我无法弄清楚到底在何处放置在下面的代码：将查询结果限制为mysql中的前10位

$sql = " 
SELECT HelpDesk 
    , COUNT(*) as NumSys 
    FROM `systems_17-18` 
WHERE HelpDesk <>'' 
    AND HelpDesk <> 'NONE'  
GROUP BY HelpDesk 
ORDER 
    by NumSys DESC 
"; 
$result = $conn->query($sql); 
$mycount = 0; 
if ($result->num_rows > 0) { 
// output data of each row 
echo "<table class='uk-table uk-table-condensed uk-table-hover uk-table-striped'>";   
while($row = $result->fetch_assoc()) 
{ 
$mycount ++; 

echo "<tr>" ; 
    echo "<td>"; 

    echo "<a href=" . $row['webaddress'] . " >"; 
    echo $row['SchoolDistrict'] . "</a>"; 
    echo "</td><td># " . $mycount . " " . $row['HelpDesk'] . " used by " . $row[NumSys] . " district(s)"; 
echo "</td></tr>"; 
} 

echo "</table>"; 
}

（注：上面的代码正确生成下面的数据，但我怎么会限制列表中只排名前10的反应用while循环ENDWHILE）在加利福尼亚州

学区使用各种帮助台系统：？？

1 MyTechDesk used by 52 district(s) 
2 SchoolDude used by 40 district(s) 
3 WebHelpDesk used by 29 district(s) 
4 ZenDesk used by 15 district(s) 
5 SpiceWorks used by 14 district(s) 
6 KACE1000/2000 used by 13 district(s) 
7 FreshDesk used by 8 district(s) 
8 Track-IT! used by 6 district(s) 
9 Custom used by 6 district(s) 
10 HEAT (Help Desk) used by 6 district(s) 
11 SysAid used by 5 district(s) 
12 ServiceDeskPlus used by 4 district(s) 
13 GroupLink HelpDesk used by 4 district(s) 
14 Altiris used by 4 district(s) 
15 GLPI used by 3 district(s) 
16 HESK used by 3 district(s) 
17 HelpDesk used by 2 district(s) 
18 Cherwell used by 2 district(s) 
19 OPRAS used by 2 district(s) 
20 Manage Engine Service Desk used by 2 district(s) 
21 Samanage used by 1 district(s) 
22 Absolute used by 1 district(s) 
23 OTRS used by 1 district(s) 
24 SherpaDesk used by 1 district(s) 
25 Applied HelpDesk used by 1 district(s) 
26 Public School Works used by 1 district(s) 
27 iTop used by 1 district(s) 
28 Connectwise used by 1 district(s) 
29 Asana used by 1 district(s) 
30 RT/SRTS used by 1 district(s) 
31 OSTicket used by 1 district(s)

你有没有考虑过使用[MySQL的LIMIT子句（HTTP：// www.mysqltutorial.org/mysql-limit.aspx）。 –

有很多问题显示如何做到这一点，这应该作为一个副本关闭。 –

对于未来，考虑搜索类似[“mysql中的前10位结果”]（https://www.w3schools.com/sql/sql_top.asp）。对于任何你知道如何在MS SQL谷歌如何在MySQL – Ibu

答

您ORDER BY语句后添加LIMIT 10。

$sql = "SELECT HelpDesk, COUNT(*) as NumSys FROM `schoolt7_systems`.`systems_17-18` WHERE HelpDesk <>'' AND HelpDesk <> 'NONE'  GROUP BY HelpDesk ORDER by NumSys DESC LIMIT 10";

编辑：从MS SQL服务器被更换到MySQL

参考：https://www.w3schools.com/sql/sql_top.asp

谢谢大家。对不起，重复的条目。我真的尝试在发布之前寻找答案！ –

答

使用LIMIT：

SELECT 
    HelpDesk, 
    COUNT(*) AS NumSys 
FROM `schoolt7_systems`.`systems_17-18` 
WHERE 
    HelpDesk <>'' AND 
    HelpDesk <> 'NONE' 
GROUP BY 
    HelpDesk 
ORDER by NumSys DESC 
LIMIT 10    -- add this to your query

将查询结果限制为mysql中的前10位

相关推荐