每月注册人数统计
代码: 全选
mysql> select Year(from_unixtime(user_regdate)) as Year, Month(from_unixtime(user_regdate)) as Month, count(*) as Total from phpbb_users group by Year,Month;
+------+-------+-------+
| Year | Month | Total |
+------+-------+-------+
| 2005 | 3 | 35 |
| 2005 | 4 | 234 |
| 2005 | 5 | 265 |
| 2005 | 6 | 254 |
| 2005 | 7 | 312 |
| 2005 | 8 | 407 |
| 2005 | 9 | 445 |
| 2005 | 10 | 748 |
| 2005 | 11 | 810 |
| 2005 | 12 | 893 |
| 2006 | 1 | 674 |
| 2006 | 2 | 750 |
| 2006 | 3 | 1139 |
| 2006 | 4 | 1327 |
| 2006 | 5 | 1222 |
| 2006 | 6 | 1627 |
| 2006 | 7 | 1617 |
| 2006 | 8 | 1781 |
| 2006 | 9 | 1932 |
| 2006 | 10 | 2323 |
| 2006 | 11 | 2720 |
| 2006 | 12 | 2288 |
| 2007 | 1 | 2353 |
| 2007 | 2 | 1760 |
| 2007 | 3 | 2761 |
| 2007 | 4 | 3793 |
| 2007 | 5 | 4000 |
| 2007 | 6 | 3670 |
| 2007 | 7 | 3451 |
| 2007 | 8 | 3994 |
| 2007 | 9 | 3919 |
| 2007 | 10 | 4808 |
| 2007 | 11 | 4953 |
| 2007 | 12 | 5130 |
| 2008 | 1 | 3893 |
| 2008 | 2 | 2391 |
| 2008 | 3 | 4153 |
| 2008 | 4 | 5778 |
| 2008 | 5 | 5873 |
| 2008 | 6 | 4739 |
| 2008 | 7 | 4640 |
| 2008 | 8 | 5206 |
| 2008 | 9 | 6596 |
| 2008 | 10 | 12381 |
| 2008 | 11 | 11301 |
| 2008 | 12 | 6898 |
| 2009 | 1 | 5026 |
| 2009 | 2 | 5379 |
| 2009 | 3 | 7564 |
| 2009 | 4 | 7115 |
| 2009 | 5 | 6836 |
| 2009 | 6 | 5256 |
| 2009 | 7 | 5793 |
| 2009 | 8 | 6063 |
| 2009 | 9 | 6454 |
| 2009 | 10 | 6399 |
| 2009 | 11 | 7264 |
| 2009 | 12 | 3801 |
| 2010 | 1 | 5293 |
| 2010 | 2 | 3216 |
| 2010 | 3 | 5564 |
| 2010 | 4 | 5656 |
| 2010 | 5 | 7204 |
| 2010 | 6 | 5166 |
| 2010 | 7 | 4964 |
| 2010 | 8 | 5092 |
| 2010 | 9 | 4799 |
| 2010 | 10 | 6660 |
| 2010 | 11 | 6576 |
| 2010 | 12 | 6100 |
| 2011 | 1 | 5070 |
| 2011 | 2 | 3638 |
| 2011 | 3 | 6872 |
| 2011 | 4 | 5653 |
| 2011 | 5 | 5134 |
| 2011 | 6 | 3893 |
| 2011 | 7 | 3888 |
| 2011 | 8 | 4264 |
| 2011 | 9 | 4220 |
| 2011 | 10 | 5109 |
| 2011 | 11 | 4833 |
| 2011 | 12 | 3939 |
| 2012 | 1 | 2748 |
| 2012 | 2 | 3917 |
| 2012 | 3 | 4586 |
| 2012 | 4 | 3816 |
| 2012 | 5 | 3410 |
| 2012 | 6 | 2865 |
| 2012 | 7 | 3477 |
| 2012 | 8 | 9167 |
| 2012 | 9 | 11280 |
| 2012 | 10 | 18057 |
| 2012 | 11 | 9205 |
| 2012 | 12 | 4384 |
| 2013 | 1 | 3317 |
| 2013 | 2 | 2078 |
| 2013 | 3 | 4536 |
| 2013 | 4 | 3829 |
| 2013 | 5 | 3188 |
| 2013 | 6 | 2201 |
| 2013 | 7 | 2300 |
| 2013 | 8 | 1893 |
| 2013 | 9 | 1813 |
| 2013 | 10 | 2192 |
| 2013 | 11 | 2055 |
| 2013 | 12 | 1632 |
| 2014 | 1 | 1535 |
| 2014 | 2 | 1154 |
| 2014 | 3 | 1675 |
| 2014 | 4 | 5203 |
| 2014 | 5 | 5054 |
| 2014 | 6 | 3467 |
| 2014 | 7 | 4694 |
| 2014 | 8 | 9286 |
| 2014 | 9 | 2853 |
| 2014 | 10 | 2924 |
| 2014 | 11 | 2031 |
| 2014 | 12 | 2655 |
| 2015 | 1 | 1556 |
| 2015 | 2 | 821 |
| 2015 | 3 | 1123 |
| 2015 | 4 | 1052 |
| 2015 | 5 | 913 |
| 2015 | 6 | 793 |
| 2015 | 7 | 798 |
| 2015 | 8 | 787 |
| 2015 | 9 | 851 |
| 2015 | 10 | 847 |
| 2015 | 11 | 719 |
| 2015 | 12 | 595 |
| 2016 | 1 | 802 |
| 2016 | 2 | 1285 |
| 2016 | 3 | 1622 |
| 2016 | 4 | 1050 |
| 2016 | 5 | 1436 |
| 2016 | 6 | 962 |
| 2016 | 7 | 970 |
| 2016 | 8 | 900 |
| 2016 | 9 | 966 |
| 2016 | 10 | 874 |
| 2016 | 11 | 591 |
| 2016 | 12 | 621 |
| 2017 | 1 | 449 |
| 2017 | 2 | 512 |
| 2017 | 3 | 613 |
| 2017 | 4 | 421 |
| 2017 | 5 | 410 |
| 2017 | 6 | 350 |
| 2017 | 7 | 775 |
| 2017 | 8 | 394 |
| 2017 | 9 | 569 |
| 2017 | 10 | 298 |
| 2017 | 11 | 282 |
| 2017 | 12 | 288 |
| 2018 | 1 | 768 |
| 2018 | 2 | 271 |
| 2018 | 3 | 355 |
| 2018 | 4 | 388 |
| 2018 | 5 | 386 |
| 2018 | 6 | 313 |
| 2018 | 7 | 324 |
| 2018 | 8 | 319 |
| 2018 | 9 | 324 |
| 2018 | 10 | 312 |
| 2018 | 11 | 282 |
| 2018 | 12 | 292 |
| 2019 | 1 | 235 |
| 2019 | 2 | 209 |
| 2019 | 3 | 350 |
| 2019 | 4 | 300 |
| 2019 | 5 | 266 |
| 2019 | 6 | 238 |
| 2019 | 7 | 246 |
| 2019 | 8 | 205 |
| 2019 | 9 | 165 |
| 2019 | 10 | 221 |
| 2019 | 11 | 271 |
| 2019 | 12 | 190 |
| 2020 | 1 | 199 |
| 2020 | 2 | 223 |
| 2020 | 3 | 271 |
| 2020 | 4 | 226 |
+------+-------+-------+
182 rows in set (3.92 sec)
代码: 全选
mysql> select Year(from_unixtime(post_time)) as Year, Month(from_unixtime(post_time)) as Month, count(*) as Total from phpbb_posts group by Year,Month;
+------+-------+-------+
| Year | Month | Total |
+------+-------+-------+
| 2005 | 3 | 86 |
| 2005 | 4 | 1248 |
| 2005 | 5 | 2099 |
| 2005 | 6 | 1996 |
| 2005 | 7 | 3589 |
| 2005 | 8 | 4659 |
| 2005 | 9 | 5745 |
| 2005 | 10 | 9986 |
| 2005 | 11 | 6665 |
| 2005 | 12 | 6176 |
| 2006 | 1 | 4889 |
| 2006 | 2 | 4198 |
| 2006 | 3 | 7815 |
| 2006 | 4 | 8330 |
| 2006 | 5 | 9798 |
| 2006 | 6 | 13684 |
| 2006 | 7 | 10735 |
| 2006 | 8 | 11480 |
| 2006 | 9 | 12883 |
| 2006 | 10 | 16669 |
| 2006 | 11 | 16982 |
| 2006 | 12 | 14741 |
| 2007 | 1 | 19885 |
| 2007 | 2 | 17870 |
| 2007 | 3 | 23305 |
| 2007 | 4 | 31941 |
| 2007 | 5 | 38088 |
| 2007 | 6 | 31537 |
| 2007 | 7 | 28955 |
| 2007 | 8 | 33986 |
| 2007 | 9 | 31422 |
| 2007 | 10 | 47301 |
| 2007 | 11 | 43242 |
| 2007 | 12 | 38735 |
| 2008 | 1 | 32630 |
| 2008 | 2 | 24055 |
| 2008 | 3 | 35760 |
| 2008 | 4 | 47498 |
| 2008 | 5 | 47785 |
| 2008 | 6 | 44474 |
| 2008 | 7 | 40486 |
| 2008 | 8 | 45585 |
| 2008 | 9 | 41847 |
| 2008 | 10 | 47826 |
| 2008 | 11 | 55238 |
| 2008 | 12 | 37186 |
| 2009 | 1 | 27878 |
| 2009 | 2 | 26723 |
| 2009 | 3 | 39175 |
| 2009 | 4 | 50999 |
| 2009 | 5 | 61516 |
| 2009 | 6 | 42621 |
| 2009 | 7 | 35070 |
| 2009 | 8 | 43568 |
| 2009 | 9 | 42030 |
| 2009 | 10 | 62177 |
| 2009 | 11 | 81801 |
| 2009 | 12 | 37030 |
| 2010 | 1 | 42947 |
| 2010 | 2 | 32591 |
| 2010 | 3 | 43336 |
| 2010 | 4 | 49244 |
| 2010 | 5 | 54054 |
| 2010 | 6 | 57787 |
| 2010 | 7 | 42570 |
| 2010 | 8 | 35141 |
| 2010 | 9 | 28026 |
| 2010 | 10 | 38021 |
| 2010 | 11 | 33826 |
| 2010 | 12 | 31309 |
| 2011 | 1 | 25733 |
| 2011 | 2 | 18921 |
| 2011 | 3 | 30867 |
| 2011 | 4 | 44588 |
| 2011 | 5 | 51596 |
| 2011 | 6 | 38670 |
| 2011 | 7 | 33504 |
| 2011 | 8 | 37761 |
| 2011 | 9 | 29827 |
| 2011 | 10 | 37301 |
| 2011 | 11 | 34455 |
| 2011 | 12 | 27557 |
| 2012 | 1 | 27859 |
| 2012 | 2 | 35371 |
| 2012 | 3 | 32861 |
| 2012 | 4 | 26058 |
| 2012 | 5 | 27806 |
| 2012 | 6 | 24813 |
| 2012 | 7 | 26065 |
| 2012 | 8 | 25464 |
| 2012 | 9 | 19184 |
| 2012 | 10 | 19558 |
| 2012 | 11 | 16669 |
| 2012 | 12 | 12542 |
| 2013 | 1 | 12812 |
| 2013 | 2 | 9281 |
| 2013 | 3 | 12406 |
| 2013 | 4 | 12587 |
| 2013 | 5 | 11125 |
| 2013 | 6 | 6851 |
| 2013 | 7 | 5728 |
| 2013 | 8 | 5707 |
| 2013 | 9 | 5223 |
| 2013 | 10 | 5352 |
| 2013 | 11 | 5285 |
| 2013 | 12 | 5574 |
| 2014 | 1 | 5529 |
| 2014 | 2 | 5414 |
| 2014 | 3 | 6727 |
| 2014 | 4 | 7476 |
| 2014 | 5 | 6565 |
| 2014 | 6 | 5250 |
| 2014 | 7 | 4189 |
| 2014 | 8 | 4870 |
| 2014 | 9 | 4075 |
| 2014 | 10 | 4763 |
| 2014 | 11 | 3886 |
| 2014 | 12 | 3052 |
| 2015 | 1 | 3245 |
| 2015 | 2 | 2285 |
| 2015 | 3 | 2777 |
| 2015 | 4 | 2486 |
| 2015 | 5 | 2419 |
| 2015 | 6 | 1661 |
| 2015 | 7 | 1644 |
| 2015 | 8 | 1999 |
| 2015 | 9 | 2252 |
| 2015 | 10 | 2731 |
| 2015 | 11 | 2054 |
| 2015 | 12 | 1583 |
| 2016 | 1 | 1857 |
| 2016 | 2 | 1536 |
| 2016 | 3 | 2565 |
| 2016 | 4 | 3864 |
| 2016 | 5 | 2991 |
| 2016 | 6 | 1791 |
| 2016 | 7 | 1700 |
| 2016 | 8 | 2151 |
| 2016 | 9 | 1947 |
| 2016 | 10 | 2102 |
| 2016 | 11 | 1793 |
| 2016 | 12 | 1846 |
| 2017 | 1 | 1372 |
| 2017 | 2 | 1408 |
| 2017 | 3 | 1583 |
| 2017 | 4 | 1578 |
| 2017 | 5 | 1461 |
| 2017 | 6 | 948 |
| 2017 | 7 | 1148 |
| 2017 | 8 | 1085 |
| 2017 | 9 | 1225 |
| 2017 | 10 | 959 |
| 2017 | 11 | 1104 |
| 2017 | 12 | 778 |
| 2018 | 1 | 944 |
| 2018 | 2 | 647 |
| 2018 | 3 | 905 |
| 2018 | 4 | 1307 |
| 2018 | 5 | 1487 |
| 2018 | 6 | 806 |
| 2018 | 7 | 992 |
| 2018 | 8 | 1052 |
| 2018 | 9 | 865 |
| 2018 | 10 | 847 |
| 2018 | 11 | 758 |
| 2018 | 12 | 538 |
| 2019 | 1 | 418 |
| 2019 | 2 | 485 |
| 2019 | 3 | 609 |
| 2019 | 4 | 508 |
| 2019 | 5 | 482 |
| 2019 | 6 | 360 |
| 2019 | 7 | 520 |
| 2019 | 8 | 438 |
| 2019 | 9 | 274 |
| 2019 | 10 | 565 |
| 2019 | 11 | 601 |
| 2019 | 12 | 322 |
| 2020 | 1 | 436 |
| 2020 | 2 | 552 |
| 2020 | 3 | 482 |
| 2020 | 4 | 532 |
+------+-------+-------+
182 rows in set (6.94 sec)
代码: 全选
mysql> select Year(from_unixtime(user_lastvisit)) as Year, Month(from_unixtime(user_lastvisit)) as Month, count(*) as Total from phpbb_users group by Year,Month;
+------+-------+-------+
| Year | Month | Total |
+------+-------+-------+
| 1970 | 1 | 67261 |
| 2005 | 3 | 3 |
| 2005 | 4 | 56 |
| 2005 | 5 | 80 |
| 2005 | 6 | 79 |
| 2005 | 7 | 90 |
| 2005 | 8 | 123 |
| 2005 | 9 | 150 |
| 2005 | 10 | 263 |
| 2005 | 11 | 338 |
| 2005 | 12 | 373 |
| 2006 | 1 | 279 |
| 2006 | 2 | 298 |
| 2006 | 3 | 527 |
| 2006 | 4 | 597 |
| 2006 | 5 | 606 |
| 2006 | 6 | 819 |
| 2006 | 7 | 781 |
| 2006 | 8 | 894 |
| 2006 | 9 | 910 |
| 2006 | 10 | 992 |
| 2006 | 11 | 1295 |
| 2006 | 12 | 1127 |
| 2007 | 1 | 1094 |
| 2007 | 2 | 847 |
| 2007 | 3 | 1292 |
| 2007 | 4 | 1806 |
| 2007 | 5 | 1909 |
| 2007 | 6 | 2062 |
| 2007 | 7 | 2011 |
| 2007 | 8 | 2103 |
| 2007 | 9 | 2064 |
| 2007 | 10 | 2407 |
| 2007 | 11 | 2782 |
| 2007 | 12 | 2820 |
| 2008 | 1 | 2178 |
| 2008 | 2 | 1379 |
| 2008 | 3 | 2274 |
| 2008 | 4 | 3017 |
| 2008 | 5 | 3356 |
| 2008 | 6 | 2689 |
| 2008 | 7 | 2595 |
| 2008 | 8 | 2791 |
| 2008 | 9 | 3142 |
| 2008 | 10 | 7085 |
| 2008 | 11 | 8463 |
| 2008 | 12 | 5745 |
| 2009 | 1 | 4094 |
| 2009 | 2 | 3978 |
| 2009 | 3 | 5186 |
| 2009 | 4 | 5205 |
| 2009 | 5 | 5705 |
| 2009 | 6 | 4746 |
| 2009 | 7 | 5256 |
| 2009 | 8 | 4825 |
| 2009 | 9 | 5016 |
| 2009 | 10 | 5165 |
| 2009 | 11 | 6771 |
| 2009 | 12 | 3971 |
| 2010 | 1 | 4693 |
| 2010 | 2 | 2995 |
| 2010 | 3 | 4976 |
| 2010 | 4 | 4831 |
| 2010 | 5 | 6862 |
| 2010 | 6 | 4902 |
| 2010 | 7 | 4554 |
| 2010 | 8 | 4602 |
| 2010 | 9 | 4296 |
| 2010 | 10 | 5995 |
| 2010 | 11 | 5679 |
| 2010 | 12 | 5695 |
| 2011 | 1 | 4956 |
| 2011 | 2 | 3484 |
| 2011 | 3 | 6301 |
| 2011 | 4 | 5868 |
| 2011 | 5 | 5830 |
| 2011 | 6 | 4262 |
| 2011 | 7 | 3976 |
| 2011 | 8 | 4303 |
| 2011 | 9 | 4310 |
| 2011 | 10 | 5333 |
| 2011 | 11 | 4996 |
| 2011 | 12 | 4418 |
| 2012 | 1 | 2939 |
| 2012 | 2 | 3937 |
| 2012 | 3 | 4703 |
| 2012 | 4 | 4415 |
| 2012 | 5 | 4889 |
| 2012 | 6 | 3305 |
| 2012 | 7 | 3632 |
| 2012 | 8 | 9312 |
| 2012 | 9 | 11164 |
| 2012 | 10 | 17811 |
| 2012 | 11 | 9189 |
| 2012 | 12 | 4222 |
| 2013 | 1 | 3921 |
| 2013 | 2 | 2449 |
| 2013 | 3 | 4435 |
| 2013 | 4 | 4227 |
| 2013 | 5 | 4036 |
| 2013 | 6 | 2667 |
| 2013 | 7 | 2683 |
| 2013 | 8 | 2537 |
| 2013 | 9 | 2190 |
| 2013 | 10 | 2481 |
| 2013 | 11 | 2370 |
| 2013 | 12 | 2196 |
| 2014 | 1 | 1719 |
| 2014 | 2 | 1377 |
| 2014 | 3 | 1974 |
| 2014 | 4 | 4559 |
| 2014 | 5 | 4611 |
| 2014 | 6 | 3824 |
| 2014 | 7 | 4511 |
| 2014 | 8 | 8726 |
| 2014 | 9 | 3043 |
| 2014 | 10 | 3291 |
| 2014 | 11 | 2209 |
| 2014 | 12 | 2825 |
| 2015 | 1 | 1783 |
| 2015 | 2 | 977 |
| 2015 | 3 | 1290 |
| 2015 | 4 | 1225 |
| 2015 | 5 | 1136 |
| 2015 | 6 | 939 |
| 2015 | 7 | 924 |
| 2015 | 8 | 879 |
| 2015 | 9 | 943 |
| 2015 | 10 | 1027 |
| 2015 | 11 | 842 |
| 2015 | 12 | 687 |
| 2016 | 1 | 907 |
| 2016 | 2 | 1339 |
| 2016 | 3 | 1645 |
| 2016 | 4 | 1197 |
| 2016 | 5 | 1577 |
| 2016 | 6 | 1084 |
| 2016 | 7 | 1058 |
| 2016 | 8 | 1032 |
| 2016 | 9 | 1073 |
| 2016 | 10 | 998 |
| 2016 | 11 | 676 |
| 2016 | 12 | 708 |
| 2017 | 1 | 547 |
| 2017 | 2 | 623 |
| 2017 | 3 | 657 |
| 2017 | 4 | 584 |
| 2017 | 5 | 495 |
| 2017 | 6 | 439 |
| 2017 | 7 | 494 |
| 2017 | 8 | 455 |
| 2017 | 9 | 449 |
| 2017 | 10 | 420 |
| 2017 | 11 | 396 |
| 2017 | 12 | 354 |
| 2018 | 1 | 435 |
| 2018 | 2 | 284 |
| 2018 | 3 | 412 |
| 2018 | 4 | 511 |
| 2018 | 5 | 498 |
| 2018 | 6 | 400 |
| 2018 | 7 | 415 |
| 2018 | 8 | 393 |
| 2018 | 9 | 410 |
| 2018 | 10 | 412 |
| 2018 | 11 | 388 |
| 2018 | 12 | 422 |
| 2019 | 1 | 339 |
| 2019 | 2 | 279 |
| 2019 | 3 | 462 |
| 2019 | 4 | 388 |
| 2019 | 5 | 375 |
| 2019 | 6 | 323 |
| 2019 | 7 | 327 |
| 2019 | 8 | 313 |
| 2019 | 9 | 248 |
| 2019 | 10 | 317 |
| 2019 | 11 | 381 |
| 2019 | 12 | 324 |
| 2020 | 1 | 342 |
| 2020 | 2 | 366 |
| 2020 | 3 | 459 |
| 2020 | 4 | 570 |
+------+-------+-------+
183 rows in set (3.50 sec)
代码: 全选
mysql> select Year(from_unixtime(user_lastvisit-user_regdate))-1970 as Year, Month(from_unixtime(user_lastvisit-user_regdate)) as Month, count(*) as Total from phpbb_users where user_lastvisit > user_regdate group by Year,Month;
+------+-------+--------+
| Year | Month | Total |
+------+-------+--------+
| 0 | 1 | 302640 |
| 0 | 2 | 18002 |
| 0 | 3 | 11780 |
| 0 | 4 | 8189 |
| 0 | 5 | 6854 |
| 0 | 6 | 5991 |
| 0 | 7 | 5413 |
| 0 | 8 | 4707 |
| 0 | 9 | 4044 |
| 0 | 10 | 3795 |
| 0 | 11 | 3457 |
| 0 | 12 | 3857 |
| 1 | 1 | 3473 |
| 1 | 2 | 2557 |
| 1 | 3 | 2748 |
| 1 | 4 | 2470 |
| 1 | 5 | 2381 |
| 1 | 6 | 2460 |
| 1 | 7 | 2375 |
| 1 | 8 | 2202 |
| 1 | 9 | 1963 |
| 1 | 10 | 2014 |
| 1 | 11 | 1922 |
| 1 | 12 | 2035 |
| 2 | 1 | 2049 |
| 2 | 2 | 1648 |
| 2 | 3 | 1609 |
| 2 | 4 | 1497 |
| 2 | 5 | 1534 |
| 2 | 6 | 1464 |
| 2 | 7 | 1543 |
| 2 | 8 | 1377 |
| 2 | 9 | 1245 |
| 2 | 10 | 1176 |
| 2 | 11 | 1221 |
| 2 | 12 | 1272 |
| 3 | 1 | 1233 |
| 3 | 2 | 1036 |
| 3 | 3 | 1082 |
| 3 | 4 | 1017 |
| 3 | 5 | 1066 |
| 3 | 6 | 946 |
| 3 | 7 | 925 |
| 3 | 8 | 868 |
| 3 | 9 | 827 |
| 3 | 10 | 842 |
| 3 | 11 | 803 |
| 3 | 12 | 814 |
| 4 | 1 | 768 |
| 4 | 2 | 674 |
| 4 | 3 | 674 |
| 4 | 4 | 639 |
| 4 | 5 | 622 |
| 4 | 6 | 598 |
| 4 | 7 | 598 |
| 4 | 8 | 571 |
| 4 | 9 | 531 |
| 4 | 10 | 485 |
| 4 | 11 | 464 |
| 4 | 12 | 506 |
| 5 | 1 | 521 |
| 5 | 2 | 438 |
| 5 | 3 | 434 |
| 5 | 4 | 387 |
| 5 | 5 | 415 |
| 5 | 6 | 400 |
| 5 | 7 | 396 |
| 5 | 8 | 367 |
| 5 | 9 | 372 |
| 5 | 10 | 331 |
| 5 | 11 | 342 |
| 5 | 12 | 343 |
| 6 | 1 | 352 |
| 6 | 2 | 269 |
| 6 | 3 | 272 |
| 6 | 4 | 247 |
| 6 | 5 | 235 |
| 6 | 6 | 251 |
| 6 | 7 | 248 |
| 6 | 8 | 214 |
| 6 | 9 | 214 |
| 6 | 10 | 217 |
| 6 | 11 | 204 |
| 6 | 12 | 206 |
| 7 | 1 | 217 |
| 7 | 2 | 160 |
| 7 | 3 | 199 |
| 7 | 4 | 171 |
| 7 | 5 | 166 |
| 7 | 6 | 170 |
| 7 | 7 | 146 |
| 7 | 8 | 139 |
| 7 | 9 | 158 |
| 7 | 10 | 138 |
| 7 | 11 | 149 |
| 7 | 12 | 165 |
| 8 | 1 | 147 |
| 8 | 2 | 117 |
| 8 | 3 | 131 |
| 8 | 4 | 130 |
| 8 | 5 | 128 |
| 8 | 6 | 113 |
| 8 | 7 | 139 |
| 8 | 8 | 126 |
| 8 | 9 | 88 |
| 8 | 10 | 122 |
| 8 | 11 | 98 |
| 8 | 12 | 92 |
| 9 | 1 | 118 |
| 9 | 2 | 104 |
| 9 | 3 | 84 |
| 9 | 4 | 88 |
| 9 | 5 | 94 |
| 9 | 6 | 99 |
| 9 | 7 | 85 |
| 9 | 8 | 67 |
| 9 | 9 | 79 |
| 9 | 10 | 66 |
| 9 | 11 | 69 |
| 9 | 12 | 90 |
| 10 | 1 | 78 |
| 10 | 2 | 81 |
| 10 | 3 | 70 |
| 10 | 4 | 74 |
| 10 | 5 | 80 |
| 10 | 6 | 72 |
| 10 | 7 | 71 |
| 10 | 8 | 72 |
| 10 | 9 | 59 |
| 10 | 10 | 65 |
| 10 | 11 | 66 |
| 10 | 12 | 78 |
| 11 | 1 | 61 |
| 11 | 2 | 57 |
| 11 | 3 | 40 |
| 11 | 4 | 49 |
| 11 | 5 | 47 |
| 11 | 6 | 57 |
| 11 | 7 | 50 |
| 11 | 8 | 46 |
| 11 | 9 | 44 |
| 11 | 10 | 29 |
| 11 | 11 | 30 |
| 11 | 12 | 20 |
| 12 | 1 | 38 |
| 12 | 2 | 24 |
| 12 | 3 | 29 |
| 12 | 4 | 27 |
| 12 | 5 | 40 |
| 12 | 6 | 23 |
| 12 | 7 | 16 |
| 12 | 8 | 26 |
| 12 | 9 | 24 |
| 12 | 10 | 15 |
| 12 | 11 | 18 |
| 12 | 12 | 18 |
| 13 | 1 | 22 |
| 13 | 2 | 17 |
| 13 | 3 | 18 |
| 13 | 4 | 14 |
| 13 | 5 | 17 |
| 13 | 6 | 9 |
| 13 | 7 | 10 |
| 13 | 8 | 9 |
| 13 | 9 | 9 |
| 13 | 10 | 11 |
| 13 | 11 | 8 |
| 13 | 12 | 7 |
| 14 | 1 | 5 |
| 14 | 2 | 5 |
| 14 | 3 | 5 |
| 14 | 4 | 7 |
| 14 | 5 | 3 |
| 14 | 6 | 5 |
| 14 | 7 | 6 |
| 14 | 8 | 2 |
| 14 | 10 | 2 |
| 14 | 11 | 1 |
| 15 | 1 | 1 |
+------+-------+--------+
179 rows in set (2.47 sec)
代码: 全选
mysql> select Hour(from_unixtime(post_time)) as Hour, count(*) as Total from phpbb_posts group by Hour;
+------+--------+
| Hour | Total |
+------+--------+
| 0 | 99126 |
| 1 | 51153 |
| 2 | 27744 |
| 3 | 16133 |
| 4 | 10592 |
| 5 | 9022 |
| 6 | 13735 |
| 7 | 29709 |
| 8 | 80481 |
| 9 | 146446 |
| 10 | 181427 |
| 11 | 188420 |
| 12 | 179141 |
| 13 | 180617 |
| 14 | 163986 |
| 15 | 168147 |
| 16 | 184698 |
| 17 | 181783 |
| 18 | 164728 |
| 19 | 176107 |
| 20 | 196952 |
| 21 | 218253 |
| 22 | 217330 |
| 23 | 166217 |
+------+--------+
24 rows in set (7.06 sec)
代码: 全选
mysql> select forum_id,topic_id,topic_views,topic_title from phpbb_topics order by topic_views desc limit 20;
+----------+----------+-------------+--------------------------------------------------------------------------------------------------------------------------+
| forum_id | topic_id | topic_views | topic_title |
+----------+----------+-------------+--------------------------------------------------------------------------------------------------------------------------+
| 179 | 267605 | 3196395 | Ubuntu 桌面培训(10.04版发布!全中文官方文档,含汉化截图,提供PDF) |
| 1 | 18569 | 3036157 | Ubuntu用户满意见证! |
| 1 | 33396 | 2967973 | 征集企业或者单位用户使用见证。 |
| 120 | 35100 | 2786929 | [教学]ubuntu常见问题指南 新手发问前必看 08/03/27 更新 |
| 120 | 739 | 2529591 | 学习linux几点忠告 |
| 120 | 7277 | 2478856 | 真正的初学者常用命令! |
| 120 | 257333 | 2453500 | 细说linux挂载——mount,及其他…… |
| 120 | 267518 | 2409755 | 笨兔兔的故事全新PDF版_指导你安装配置使用Ubuntu_基于10.04(实体书已出版!) |
| 120 | 53155 | 2379050 | 星星之火,可以燎原!------------完整的ubuntu镜像源/本地源/更新源/离线升级包!制作总结! |
| 120 | 187105 | 2328156 | [图片版]新手当自强(续)FAQ(请发帖提问前一定进来看看!) |
| 120 | 18334 | 2314780 | IRC扫盲——你可以不会玩QQ,但不能不会IRC! |
| 120 | 67441 | 2274419 | 新手当自强:如何使用论坛、wiki、搜索等来获得自己需要的知识 |
| 120 | 125847 | 2268892 | 一键安装 Ubuntu 软件 |
| 120 | 117838 | 2267521 | [教学]彩色的man pages,让你寻找帮助时也赏心悦目 |
| 120 | 113592 | 2266302 | 这又是一个适合新手看的东东 |
| 120 | 97951 | 2260878 | [原创]How To: 用ubuntu Desktop LiveCD测试电脑硬件/兼容性 |
| 120 | 58816 | 2259607 | 提问的智慧 (新手請讀) |
| 120 | 109255 | 2252870 | Ubuntu常用操作技巧 [WIKI] |
| 120 | 172165 | 2244361 | 写给想学Linux系統管理的人 |
| 120 | 263478 | 2237358 | nvidia驱动从安装到配置(解决刷新率, 无标题栏和使用开源驱动等问题) |
+----------+----------+-------------+--------------------------------------------------------------------------------------------------------------------------+
20 rows in set (0.74 sec)
有需要者参考。