mysql - 为具有多个连接的查询编写计数查询

Question

对于 Spring JPA，我正在使用下面的 native 查询，并且需要实现分页。为此，我需要一个计数查询，它将返回以下查询返回的行。如何编写计数查询。

SELECT  count(distinct opportunity_vendors.opportunity_id) as oppcount, vendors.name , count(applications.id) as appcount
FROM public.vendors 
  inner join public.opportunity_vendors on vendors.id = opportunity_vendors.vendor_id
  inner join public.opportunities on opportunity_vendors.opportunity_id = opportunities.id
  inner join public.applications on opportunity_vendors.opportunity_id = applications.opportunity_id
  group by vendors.name having length(vendors.name) > 0;```

Answer 1

你可以这样做:

select count(*) from (
SELECT  count(distinct opportunity_vendors.opportunity_id) as oppcount, vendors.name , count(applications.id) as appcount
FROM public.vendors 
  inner join public.opportunity_vendors on vendors.id = opportunity_vendors.vendor_id
  inner join public.opportunities on opportunity_vendors.opportunity_id = opportunities.id
  inner join public.applications on opportunity_vendors.opportunity_id = applications.opportunity_id
  group by vendors.name having length(vendors.name) > 0
) d

最诚挚的问候，
比亚尼