基本信息:
问题:出于教育目的,我决定在嵌套查询中使用日期。我得到了一个奇怪的结果——一个单独的带有 DATE 的嵌套查询(一个带有 DATETIME 的字段)可以正常工作。但作为子查询,它会引发错误。她在我看来是不可理解的。同时,如果您在此子查询中仅使用带有 DATETIME 的字段,则不会出错。尊重 DATE 的文档 - 仍然不清楚。请告知是什么问题,如何处理,在更广泛的层面上是否存在类似问题?我在 Stepic 平台上运行,也许他们有问题?
代码的作用:如果具有特定卡的用户在同一天从不同城市呼叫,则请求发出所有这些呼叫(针对所有此类用户)。
错误代码:
SELECT id, User, card, DATE(calling_Time) AS Calling_DATE, City
FROM calls
WHERE (User, card, DATE(calling_Time)) IN ((
SELECT User, card, DATE(calling_Time)
FROM calls
GROUP BY User, card, DATE(calling_Time)
HAVING COUNT(City) > 1));
错误文本: 错误 1055:HAVING 子句的表达式 #1 不在 GROUP BY 子句中,并且包含在功能上不依赖于 GROUP BY 子句中的列的非聚合列“calls.calling_Time”;这与 sql_mode=only_full_group_by 不兼容
附加信息
工作内部查询+结果:
SELECT User, card, DATE(calling_Time)
FROM calls
GROUP BY User, card, DATE(calling_Time)
HAVING COUNT(City) > 1;
+------+------+--------------------+
| User | card | DATE(calling_Time) |
+------+------+--------------------+
| 1 | 1 | 2018-05-25 |
+------+------+--------------------+
Affected rows: 1
工作完整代码(仅没有 DATE)+ 结果:
SELECT id, User, card, calling_Time, City
FROM calls
WHERE (User, card, calling_Time) IN ((SELECT User, card, calling_Time
FROM calls
GROUP BY User, card, calling_Time
HAVING COUNT(City) > 1));
+----+------+------+---------------------+--------+
| id | User | card | calling_Time | City |
+----+------+------+---------------------+--------+
| 1 | 1 | 1 | 2018-05-25 21:25:54 | Москва |
| 4 | 1 | 1 | 2018-05-25 21:25:54 | Питер |
+----+------+------+---------------------+--------+
Affected rows: 2
创建并填充测试表:
CREATE TABLE calls(
id INT PRIMARY KEY AUTO_INCREMENT,
user VARCHAR (25),
card INT,
calling_Time DATETIME,
City VARCHAR (25)
);
INSERT INTO calls (user, card, calling_Time, City)
VALUES
(1, 1, '2018-05-25 21:25:54', 'Москва'),
(2, 1, '2018-05-25 21:25:54', 'Самара'),
(1, 1, '2018-05-26 21:25:54', 'Москва'),
(1, 1, '2018-05-25 21:25:54', 'Питер')
;
SELECT * FROM calls;
+----+------+------+---------------------+--------+
| id | user | card | calling_Time | City |
+----+------+------+---------------------+--------+
| 1 | 1 | 1 | 2018-05-25 21:25:54 | Москва |
| 2 | 2 | 1 | 2018-05-25 21:25:54 | Самара |
| 3 | 1 | 1 | 2018-05-26 21:25:54 | Москва |
| 4 | 1 | 1 | 2018-05-25 21:25:54 | Питер |
+----+------+------+---------------------+--------+
Affected rows: 4