php - 在 mysql 查询中强制计数为零

Question

我有两个表 t1, t2 和以下查询:

SELECT t2.year,
       Count(t1.id) AS count
FROM   t1,
       t2
WHERE  t2.t1id = t1.id
       AND t2.year IN ( 1995, 1996, 1997, 1998,
                    1999, 2000 )
GROUP  BY t2.year
ORDER  BY t1.year 

结果是:

+----------+--------+
| year     | count  |
+----------+--------+
|     1995 |      1 |
|     1998 |      3 |
|     1999 |      3 |
|     2000 |     28 |
+----------+--------+

如您所见，有些年份不见了。是否可以重写此查询以得到结果？

+----------+--------+
| year     | count  |
+----------+--------+
|     1995 |      1 |
|     1996 |      0 |
|     1997 |      0 |
|     1998 |      3 |
|     1999 |      3 |
|     2000 |     28 |
+----------+--------+

我可以使用 php 并检查缺少哪些行以填补缺失的空白，但这似乎效率不高。有什么想法吗？

编辑

t1

+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| id        | int(11)      | NO   | PRI | NULL    | auto_increment |
| name      | varchar(128) | NO   |     | NULL    |                |
+-----------+--------------+------+-----+---------+----------------+

t2

+-----------+--------------+------+-----+---------+----------------+
| Field     | Type         | Null | Key | Default | Extra          |
+-----------+--------------+------+-----+---------+----------------+
| id        | int(11)      | NO   | PRI | NULL    | auto_increment |
| t1id      | int(11)      | NO   |     | NULL    |                |
| year      | int(11)      | NO   |     | NULL    |                |
+-----------+--------------+------+-----+---------+----------------+

例如:

t1

+----------+---------+
| id       | name    |
+----------+---------+
|        1 |    john |
|        2 |     bob |
|       .. |      .. |
+----------+---------+

t2

+----------+---------+---------+
| id       | t1id    |    year |
+----------+---------+---------+
|      100 |       1 |    1995 |
|      101 |       2 |    1998 |
|      103 |       3 |    1998 |
|       .. |      .. |      .. |  
+----------+---------+---------+

组合之后我得到的结果是:

+----------+---------+
| id       |    year |
+----------+---------+
|      100 |    1995 |
|      101 |    1998 |
|      103 |    1998 |
|       .. |      .. |  
+----------+---------+

Answer 1

SELECT t2.year,
       IF(Count(t1.id) > 0, Count(t1.id), 0)
FROM   t1,
       t2
WHERE  t2.t1id = t1.id
       AND t2.year IN ( 1995, 1996, 1997, 1998,
                    1999, 2000 )
GROUP  BY t2.year
ORDER  BY t1.year