dongliu8542 2016-06-03 06:32
浏览 71
已采纳

前3个结果的SQL查询

I've set of pages and each page has PageID and will record unique visitors information IP , Country , Browser , System and Date of visit

Here is sample of the database table

-------------------------------------------------------------------
| PageID |     IP     | Country |  Browser | System  |   Date     | 
-------------------------------------------------------------------
|    1   | 00-00-00-1 |   UK    |  Chrome  |   Win7  | 2016-05-22 |
-------------------------------------------------------------------
|    6   | 00-00-00-2 |  France | Firefox  |   Win8  | 2016-05-25 |
-------------------------------------------------------------------
|    1   | 00-00-00-3 |   USA   | Firefox  |  WinXP  | 2016-05-26 |
-------------------------------------------------------------------
|    8   | 00-00-00-4 |  Spain  |   Opera  | Android | 2016-05-28 |
-------------------------------------------------------------------
|    1   | 00-00-00-5 |    UK   |  Chrome  |  Win10  | 2016-06-01 |
-------------------------------------------------------------------
|    8   | 00-00-00-6 |  Italy  |  Safari  |Kardishan| 2016-06-02 |
-------------------------------------------------------------------
|    5   | 00-00-00-7 |   USA   | Unknown  | Android | 2016-06-02 |
-------------------------------------------------------------------
|    3   | 00-00-00-8 |    UK   |  Firefox |   Win7  | 2016-06-02 |
-------------------------------------------------------------------

My questions

1) How to make query to get top 3 countries where visitors comes from ? from the above sample the results should be :-

UK        3
USA       2
France    1

2) How to same query to get top 3 countries where visitors comes from where PageID = 1 from the above sample the results should be :-

UK    2
USA   1

~ that would helps me a lot to understand how statics can be generated, thanks

  • 写回答

2条回答 默认 最新

  • drh37116 2016-06-03 06:35
    关注

    This would give you the top 3 countries overall:

    SELECT Country, COUNT(*)
FROM mytable
GROUP BY Country 
ORDER BY COUNT(*) DESC LIMIT 3

    To get the top 3 for a specific PageID just add a WHERE PageID = 1 clause.

    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论
查看更多回答(1条)

报告相同问题？

悬赏问题

  • ¥50 导入文件到网吧的电脑并且在重启之后不会被恢复
  • ¥15 （希望可以解决问题）ma和mb文件无法正常打开，打开后是空白，但是有正常内存占用，但可以在打开Maya应用程序后打开场景ma和mb格式。
  • ¥20 ML307A在使用AT命令连接EMQX平台的MQTT时被拒绝
  • ¥20 腾讯企业邮箱邮件可以恢复么
  • ¥15 有人知道怎么将自己的迁移策略布到edgecloudsim上使用吗？
  • ¥15 错误 LNK2001 无法解析的外部符号
  • ¥50 安装pyaudiokits失败
  • ¥15 计组这些题应该咋做呀
  • ¥60 更换迈创SOL6M4AE卡的时候，驱动要重新装才能使用，怎么解决？
  • ¥15 让node服务器有自动加载文件的功能