mysql> SELECT mobile_num,COUNT(DISTINCT vlr) AS vlrcount FROM alarmdetail
GROUP BY mobile_num ORDER BY vlrcount DESC LIMIT 10;
+---------------+----------+
| mobile_num | vlrcount |
+---------------+----------+
| 8615868157030 | 4 |
| 8615888432117 | 4 |
| 8615888757011 | 3 |
| 8615824157681 | 3 |
| 8615888779740 | 3 |
| 8613806853520 | 3 |
| 8615824459241 | 3 |
| 8613566124713 | 3 |
| 8615888202117 | 3 |
| 8613566169670 | 3 |
+---------------+----------+
10 rows in set (0.00 sec)我想让查询的结果前面有1,2,3,4,...,10这样的编号。这个该怎么搞啊
请大家帮帮忙啊。在csdn上找到这样的方法,在我的这个结果中不行啊。mysql> SELECT (SELECT COUNT(1)+1 FROM alarmdetail b WHERE b.id
alarmdetail.id ) AS Rank, mobile_num,COUNT(DISTINCT vlr) AS vlrcount
-> FROM alarmdetail
-> GROUP BY mobile_num
-> ORDER BY vlrcount DESC LIMIT 10;
+------+---------------+----------+
| Rank | mobile_num | vlrcount |
+------+---------------+----------+
| 132 | 8615868157030 | 4 |
| 278 | 8615888432117 | 4 |
| 264 | 8613566233443 | 3 |
| 1147 | 8613567771731 | 3 |
| 259 | 8613806853520 | 3 |
| 1076 | 8615824145234 | 3 |
| 142 | 8615858155622 | 3 |
| 1384 | 8615868418510 | 3 |
| 361 | 8615869009887 | 3 |
| 337 | 8615869174411 | 3 |
+------+---------------+----------+
10 rows in set (0.48 sec)
GROUP BY mobile_num ORDER BY vlrcount DESC LIMIT 10;存为VIEW NEWTT:
SELECT A.mobile,A.vlrcount,COUNT(B.vlrcount) FROM NEWTT A INNER JOIN NEWTT B
ON A.mobile>=B.mobile GROUP BY A.mobile,A.vlrcount
SET @NUM=0;
SELECT @NUM:=@NUM+1,* FROM NEWTT
`id` int(10) unsigned NOT NULL auto_increment,
`alarmid` int(10) unsigned NOT NULL,
`mobile_num` varchar(20) NOT NULL,
`imsi_num` varchar(20) NOT NULL,
`vlr_index` tinyint(3) unsigned NOT NULL default '0',
`vlr` varchar(20) NOT NULL,
`reg_time` datetime NOT NULL,
`processed` varchar(10) default NULL,
`area` varchar(10) default NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1508 DEFAULT CHARSET=gbk CHECKSUM=1 DELAY_KEY_WRITE=1 ROW_FORMAT=DYNAMIC
+---------------+----------+
| mobile_num | vlrcount |
+---------------+----------+
| 8615888432117 | 4 |
| 8615868157030 | 4 |
| 8615888208211 | 3 |
| 8613566124713 | 3 |
| 8615888202117 | 3 |
| 8615869001551 | 3 |
| 8615858155622 | 3 |
| 8615888827446 | 3 |
| 8615858222611 | 3 |
| 8615868856244 | 3 |
+---------------+----------+
10 rows in set (0.02 sec)mysql> SELECT A.mobile_num,A.vlrcount,COUNT(B.vlrcount) FROM stat_clonetop
A INNER JOIN stat_clonetop B
-> ON A.mobile_num>=B.mobile_num GROUP BY A.mobile_num,A.vlrcount;
+---------------+----------+-------------------+
| mobile_num | vlrcount | COUNT(B.vlrcount) |
+---------------+----------+-------------------+
| 8613566124713 | 3 | 1 |
| 8615858155622 | 3 | 2 |
| 8615858222611 | 3 | 3 |
| 8615868157030 | 4 | 4 |
| 8615868856244 | 3 | 5 |
| 8615869001551 | 3 | 6 |
| 8615888202117 | 3 | 7 |
| 8615888208211 | 3 | 8 |
| 8615888432117 | 4 | 9 |
| 8615888827446 | 3 | 10 |
+---------------+----------+-------------------+
10 rows in set (0.00 sec)
MySQL中的ROWNUM的实现
(⊙v⊙)嗯 是我没有说明白啊
| mobile_num | vlrcount | COUNT(B.vlrcount) |
+---------------+----------+-------------------+
| 8613566124713 | 3 | 1 |
| 8615858155622 | 3 | 2 |
| 8615858222611 | 3 | 3 |
| 8615868157030 | 4 | 4 |
| 8615868856244 | 3 | 5 |
| 8615869001551 | 3 | 6 |
| 8615888202117 | 3 | 7 |
| 8615888208211 | 3 | 8 |
| 8615888432117 | 4 | 9 |
| 8615888827446 | 3 | 10 |
+---------------+----------+-------------------+
10 rows in set (0.00 sec)
在这里面 按照vlrcount从大到小排序,对应编号是1,2,3,10
+---------------+----------+
| mobile_num | vlrcount |
+---------------+----------+
| 8615888432117 | 4 | 1
| 8615868157030 | 4 | 2
| 8615888208211 | 3 | 3
| 8613566124713 | 3 | 4
| 8615888202117 | 3 | 5
| 8615869001551 | 3 | 6
| 8615858155622 | 3 | 7
| 8615888827446 | 3 | 8
| 8615858222611 | 3 | 9
| 8615868856244 | 3 | 10
+---------------+----------+
10 rows in set (0.02 sec)我想 获得这样的查询结果
SELECT @NUM:=@NUM+1,* FROM stat_clonetop
您说的是这样的方法吗 (第二种) 首先介绍一种用一条SQL语句完成的办法,原理是在结果中查询大于等于该纪录的纪录条数,就可以得到它的Rank了
Example:
USE pubs
SELECT COUNT(*) AS Rank, a1.au_lname, a1.au_fname
FROM authors a1, authors a2
WHERE a1.au_lname a1.au_fname >= a2.au_lname a2.au_fname
GROUP BY a1.au_lname, a1.au_fname
ORDER BY Rank
不过呢,这种方法有它的局限性,第一是性能不好,第二是如果存在相同的纪录,那么Rank就会出现并列的情况,比如出现两个2,但是没有3了
有没有别的方法呢?当然有的,SQL提供了一个IDENTITY Function,可以得到标识列的值,不过可惜的很的是,这个函数只能用于SELECT INTO语句,所以我们只好引入一个临时表了
Example:
USE pubs
SELECT IDENTITY(INT, 1, 1) AS Rank,au_lname,au_fname
INTO #tmp
FROM authors
SELECT * FROM #tmp
DROP TABLE #tmp
这种方法的性能和适用性都比第一种方法要强,不过缺点是必须通过几条SQL语句才能完成。
所以如果可能的话,一般还是建议在客户端完成这一操作
SELECT @NUM:=@NUM+1,* FROM stat_clonetop
SELECT *,@NUM:=@NUM+1 FROM stat_clonetop
恩 这个方法 可以啊 谢谢您但是 我这边是需要在stat_clonetop这个视图里面有1,2,3,..10 这样的编号
SELECT @NUM:=@NUM+1,* FROM stat_clonetop这种方法的缺陷就是它不是一句SQL,这是一个过程,所以也不可能通过VIEW来实现。
http://blog.csdn.net/ACMAIN_CHM/archive/2009/04/20/4095531.aspx中的3. 使用联接查询(笛卡尔积)
缺点,显然效率会差一些。4. 子查询
缺点,和联接查询一样,具体的效率要看索引的配置和MySQL的优化结果。