mysql - 如何根据时间从两个单独的表中获取计数？

Question

我有四个表:Companys、Vehicles、Quotes 和 PhoneRequests。

公司可能会收到有关其车辆的报价和请求。

每个报价和请求都带有时间标记。

我想查找每个公司每个月收到的报价和请求的数量，只要报价或请求的数量大于 0。

我不知道如何分组以使两个单独表中的月份相同。

这是我到目前为止所得到的:

SELECT
    company.name `company`,
    vehicle.name `vehicle`,
    DATE_FORMAT(COALESCE(quote.qtime, pr.date),'%Y-%m') `month`,
    COUNT(DISTINCT quote.qid) `quotes`,
    COUNT(DISTINCT pr.id) `phone_reqs`
FROM companies company
    LEFT JOIN vehicles vehicle ON vehicle.compid=company.cid
    LEFT JOIN quote_vehicle qv ON qv.vehicle_id=vehicle.vid
    LEFT JOIN quotes quote ON qv.quote_id=quote.qid
    LEFT JOIN phone_requests pr ON pr.vehicle_id=vehicle.vid AND quote.qtime IS NULL OR DATE_FORMAT(quote.qtime,'%Y-%m')=DATE_FORMAT(pr.date,'%Y-%m')
GROUP BY
    company.cid, `month`
HAVING
    quotes > 0 OR phone_reqs > 0
ORDER BY
    `month` DESC, `company` ASC

但它似乎将 phone_reqs 归因于错误的公司，可能是因为 AND quote.qtime IS NULL OR DATE_FORMAT(quote.qtime,'%Y-%m' )=DATE_FORMAT(pr.date,'%Y-%m') 位。不过，我不知道如何确保报价和请求在同一个月内发生。

Answer 1

也许这会有所帮助

SELECT
    company.name `company`,
    vehicle.name `vehicle`,
    month,
    quote.quotes,
    pr.phone_reqs
FROM companies company
    LEFT JOIN vehicles vehicle ON vehicle.compid=company.cid
    LEFT JOIN quote_vehicle qv ON qv.vehicle_id=vehicle.vid
LEFT JOIN (SELECT COUNT(DISTINCT qid) quotes, DATE_FORMAT(qtime,'%Y-%m') `month` FROM quotes GROUP BY `month`) quote ON quote.qid = qv.quote_id
LEFT JOIN (SELECT COUNT(DISTINCT id) `phone_reqs`, DATE_FORMAT(date,'%Y-%m') `month` FROM phone_requests GROUP BY `month`) pr ON pr.phone_reqs = vehicle.vid AND quote.month = pr.month
GROUP BY  company.cid
HAVING
    quotes > 0 OR phone_reqs > 0
ORDER BY
    `month` DESC, `company` ASC