56,677
社区成员
发帖
与我相关
我的任务
分享
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)
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)
mysql> select * from stat_clonetop;
+---------------+----------+
| 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)