我有一个像这样的表:然后可以运行此查询以按ID获取订单数量:
mysql> describe orders;
+-------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+---------------------+------+-----+---------+----------------+
| orderID | bigint(20) unsigned | NO | PRI | NULL | auto_increment |
| itemID | bigint(20) unsigned | NO | | NULL | |
+-------------+---------------------+------+-----+---------+----------------+
这样,我就可以对表中每个
itemID
进行计数,如下所示:SELECT itemID, COUNT(*) as ordercount
FROM orders
GROUP BY itemID ORDER BY ordercount DESC;
我也想获取行号,所以我可以说
itemID=388
是第一行,234
是第二行,依此类推(基本上是订单的排名,而不仅仅是原始的计数)。我知道返回结果集后可以在Java中执行此操作,但是我想知道是否有一种方法可以纯粹在SQL中处理它。排名将其添加到结果集中,但顺序不正确:+--------+------------+
| itemID | ordercount |
+--------+------------+
| 388 | 3 |
| 234 | 2 |
| 3432 | 1 |
| 693 | 1 |
| 3459 | 1 |
+--------+------------+
#1 楼
看看这个。将查询更改为:
SET @rank=0;
SELECT @rank:=@rank+1 AS rank, itemID, COUNT(*) as ordercount
FROM orders
GROUP BY itemID
ORDER BY ordercount DESC;
SELECT @rank;
最后一个选择是您的数量。
评论
这会将排名添加到结果集中,但不会将它们排在正确的顺序中-更新了结果的问题
–乔治
10 Mar 26 '10在0:22
尝试保持ORDER BY ordercount DESC,然后将整个查询包装在另一个SELECT中,该SELECT从第一个中获取所有内容,但按等级列排序(在这种情况下为0)。
–迈克·希亚洛维奇(Mike Cialowicz)
10 Mar 26 '10在0:27
你能举个例子吗?我将如何包装选择?
–乔治
2010-3-27在18:21
查看swamibebop的答案
– thaddeusmt
2011年3月29日在1:22
@MikeCialowicz,这不起作用。有关正确答案,请参阅我的解决方案或Swamibebop的解决方案。
–起搏器
15年4月24日在11:45
#2 楼
SELECT @rn:=@rn+1 AS rank, itemID, ordercount
FROM (
SELECT itemID, COUNT(*) AS ordercount
FROM orders
GROUP BY itemID
ORDER BY ordercount DESC
) t1, (SELECT @rn:=0) t2;
评论
感谢您的澄清,这解决了我遇到的乱序问题。
– thaddeusmt
2011年3月29日在18:49
谢谢,这对我真的很有用:)我很惊讶没有一种更直接的方法可以从结果集中获取行“索引”……但是无论如何,这很方便。
–rat
2012年1月5日17:22
您可以通过更改SELECT \ @rn:= \ @ rn + 1 AS排名,itemID,ordercount,\ @ tot:= \ @ tot + ordercount中的第一条选择语句作为总计数来添加总计数递增的第四行。要定义\ @tot的初始值,应在t2之后添加:(SELECT \ @tot:= 0)t3。在每个\ @之前删除\,我必须使用它来规避mini-Markdown格式化。
–扬·埃哈特(Jan Ehrhardt)
2014年4月26日0:22
谁能解释t1和t2的相关性?
–讨厌
2014年4月30日,下午3:11
@ Jared,MySQL语法只需要一些内容即可。它可以是任何东西,甚至x和y。
–起搏器
15年4月24日在11:10
#3 楼
Swamibebop的解决方案有效,但是通过利用table.*
语法,我们可以避免重复内部select
的列名,并获得更简单/更简短的结果:会给您:SELECT @r := @r+1 ,
z.*
FROM(/* your original select statement goes in here */)z,
(SELECT @r:=0)y;
评论
您是否偶然知道为什么在select语句中使用@r:= @r + 1是可行的,但是如果它在带有声明r int的存储过程中;设置r = 0 ;,它会抱怨(在r:= r +1上)?
– Dan M.
16年12月17日在17:03
@Pacerier,第二个选择在某处是否保证行的顺序?我知道,没有顺序by子句的select返回的行的顺序在任何地方都不能保证,最外面的select就是这样,尽管它是从内部有序的select中选择的,所以可能是一个例外。但是,如果不是这样,我将看不到这是正确的解决方案,因为它具有与Chibu的Mike相同的缺陷-无法保证选择的顺序会经过记录并编号。
– Dan M.
16年12月17日在17:17
您是否知道为什么只要不在字段列表中时ORDER BY都不起作用?查看我的结果:hastebin.com/aluqefunoy.rb
–冬天
18-10-24在20:32
#4 楼
您可以使用MySQL变量来做到这一点。这样的事情应该起作用(尽管它包含两个查询)。SELECT 0 INTO @x;
SELECT itemID,
COUNT(*) AS ordercount,
(@x:=@x+1) AS rownumber
FROM orders
GROUP BY itemID
ORDER BY ordercount DESC;
评论
小心,这是行不通的,因为order by是在变量@x被求值之后发生的。通过订购其他栏来尝试进行实验。还可以同时测试desc和asc。您会看到很多次它们将失败,并且只有几次才能运行,这是纯运气,这是由于原始“选择”的顺序与order by的顺序相同。请参阅我的解决方案和/或Swamibebop的解决方案。
–起搏器
15年4月24日在11:43
@Pacerier您确定吗?我在另一个示例(基本上是从数字列中进行选择,然后根据其顺序进行编号)中的类似查询感到疲倦,似乎如果我按var / row num进行了排序,那么当它更改结果行的顺序时,但每个数字都有相同的行号。但是,如果按数字列排序,则ASC / DESC会更改这些数字的编号顺序(从最小到最大,反之亦然)。因此,在这种情况下,似乎先评估了by。
– Dan M.
16 Dec 17'在17:42
#5 楼
现在已在MySQL 8.0和MariaDB 10.2中内置:SELECT
itemID, COUNT(*) as ordercount,
ROW_NUMBER OVER (PARTITION BY itemID ORDER BY rank DESC) as rank
FROM orders
GROUP BY itemID ORDER BY rank DESC
评论
供将来参考:如果要从等级1到等级5进行排序,请使用ORDER BY等级ASC(按ASCending顺序按等级排序)。我想这就是您的意思,但排序不正确MySQL中ROW_NUMBER()的可能重复