如何在SQL查询中获得匹配的左联接项总数

Question

我正在努力寻找一种方法来获得一个左联接中匹配的行数的总计数。

这是当前的查询：

SELECT * FROM renewal
LEFT JOIN appointment ON appointment.renewalid=renewal.id 

我试过：

SELECT *, COUNT(app.id) AS appcount FROM renewal
LEFT JOIN appointment ON appointment.renewalid=renewal.id 

但这显然不是正确的方法，因为它只返回返回的每个更新行的约会总数。

我还尝试了一个子查询：

SELECT customer.*, app.totalcount FROM renewal
LEFT JOIN (SELECT COUNT(id) AS appcount FROM appointment) AS app ON app.renewalid=renewal.id 

这也不起作用。

目前，我可以让它为每个客户返回总数，但不是一个总计。

我之所以不只是查询约会表，是因为我只需要返回链接到指定外部“WHERE”语句的客户的约会总数。

免责声明：上面的查询是一个更加简化的版本，只是为了便于阅读。

以下是完整的查询：

SELECT
    renewal.id AS renid,
    renewal.personid,
    renewal.enddate,
    renewal.assettype,
    renewal.producttype,
    renewal.vrm,
    renewal.make,
    renewal.model,
    renewal.submodel,
    renewal.derivative,
    renewal.complete,
    person.forename,
    person.surname,
    person.company,
    appointment.id AS appid,
    COUNT(appointment.renewalid) AS appointedcount,
    appointment.renewalid,
    n.latestnote,
    (
    SELECT
        COUNT(complete)
    FROM
        renewal
    WHERE
        complete = 1 && enddate BETWEEN '2020-01-01' AND '2020-01-30' && dealershipid = '1' && assettype = 'N' && producttype NOT LIKE '%CH%' && complete = 1
) AS renewedcount
FROM
    renewal
LEFT JOIN person ON person.id = renewal.personid
LEFT JOIN appointment ON appointment.renewalid = renewal.id
LEFT JOIN(
    SELECT
        note AS latestnote,
        TIMESTAMP,
        renewalid
    FROM
        renewal_note
    ORDER BY
        TIMESTAMP
    DESC
) AS n
ON
    n.renewalid = renewal.id
WHERE
    enddate BETWEEN '2020-01-01' AND '2020-01-30' && renewal.dealershipid = '1' && assettype = 'N' && producttype NOT LIKE '%CH%'
GROUP BY
    renid
ORDER BY
    enddate ASC

这就是使用完整查询输出的内容(删除与此问题无关的杂乱列)：

| renid  | appid  | appointedcount  | renewedcount  |  |
|--------|--------|-----------------|---------------|--|
|  60177 |   1096 |               6 |             5 |  |
|  64704 |   2470 |               6 |             5 |  |
|  43057 |        |               0 |             5 |  |
|  64626 |        |               0 |             5 |  |
|  11123 |        |               0 |             5 |  |
|  72469 |        |               0 |             5 |  |
|  76055 |   2879 |               7 |             5 |  |
|  76001 |   2546 |               3 |             5 |  |
|  72171 |   2769 |               6 |             5 |  |
|  76073 |        |               0 |             5 |  |
|  73183 |   2093 |               8 |             5 |  |
|  73114 |   2834 |               6 |             5 |  |
|  43088 |        |               0 |             5 |  |
|    732 |        |               0 |             5 |  |
|  11157 |        |               0 |             5 |  |
|  60207 |        |               0 |             5 |  |
|  73103 |   2015 |               3 |             5 |  |
|  75982 |        |               0 |             5 |  |
|  43076 |        |               0 |             5 |  |

似乎最高的指定计数值是8，如果计算带有appid (约会)的行数，则加起来是8。我相信我正朝着正确的方向前进，因为它正在返回8(随机更新行)，但似乎无法越过这一点。

有人能引导我朝正确的方向走吗？

Answer 1

我想你想要一个窗口功能：

SELECT *, COUNT(a.id) OVER () AS total_appcount
FROM renewal r LEFT JOIN
     appointment a
     ON a.renewalid = r.id ;

在旧版本的MySQL中，可以使用关联子查询：

SELECT *,
       (SELECT COUNT(*)
        FROM renewal r JOIN
             appointment a
             ON a.renewalid = r.id
       ) AS total_appcount
FROM renewal r LEFT JOIN
     appointment a
     ON a.renewalid = r.id ;

注意，对于子查询，您不需要外部联接，因为您只需要匹配。