SQL SUM不起作用——看起来是这样

Question

架构：

-- phpMyAdmin SQL Dump
-- version 3.4.5
-- http://www.phpmyadmin.net
SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";
CREATE TABLE IF NOT EXISTS `links` (
  `pageId` int(10) unsigned NOT NULL,
  `linkId` int(10) unsigned NOT NULL,
  `whenUsed` datetime NOT NULL,
  `whenRendered` datetime NOT NULL,
  KEY `pageId` (`pageId`),
  KEY `linkId` (`linkId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `links` (`pageId`, `linkId`, `whenUsed`, `whenRendered`) VALUES
(1, 9, '2013-02-14 00:44:20', '2013-02-14 00:42:39'),
(1, 9, '2013-02-14 00:44:21', '2013-02-14 00:42:39'),
(1, 9, '2013-02-14 00:44:23', '2013-02-14 00:44:21'),
(1, 8, '2013-02-14 00:44:25', '2013-02-14 00:44:23'),
(1, 7, '2013-02-14 00:44:26', '2013-02-14 00:44:25'),
(1, 6, '2013-02-14 00:44:28', '2013-02-14 00:44:26'),
(1, 3, '2013-02-14 00:44:29', '2013-02-14 00:44:26'),
(1, 7, '2013-02-14 00:44:31', '2013-02-14 00:44:29'),
(1, 8, '2013-02-14 00:44:32', '2013-02-14 00:44:31'),
(1, 11, '2013-02-14 00:44:34', '2013-02-14 00:44:32');

内部查询：

SELECT `linkId`, 
TIMEDIFF(`whenUsed`, `whenRendered`) AS 'URDiff', 
CONVERT(DATEDIFF(`whenUsed`, UTC_TIMESTAMP()), SIGNED) AS 'dateDiff',
CONVERT('dateDiff' = 0, UNSIGNED) AS 'usedToday',
CONVERT('dateDiff' < 8 AND 'dateDiff' > 0, UNSIGNED) AS 'usedThisWeek',
CONVERT('dateDiff' < 31 AND 'dateDiff' > 7, UNSIGNED) AS 'usedThisMonth', 
CONVERT('dateDiff' < 365 AND 'dateDiff' > 30, UNSIGNED) AS 'usedThisYear'
FROM `links` 
WHERE MINUTE('URDiff') < 15 AND HOUR('URDiff') = 0
AND `pageId` = '1'
) AS T

内部查询结果：

+--------+----------+----------+-----------+--------------+---------------+--------------+
| linkId | URDiff   | dateDiff | usedToday | usedThisWeek | usedThisMonth | usedThisYear |
+--------+----------+----------+-----------+--------------+---------------+--------------+
|      9 | 00:01:41 |        0 |         1 |            0 |             0 |            0 |
|      9 | 00:01:42 |        0 |         1 |            0 |             0 |            0 |
|      9 | 00:00:02 |        0 |         1 |            0 |             0 |            0 |
|      8 | 00:00:02 |        0 |         1 |            0 |             0 |            0 |
|      7 | 00:00:01 |        0 |         1 |            0 |             0 |            0 |
|      6 | 00:00:02 |        0 |         1 |            0 |             0 |            0 |
|      3 | 00:00:03 |        0 |         1 |            0 |             0 |            0 |
|      7 | 00:00:02 |        0 |         1 |            0 |             0 |            0 |
|      8 | 00:00:01 |        0 |         1 |            0 |             0 |            0 |
|     11 | 00:00:02 |        0 |         1 |            0 |             0 |            0 |
+--------+----------+----------+-----------+--------------+---------------+--------------+ 

外部查询：

SELECT 
`linkId`,
SUM('T.usedToday') AS 'countToday',
SUM('T.usedThisWeek') AS 'countThisWeek',
SUM('T.usedThisMonth') AS 'countThisMonth',
SUM('T.usedThisYear') AS 'countThisYear'
FROM
(
SELECT `linkId`, 
TIMEDIFF(`whenUsed`, `whenRendered`) AS 'URDiff', 
CONVERT(DATEDIFF(`whenUsed`, UTC_TIMESTAMP()), SIGNED) AS 'dateDiff',
CONVERT('dateDiff' = 0, UNSIGNED) AS 'usedToday',
CONVERT('dateDiff' < 8 AND 'dateDiff' > 0, UNSIGNED) AS 'usedThisWeek',
CONVERT('dateDiff' < 31 AND 'dateDiff' > 7, UNSIGNED) AS 'usedThisMonth', 
CONVERT('dateDiff' < 365 AND 'dateDiff' > 30, UNSIGNED) AS 'usedThisYear'
FROM `links` 
WHERE MINUTE('URDiff') < 15 AND HOUR('URDiff') = 0
AND `pageId` = '1'
) AS T
GROUP BY `linkId`

外部查询结果：

+--------+------------+---------------+----------------+---------------+
| linkId | countToday | countThisWeek | countThisMonth | countThisYear |
+--------+------------+---------------+----------------+---------------+
|      3 |          0 |             0 |              0 |             0 |
|      6 |          0 |             0 |              0 |             0 |
|      7 |          0 |             0 |              0 |             0 |
|      8 |          0 |             0 |              0 |             0 |
|      9 |          0 |             0 |              0 |             0 |
|     11 |          0 |             0 |              0 |             0 |
+--------+------------+---------------+----------------+---------------+

想一想？我可以从内部结果中清楚地看到，usedToday列中有3行对于linkId 9是1（我已经将它们转换为无符号，所以它们的SUM应该可以）。然而，linkId 9的countToday是0，而不是3。为什么？

MySQL给了我一些有趣的警告：

Warning (Code 1292): Truncated incorrect time value: 'URDiff'
Warning (Code 1292): Truncated incorrect time value: 'URDiff'
Warning (Code 1292): Truncated incorrect DOUBLE value: 'dateDiff'
Warning (Code 1292): Truncated incorrect DOUBLE value: 'dateDiff'
Warning (Code 1292): Truncated incorrect DOUBLE value: 'dateDiff'
Warning (Code 1292): Truncated incorrect DOUBLE value: 'dateDiff'
Warning (Code 1292): Truncated incorrect DOUBLE value: 'dateDiff'
Warning (Code 1292): Truncated incorrect DOUBLE value: 'dateDiff'
Warning (Code 1292): Truncated incorrect DOUBLE value: 'dateDiff'
Warning (Code 1292): Truncated incorrect DOUBLE value: 'T.usedToday'
Warning (Code 1292): Truncated incorrect DOUBLE value: 'T.usedThisWeek'
Warning (Code 1292): Truncated incorrect DOUBLE value: 'T.usedThisMonth'
Warning (Code 1292): Truncated incorrect DOUBLE value: 'T.usedThisYear'
Warning (Code 1292): Truncated incorrect DOUBLE value: 'T.usedToday'
Warning (Code 1292): Truncated incorrect DOUBLE value: 'T.usedThisWeek'
Warning (Code 1292): Truncated incorrect DOUBLE value: 'T.usedThisMonth'
Warning (Code 1292): Truncated incorrect DOUBLE value: 'T.usedThisYear'
Warning (Code 1292): Truncated incorrect DOUBLE value: 'T.usedToday'
Warning (Code 1292): Truncated incorrect DOUBLE value: 'T.usedThisWeek'
Warning (Code 1292): Truncated incorrect DOUBLE value: 'T.usedThisMonth'
Warning (Code 1292): Truncated incorrect DOUBLE value: 'T.usedThisYear'
Warning (Code 1292): Truncated incorrect DOUBLE value: 'T.usedToday'
Warning (Code 1292): Truncated incorrect DOUBLE value: 'T.usedThisWeek'
Warning (Code 1292): Truncated incorrect DOUBLE value: 'T.usedThisMonth'
Warning (Code 1292): Truncated incorrect DOUBLE value: 'T.usedThisYear'
Warning (Code 1292): Truncated incorrect DOUBLE value: 'T.usedToday'
Warning (Code 1292): Truncated incorrect DOUBLE value: 'T.usedThisWeek'
Warning (Code 1292): Truncated incorrect DOUBLE value: 'T.usedThisMonth'
Warning (Code 1292): Truncated incorrect DOUBLE value: 'T.usedThisYear'
Warning (Code 1292): Truncated incorrect DOUBLE value: 'T.usedToday'
Warning (Code 1292): Truncated incorrect DOUBLE value: 'T.usedThisWeek'
Warning (Code 1292): Truncated incorrect DOUBLE value: 'T.usedThisMonth'
Warning (Code 1292): Truncated incorrect DOUBLE value: 'T.usedThisYear'
Warning (Code 1292): Truncated incorrect DOUBLE value: 'T.usedToday'
Warning (Code 1292): Truncated incorrect DOUBLE value: 'T.usedThisWeek'
Warning (Code 1292): Truncated incorrect DOUBLE value: 'T.usedThisMonth'
Warning (Code 1292): Truncated incorrect DOUBLE value: 'T.usedThisYear'
Warning (Code 1292): Truncated incorrect DOUBLE value: 'T.usedToday'
Warning (Code 1292): Truncated incorrect DOUBLE value: 'T.usedThisWeek'
Warning (Code 1292): Truncated incorrect DOUBLE value: 'T.usedThisMonth'
Warning (Code 1292): Truncated incorrect DOUBLE value: 'T.usedThisYear'
Warning (Code 1292): Truncated incorrect DOUBLE value: 'T.usedToday'
Warning (Code 1292): Truncated incorrect DOUBLE value: 'T.usedThisWeek'
Warning (Code 1292): Truncated incorrect DOUBLE value: 'T.usedThisMonth'
Warning (Code 1292): Truncated incorrect DOUBLE value: 'T.usedThisYear'
Warning (Code 1292): Truncated incorrect DOUBLE value: 'T.usedToday'
Warning (Code 1292): Truncated incorrect DOUBLE value: 'T.usedThisWeek'
Warning (Code 1292): Truncated incorrect DOUBLE value: 'T.usedThisMonth'
Warning (Code 1292): Truncated incorrect DOUBLE value: 'T.usedThisYear'

从我在网上找到的关于这一警告的信息来看，这似乎发生在一些没有在名字上使用引号的人身上（我到处都用引号）。现在，为什么解析器会对引号的使用发出这样的警告呢？

Answer 1

爸爸！以下是有效的查询：

SELECT
`linkId`,
SUM(T.usedToday)ascountToday,
SUM(T.usedThisWeek)as countThisWeek,
SUM(T.usedThisMonth)as countThisMonth,
SUM(T.usedThisYear)as countThisYear
FROM
(
    SELECT `linkId`,
    TIMEDIFF(`whenUsed`, `whenRendered`) as URDiff,
    DATEDIFF(`whenUsed`, UTC_TIMESTAMP()) as `dateDiff`,
    if(DATEDIFF(`whenUsed`, UTC_TIMESTAMP()) = 0, 1, 0)as usedToday,
    if(DATEDIFF(`whenUsed`, UTC_TIMESTAMP()) < 8 AND DATEDIFF(`whenUsed`, UTC_TIMESTAMP()) > 0, 1, 0)as usedThisWeek,
    if(DATEDIFF(`whenUsed`, UTC_TIMESTAMP()) < 31 AND DATEDIFF(`whenUsed`, UTC_TIMESTAMP()) > 7, 1, 0) as usedThisMonth,
    if(DATEDIFF(`whenUsed`, UTC_TIMESTAMP()) < 365 AND DATEDIFF(`whenUsed`, UTC_TIMESTAMP()) > 30, 1, 0) as usedThisYear
    FROM `links`
    WHERE MINUTE(TIMEDIFF(`whenUsed`, `whenRendered`)) < 15 AND HOUR(TIMEDIFF(`whenUsed`, `whenRendered`)) = 0
    AND `pageId` = 1
) as T
GROUP BY `linkId`