mysql - 获取来自不同位置的访问次数

visit mysql表：

+---------+-------------+------------+--------+---------------------+
| visitId | visitSiteId | homeSiteId | userId |       time          |
+---------+-------------+------------+--------+---------------------+
|   1     |      1      |     1      |  1001  | 2013-09-01 11:50:12 |
|   2     |      1      |     1      |  1001  | 2013-09-03 12:50:12 |
|   3     |      2      |     1      |  1001  | 2013-09-04 13:50:12 |
|   4     |      2      |     1      |  1001  | 2013-09-07 11:00:00 |
|   5     |      2      |     1      |  1001  | 2013-09-09 12:32:12 |
|   6     |      2      |     1      |  1001  | 2013-09-13 15:48:12 |
|   7     |      2      |     2      |  1002  | 2013-09-01 11:00:12 |
|   8     |      2      |     2      |  1002  | 2013-09-02 12:50:12 |
|   9     |      2      |     2      |  1002  | 2013-09-05 13:50:12 |
|   10    |      1      |     2      |  1002  | 2013-09-06 11:50:12 |
|   11    |      1      |     2      |  1002  | 2013-09-07 12:50:12 |
|   12    |      2      |     2      |  1002  | 2013-09-15 13:50:12 |
+---------+-------------+------------+--------+---------------------+

记录访问日期时间，访问的站点和用户的主页

我想找到如何从上述数据输出3个报告。


返回所有非网站访问次数> = HomeSite访问次数的用户；任何日期
返回任何非现场访问次数> =最小输入整数的用户，例如非房屋计数> = 10
1和2都具有日期范围，即时间> ='2013-09-01 11:50:12'和时间

我对MySQL还是很陌生，并且在子查询语法方面苦苦挣扎，因此不胜感激：
以下查询将返回“家庭访问”和“非家庭访问”的计数，但我无法理解合并结果的子查询

SELECT COUNT(v.user_id), v.user_id FROM visit v WHERE v.Site <> v.HomeSite GROUP BY v.user_id;
SELECT COUNT(v.user_id), v.user_id FROM visit v WHERE v.Site = v.HomeSite GROUP BY v.user_id;
SELECT COUNT(v.user_id), v.user_id FROM visit v WHERE v.Site <> v.HomeSite AND v.Time >= '2013-09-01 11:50:12' AND v.Time <= '2013-09-13 15:48:12' GROUP BY v.user_id;
SELECT COUNT(v.user_id), v.user_id FROM visit v WHERE v.Site = v.HomeSite AND v.Time >= '2013-09-01 11:50:12' AND v.Time <= '2013-09-13 15:48:12' GROUP BY v.user_id;

这是MySQL创建表和INSERT数据以帮助进行测试：

 CREATE TABLE `dbName`.`visit`(`visitId` INT(11) NOT NULL AUTO_INCREMENT, `visitSiteId` INT(11), `homeSiteId` INT(11), `userId` INT(11), `time` DATETIME, PRIMARY KEY (`visitId`));

INSERT INTO `dbName`.`visit`(`visitSiteId`,`homeSiteId`,`userId`,`time`) VALUES
  (1,1,1001,'2013-09-01 11:50:12'),
  (1,1,1001,'2013-09-03 12:50:12'),
  (2,1,1001,'2013-09-04 13:50:12'),
  (2,1,1001,'2013-09-07 11:00:00'),
  (2,1,1001,'2013-09-09 12:32:12'),
  (2,1,1001,'2013-09-13 15:48:12'),
  (2,1,1002,'2013-09-01 11:00:12'),
  (2,1,1002,'2013-09-02 12:50:12'),
  (2,1,1002,'2013-09-05 13:50:12'),
  (1,1,1002,'2013-09-06 11:50:12'),
  (1,1,1002,'2013-09-07 12:50:12'),
  (2,1,1002,'2013-09-15 13:50:12');

最佳答案

尚未尝试和测试。

SELECT Count(visitId) AS Visits, userId
    FROM homeSiteId
    GROUP BY visitId HAVING Count(visitId) > 2999