>问题
我有3张桌子:客户,公司和电话。
一个客户有很多公司,一个公司有很多电话。(一对多(。
公司的当前状态是公司最后一次调用的结果(MAX(created_at((。
现在,我想要一个客户的所有公司的列表,并在结果中包含最后一次调用的列。
所需结果
结果应该是:
公司*, 最后通话.*,
- 可能有created_at日期相同的呼叫。那么 de 结果中应该只有 1 行。 并非所有公司都有调用,
- 公司仍应在结果中,并且调用的列应为 NULL。(左连接(
表
客户
- ID(整数,主键(
- 中文名
- 地址(瓦尔查尔(
- 城市(瓦尔查尔(
公司
- ID(整数,主键(
- customer_id(国际(
- 中文名- 地址(瓦尔查尔(
- 城市(瓦尔查尔(
调用
- ID(整数,主键(
- company_id(国际(
- 结果(瓦尔查尔(
- created_at(日期时间(
尝试
我想出的一个不起作用的查询是:
SELECT * FROM companies co
LEFT JOIN calls ca ON co.id = ca.company_id
WHERE co.customer_id = ?
GROUP BY co.id
HAVING ca.created_at = (SELECT max(ll.created_at) FROM calls ll WHERE ll.company_id = co.id)
你应该只加入一个选择,这样你就不会试图重新评估选择。
SELECT co.id, co.label, ca.result, ca.id, t.date_created as most_recent
FROM companies co
LEFT JOIN
( SELECT MAX(created_at) as date_created, company_id
FROM calls
GROUP BY company_id
) t ON t.company_id = co.id
JOIN calls ca ON ca.company_id = t.company_id AND t.date_created = ca.created_at
WHERE co.customer_id = ?
编辑:
问题是您在最大日期每家公司有多个电话。 要测试这一点,只需拉出一个客户和公司并查看结果。
SELECT co.id, co.label, ca.result, ca.id, ca.created_at as most_recent_date
FROM companies co
LEFT JOIN
( SELECT MAX(created_at) as date_created, company_id
FROM calls
GROUP BY company_id
) t ON t.company_id = co.id
JOIN calls ca ON ca.company_id = t.company_id AND t.date_created = ca.created_at
WHERE co.customer_id = ? AND co.id = ?
运行此查询并指定特定公司。 查看move_recent_date
列,查看每行的日期是否相同,以及是否为最大日期
加入calls
两次来执行此操作,第一次是在子查询中,您可以在子查询中检索每个公司的上次调用日期:
SELECT * FROM companies co
LEFT JOIN (SELECT company_id, MAX(created_at) AS last_call FROM calls GROUP BY company_id) AS last_calls ON last_calls.company_id = co.id
LEFT JOIN calls ca ON ca.company_id = last_calls.company_id AND ca.created_at = last_calls.last_call
WHERE co.customer_id = ?
GROUP BY co.id
看起来我找到了答案。这个给出正确的结果,并且仍然足够快(0.27秒(
SELECT co.*, v.*
FROM companies co
LEFT JOIN
(
SELECT
ca.*
FROM calls ca
JOIN
(
SELECT
company_id,
MAX(created_at) AS max_created_at
FROM calls
GROUP BY company_id
) t
ON ca.company_id = t.company_id AND ca.created_at = t.max_created_at
GROUP BY company_id
) v ON co.id = v.company_id
谢谢大家!
您应该进行子选择以获取从调用创建的最大值,并将其用作公司和呼叫表的连接条件。
SELECT
co.id AS company_id,
co.name AS company_name,
ca.id AS lastcall_id,
ca.result AS lastcall_result
FROM companies AS co
LEFT JOIN calls AS ca
ON co.id = ca.company_id
INNER JOIN
(
SELECT
company_id,
MAX(created_at) AS max_created_at
FROM calls
GROUP BY company_id
) AS max_created_per_company
ON ca.company_id = max_created_per_company.company_id
AND ca.created_at = max_created_per_company.created_at
WHERE co.customer_id = ?
您可以双重联接到 lastcall 表,如以下示例所示:
select companies.id, companies.name , lastcall.id, lastcall.result from companies
inner join (select max(created_at) as lastcall, company_id from calls group by company_id) maxcalls
on (companies.id = maxcalls.company_id)
inner join lastcall on (lastcall = created_at and companies.id = lastcall.company_id)
where customer_id = ?
select companies.* , ca.* from companies
inner join (select max(created_at) as lastcall, company_id from calls group by company_id) maxcalls on (companies.id = maxcalls.company_id)
inner join calls ca on (maxcalls.lastcall = ca.created_at and companies.id = ca.company_id)
where customer_id = ?