如何使用Sequelize对子查询结果执行COUNT统计？

不管用哪种ORM，核心思路都是把原复杂查询作为子查询，外层套COUNT(*)统计，以下是主流ORM的具体写法：

1. Hibernate/JPA #

如果用JPQL，直接将原查询包装成子查询：

String originalJpql = "SELECT e.id, COUNT(o) FROM Order o JOIN o.customer c WHERE ... GROUP BY e.id";
String countJpql = "SELECT COUNT(*) FROM (" + originalJpql + ") AS sub";
Query countQuery = entityManager.createQuery(countJpql);
Long total = (Long) countQuery.getSingleResult();

如果用Criteria API，可以通过Subquery构建：

CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Long> countQuery = cb.createQuery(Long.class);
// 构建原复杂查询的子查询
Subquery<Object[]> subQuery = countQuery.subquery(Object[].class);
Root<Order> subRoot = subQuery.from(Order.class);
subQuery.select(cb.array(subRoot.get("id"), cb.count(subRoot)));
subQuery.join(subRoot.get("customer"));
subQuery.where(/* 原查询条件 */);
subQuery.groupBy(subRoot.get("id"));
// 外层统计
countQuery.select(cb.count(cb.from(subQuery)));
Long total = entityManager.createQuery(countQuery).getSingleResult();

2. MyBatis #

直接在Mapper XML里写嵌套查询，和原生SQL写法一致：

<select id="countComplexQueryResult" resultType="java.lang.Long">
    SELECT COUNT(*) FROM (
        SELECT o.id, COUNT(i.id) 
        FROM orders o 
        JOIN order_items i ON o.id = i.order_id 
        WHERE o.status = 'PAID' 
        GROUP BY o.id
    ) AS sub
</select>

如果用注解方式，同样直接写嵌套SQL：

@Select("SELECT COUNT(*) FROM (" +
        "SELECT o.id, COUNT(i.id) FROM orders o JOIN order_items i ON o.id = i.order_id WHERE o.status = 'PAID' GROUP BY o.id" +
        ") AS sub")
Long countComplexQueryResult();

3. Entity Framework (EF Core) #

用LINQ的FromSqlRaw或者直接构建子查询：

方式一：原生SQL嵌套 #

var originalSql = @"SELECT o.Id, COUNT(i.Id) 
                    FROM Orders o 
                    JOIN OrderItems i ON o.Id = i.OrderId 
                    WHERE o.Status = 'Paid' 
                    GROUP BY o.Id";
var count = context.Database.SqlQuery<long>($"SELECT COUNT(*) FROM ({originalSql}) AS sub").Single();

方式二：LINQ子查询 #

var subQuery = from o in context.Orders
               join i in context.OrderItems on o.Id equals i.OrderId
               where o.Status == OrderStatus.Paid
               group o by o.Id into g
               select new { g.Key, Count = g.Count() };
var count = subQuery.Count();

（注：EF Core会自动将这个LINQ查询转换成SELECT COUNT(*) FROM (...)的原生SQL）

通用注意事项 #

• 子查询必须有别名（比如AS sub），否则部分数据库（如MySQL、SQL Server）会报错
• 如果原查询有DISTINCT，统计时注意是否需要保持去重逻辑，比如SELECT COUNT(DISTINCT col) FROM (...)
• 部分ORM对复杂子查询的支持有限，若遇到语法错误，优先尝试原生SQL拼接的方式

内容的提问来源于stack exchange，提问作者pronodingo