mysql - 根据sql中的另一个表显示所有日期的计数？

Question

我有两个表，一个是日历，第二个是 final_registration，如下所示:

我的日历表:

*--------------------*
| S.No. |  datefield |
*--------------------*
|  1    | 2019-01-01 |
|  2    | 2019-01-02 |
|  3    | 2019-01-03 |
|  4    | 2019-01-04 |
|  5    | 2019-01-05 |
|  6    | 2019-01-06 |
|  7    | 2019-01-07 |
|  8    | 2019-01-08 |
|  9    | 2019-01-09 |
|  10   | 2019-01-10 |
|  11   | 2019-01-11 |
|  12   | 2019-01-12 |
|  13   | 2019-01-13 |
|  14   | 2019-01-14 |
|  15   | 2019-01-15 |
|  16   | 2019-01-16 |
|  17   | 2019-01-17 |
|  18   | 2019-01-18 |
|  19   | 2019-01-19 |
|  20   | 2019-01-20 |
|  21   | 2019-01-21 |
|  22   | 2019-01-22 |
|  23   | 2019-01-23 |
|  24   | 2019-01-24 |
|  25   | 2019-01-25 |
|  26   | 2019-01-26 |
|  27   | 2019-01-27 |
|  28   | 2019-01-28 |
|  29   | 2019-01-29 |
|  30   | 2019-01-30 |
|  31   | 2019-01-31 |
---------------------

我的第二张 table :

*-----------------------------------------*
| id | event_id |  name |    booking_date  |
*-----------------------------------------*
| 1  |   101    |  Ritu | 2019-01-15 13:21 |
| 2  |   101    | Seeta | 2019-01-15 18:21 |
| 3  |   101    | Geeta | 2019-01-16 13:21 |
| 4  |   102    | Wasim | 2019-01-16 14:21 |
| 5  |   102    | Rahul | 2019-01-17 13:21 |
| 6  |   101    | Gagan | 2019-01-17 14:21 |
| 7  |   101    | Sunny | 2019-01-17 15:21 |
| 8  |   101    | Aman  | 2019-01-17 16:21 |
-------------------------------------------

我正在尝试以下输出:

*--------------------*
|  datefield | count |
*--------------------*
| 2019-01-01 |  0   |
| 2019-01-02 |  0   |
| 2019-01-03 |  0   |
| 2019-01-04 |  0   |
| 2019-01-05 |  0   |
| 2019-01-06 |  0   |
| 2019-01-07 |  0   |
| 2019-01-08 |  0   |
| 2019-01-09 |  0   |
| 2019-01-10 |  0   |
| 2019-01-11 |  0   |
| 2019-01-12 |  0   |
| 2019-01-13 |  0   |
| 2019-01-14 |  0   |
| 2019-01-15 |  2   |
| 2019-01-16 |  1   |
| 2019-01-17 |  3   |
---------------------

今天是 1 月 17 日，所以我需要输出到当前日期。我试过查询，但没有给我 1 月 1 日至 1 月 14 日的查询

SELECT calendar.datefield, COUNT(calendar.datefield)
FROM calendar
LEFT JOIN final_registration
    ON DATE_FORMAT(calendar.datefield, '%Y-%m-%d') = DATE_FORMAT(final_registration.booking_date, '%Y-%m-%d')
WHERE DATE_FORMAT(calendar.datefield, '%Y-%m-%d') <= DATE_FORMAT( CURDATE(), '%Y-%m-%d' )
  AND DATE_FORMAT(calendar.datefield, '%Y-%m') = DATE_FORMAT( CURDATE(), '%Y-%m' )
  AND final_registration.event_id = '101'
GROUP BY DATE_FORMAT(calendar.datefield, '%Y-%m-%d')

我的查询给了我以下结果:

*--------------------*
|  datefield | count |
*--------------------*
| 2019-01-15 |   2   |
| 2019-01-16 |   1   |
| 2019-01-17 |   3   |
*--------------------*

我已经尝试了很多但无法得到我的结果。

Answer 1

您通过在 WHERE 子句中包含条件，使您的 LEFT 连接成为 INNER 连接。试试这个:

SELECT calendar.datefield, COUNT(calendar.datefield)
FROM calendar
LEFT JOIN final_registration
    ON DATE_FORMAT(calendar.datefield, '%Y-%m-%d') = DATE_FORMAT
(final_registration.booking_date, '%Y-%m-%d')
  AND final_registration.event_id = '101'
WHERE DATE_FORMAT(calendar.datefield, '%Y-%m-%d') <= DATE_FORMAT( CURDATE(), '%Y-%m-%d' )
  AND DATE_FORMAT(calendar.datefield, '%Y-%m') = DATE_FORMAT( CURDATE(), '%Y-%m' )
GROUP BY DATE_FORMAT(calendar.datefield, '%Y-%m-%d')