我正在计数要在饼图中显示的内容。我只想按数字显示最高的6个计数,其实际为InvestmentType,其余的作为“ Others”显示为investmentType。
SELECT i.investment_type AS investmentType,
COUNT(*) AS investmentCount
FROM investment i,
vertical v
WHERE v.vertical_name = i.investment_type
AND v.type = 'STR'
AND i.status_funding = 'o'
GROUP
BY i.investment_type
ORDER
BY investmentCount desc
上面的查询给了我一个结果
通过将
limit 6
添加到查询中,我得到我需要的是带有investmentType“ Others”和investmentCount“ 7”的另一行。
最佳答案
您可能想尝试一下:
SELECT i.investment_type as investmentType,COUNT(*) as investmentCount FROM investment i,vertical v
WHERE v.vertical_name =i.investment_type AND v.type='STR' AND i.status_funding ='o'
group by i.investment_type order by investmentCount desc
limit 6
UNION
SELECT "others" as investmentType, SUM(othersInvestmentCount) as investmentCount FROM (
SELECT COUNT(*) as othersInvestmentCount FROM investment i,vertical v
WHERE v.vertical_name =i.investment_type AND v.type='STR' AND i.status_funding ='o'
group by i.investment_type order by investmentCount desc
limit 6, 4294967296
)
我没有测试此查询,如果发现语法问题,可以对其进行编辑。涉及到三个实际查询,但不要太慢(如果不需要更快,则无需尝试更快)。
我假设您的数据库中的记录少于2 ^ 32,对于MySQL数据库来说,这似乎是一个非常合理的假设(但是,如果您感到不安全,只需将其替换为2 ^ 64)。
关于mysql - 限制数量,剩下的作为“其他”,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/26114510/