MySQIi选择数量和按数量排序 [英] MySQIi select count and order by count

问题描述

我的表看起来像这样(这只是一个例子):

My table looks like this (this is just an example):

id    name    title    time
----------------------------
1     name1   title1   1000
2     name1   title1   1001
3     name1   title1   1002
4     name2   title2   1003
5     name2   title2   1004
6     name3   title3   1005
7     name4   title4   1006
8     name4   title4   1007
9     name4   title4   1008
10    name4   title4   1009

我想编写一条SQL语句来选择每个名称的计数以及与之相关的信息.时间确实是UNIX的时间戳，但是我只是为了使这个问题变得简单而改变了.

I want to write a SQL statement to select the count of each name as well as the info related to it. Time is really a UNIX timestamp, but I just changed to make simple for this question.

// This is will get me the trending posts for only the last minute
$time_in_past = time() - 60;
$mysqli->query(
    "SELECT COUNT(`id`) FROM `trending`........WHERE `time` < '$time_in_past'"
);

我想在PHP中使用while()语句获取结果后是这样的:

I want to results after fetching with while() statement in PHP to be like this:

name: name4 count: 4 title: title4
name: name1 count: 3 title: title1
name: name2 count: 2 title: title2
name: name3 count: 1 title: title3

我希望按COUNT个进行排序，但仍然得到10个结果(在本示例中只有4个).

I want them to be ordered by COUNT of each one while still getting 10 results (in this example there are only 4).

推荐答案

看起来您可以通过简单的GROUP BY逃脱，就像这样:

Looks like you could get away with a simple GROUP BY, something like this:

SELECT name, COUNT(*), title
FROM trending
GROUP BY name, title
ORDER BY COUNT(*) DESC;

由于您的name和title外观没有任何区别，因此将它们组合在一起不会给您更多的行. DESC的顺序为您提供了相反的计数.

Since your name and title appear don't ever differ, grouping on those together will not give you any more rows. The ordering by DESC give you the count in a reverse order.

这篇关于MySQIi选择数量和按数量排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！