MySQL COUNT(*) 不计算结果行

Question

m:n 关系实现订阅模型的简化模式:

CREATE TABLE c (
  id INT(11) PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(32)
) ENGINE=MyISAM CHARACTER SET=UTF8;

CREATE TABLE t (
  id INT(11) PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(32)
) ENGINE=MyISAM CHARACTER SET=UTF8;

CREATE TABLE c2t (
  id INT(11) PRIMARY KEY AUTO_INCREMENT,
  cid INT(11) NOT NULL,
  tid INT(11) NOT NULL,
  dateStart DATE NULL,
  dateEnd DATE NULL
) ENGINE=MyISAM CHARACTER SET=UTF8;

INSERT INTO c (name) VALUES ('mike'),('carl'),('suzy');

INSERT INTO t (name) VALUES ('plan1'),('plan2'),('plan3'),('plan4');

INSERT INTO c2t (cid, tid, dateStart, dateEnd) VALUES
  (1, 1, '2014-01-01', '2014-07-31'),
  (1, 2, '2014-08-01', '2015-07-31'),
  (1, 1, '2015-08-01', null),
  (1, 3, '2015-09-01', null),

  (2, 1, '2014-01-01', '2015-07-31'),
  (2, 2, '2015-08-01', '2015-09-30'),
  (2, 3, '2015-09-30', null),

  (3, 1, '2014-01-01', '2014-12-31'),
  (3, 2, '2014-01-01', '2014-12-31'),
  (3, 3, '2015-01-01', '2015-10-31'),
  (3, 4, '2015-01-01', '2015-10-31');

我开发了一个查询来查找对 t 进行有效订阅的 c:

SELECT c.*
FROM c
LEFT JOIN c2t ON c.id = c2t.cid
  AND NOW() BETWEEN COALESCE(dateStart, '0000-00-00')
    AND COALESCE(dateEnd, DATE_ADD(NOW(), INTERVAL 1 DAY))
GROUP BY c2t.cid
HAVING COUNT(c2t.id) > 0;

结果符合预期:

id  name
1   mike
2   carl

当我尝试对结果行进行计数时出现问题。查询几乎相同，我只是添加了一个 COUNT(*):

SELECT COUNT(*)
FROM c
LEFT JOIN c2t ON c.id = c2t.cid
  AND NOW() BETWEEN COALESCE(dateStart, '0000-00-00')
    AND COALESCE(dateEnd, DATE_ADD(NOW(), INTERVAL 1 DAY))
GROUP BY c2t.cid
HAVING COUNT(c2t.id) > 0;

结果:

`COUNT(*)`
2
1

预期结果将是包含找到的行数 (2) 的单行。我只能假设 GROUP BY 正在干扰，但不知道如何解决。欢迎提供解释。

Answer 1

用子查询包装所有内容并在外部查询中使用COUNT:

SELECT COUNT(*)
FROM (
  SELECT c.*
  FROM c
  LEFT JOIN c2t ON c.id = c2t.cid
    AND NOW() BETWEEN COALESCE(dateStart, '0000-00-00')
    AND COALESCE(dateEnd, DATE_ADD(NOW(), INTERVAL 1 DAY))
  GROUP BY c2t.cid
  HAVING COUNT(c2t.id) > 0
) AS sub