jquery - 如何查询 MYSQL 表以获取总体和过去一年内列出现的计数？

Question

我正在使用 MySQL，想要创建一个查询来计算去年每个 contact_id 发生的约会次数，以及总体发生的次数。

我的预约表包含以下行和示例数据:

id | contact_id | appointment_date | status_id

1  |  1         |  2019-01-01      | Attended
2  |  1         |  2018-05-01      | Attended
3  |  2         |  2019-04-01      | Cancelled
4  |  2         |  2019-05-01      | No Show
5  |  1         |  2018-07-01      | Cancelled
6  |  1         |  2019-08-01      | Not Complete

假设我想查看 contact_id '1' 有多少次约会，我希望结果如下:

 Status       |  Total overall | Total this year | Total last year
 Attended     |  2             | 1               | 1
 Cancelled    |  1             | 0               | 1
 No Show      |  0             | 0               | 0
 Not Complete |  1             | 1               | 0

这个查询可以用 MYSQL 执行吗？

我将在 HTML/PHP/jQuery Web 应用程序中使用它。我将有一个按钮，单击后将生成此信息。也许我应该放弃查询的想法，并在简单的

之后使用某种 jQuery 表/数据操作来操作它

'从约会中选择 * WHERE contact_id = 1'？

Answer 1

如果您想在单个查询中包含它，您可以尝试如下操作:

SELECT
      status_id
    , COUNT(*) AS `Total overall`
    , SUM(IF(YEAR(appointment_date) = YEAR(CURDATE()), 1, 0)) AS `Total this year`
    , SUM(IF(YEAR(appointment_date) = YEAR(CURDATE()) - 1, 1, 0)) AS `Total last year`
FROM
    appointment
WHERE
    contact_id = 1
GROUP BY
    status_id

=>

status_id   total   total_this_year total_last_year
Attended    2   1   1
Cancelled   1   0   1
Not Complete    1   1   0

上述查询不会返回“No Show”的信息。如果您确实需要所有状态，您可以执行以下操作:

SELECT
      s.status_id
    , IFNULL(a.total, 0) AS `Total overall`
    , IFNULL(a.total_this_year, 0) AS `Total this year`
    , IFNULL(a.total_last_year, 0) AS `Total last year`
FROM
    (
        SELECT
            DISTINCT status_id
        FROM
            appointment
    ) AS s
    LEFT JOIN (
        SELECT
              status_id
            , COUNT(*) AS `total`
            , SUM(IF(YEAR(appointment_date) = YEAR(CURDATE()), 1, 0)) AS `total_this_year`
            , SUM(IF(YEAR(appointment_date) = YEAR(CURDATE()) - 1, 1, 0)) AS `total_last_year`
        FROM
            appointment
        WHERE
            contact_id = 1
        GROUP BY
            status_id
    ) AS `a` ON (
        a.status_id = s.status_id
    )

=>

status_id   Total overall   Total this year Total last year
Attended    2   1   1
Cancelled   1   0   1
Not Complete    1   1   0
No Show 0   0   0

附注我不喜欢在查询中使用的年份计算，但是为了简单起见，将其保留在此处。在现实生活中，我会使用范围比较 appointment_date BETWEEN CONCAT(YEAR(CURDATE()), '-01-01') AND CONCAT(YEAR(CURDATE()), '-12-31') 而不是 YEAR(appointment_date) = YEAR(CURDATE())。