Mysql查询例1
本文仅仅是在实际工作中需求(分析WMS日志)的一个简化例子。
// 数据库(log),表(table1),表数据为:
mysql> select * from log.table1;
+----------------+-------------+------------+
| c_ip | cs_uri_stem | c_playerid |
+----------------+-------------+------------+
| 192.168.12.100 | a.wmv | A |
| 192.168.12.100 | a.wmv | A |
| 192.168.12.100 | a.wmv | A |
| 192.168.12.100 | b.wmv | B |
| 192.168.12.111 | b.wmv | C |
| 192.168.12.111 | b.wmv | D |
+----------------+-------------+------------+
表描述:
c_ip:代表浏览视频的客户机IP地址;
cs_uri_stem:代表用户所看视频的文件名;
c_playerid:代表用户看视频所产生的ID号(同一IP看同一文件的ID可能相同,同一用户看不同视频的ID不可能相同,不同用户看不同视频ID也不可能相同;
//需求:统计cs_uri_stem有几种数据可能,每种可能值c_playerid有值的个数。(预期:有a.wmv和b.wmv两种值,a.wmv的c_playerid有一个值[A],b.wmv的c_playerid有三个值[B,C,D])
mysql> select cs_uri_stem,count(distinct c_playerid) from log.table1 group by cs_uri_stem;+-------------+----------------------------+
| cs_uri_stem | count(distinct c_playerid) |
+-------------+----------------------------+
| a.wmv | 1 |
| b.wmv | 3 |
+-------------+----------------------------+
2 rows in set (0.00 sec)
按个数由多到少排序
mysql> select cs_uri_stem,count(distinct c_playerid) from log.table1 group by cs_uri_stem order by count(distinct c_playerid) desc;+-------------+----------------------------+
| cs_uri_stem | count(distinct c_playerid) |
+-------------+----------------------------+
| b.wmv | 3 |
| a.wmv | 1 |
+-------------+----------------------------+
2 rows in set (0.00 sec)
简化表格,并取最大值
mysql> select cs_uri_stem,count(distinct c_playerid) as playeridnum from log.table1 group by cs_uri_stem order by playeridnum desc limit 1;
+-------------+-------------+
| cs_uri_stem | playeridnum |
+-------------+-------------+
| b.wmv | 3 |
+-------------+-------------+
1 row in set (0.00 sec)
同样可以统计一下每个WMV文件被多少个用户(c_ip)浏览过。
mysql> select cs_uri_stem,count(distinct c_ip) as c_ip_num from log.table1 group by cs_uri_stem order by c_ip_num desc ;+-------------+----------+
| cs_uri_stem | c_ip_num |
+-------------+----------+
| b.wmv | 2 |
| a.wmv | 1 |
+-------------+----------+
2 rows in set (0.00 sec)
对视频文件(cs_uri_stem)分类统计,查出每个视频文件被播放的次数以及每个视频被多少个用户浏览过,显示在一张表中。
mysql> select cs_uri_stem,count(distinct c_playerid) as playeridnum,count(distinct c_ip) as c_ip_num from log.table1 group by cs_uri_stem order by c_ip_num desc ;+-------------+-------------+----------+
| cs_uri_stem | playeridnum | c_ip_num |
+-------------+-------------+----------+
| b.wmv | 3 | 2 |
| a.wmv | 1 | 1 |
+-------------+-------------+----------+
2 rows in set (0.00 sec)
结果显示b.wmv的播放次大于播放人数,说明有人多次看过此视频。