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