Drizzle | 统计行数
PostgreSQL
MySQL
SQLite
This guide assumes familiarity with:

要统计表中的所有行，可以使用 count() 函数或 sql 运算符，如下所示：

index.ts
schema.ts
import { count, sql } from 'drizzle-orm';
import { products } from './schema';

const db = drizzle(...);

await db.select({ count: count() }).from(products);

// Under the hood, the count() function casts its result to a number at runtime.
await db.select({ count: sql`count(*)`.mapWith(Number) }).from(products);
// result type
type Result = {
  count: number;
}[];
select count(*) from products;

要统计指定列包含非 NULL 值的行，可以使用 count() 函数和列：

await db.select({ count: count(products.discount) }).from(products);
// result type
type Result = {
  count: number;
}[];
select count("discount") from products;

Drizzle 拥有简单灵活的 API，可让你创建自定义解决方案。在 PostgreSQL 和 MySQL 中，count() 函数返回 bigint，其驱动程序会将其解释为字符串，因此应将其转换为整数：

import { AnyColumn, sql } from 'drizzle-orm';

const customCount = (column?: AnyColumn) => {
  if (column) {
    return sql<number>`cast(count(${column}) as integer)`; // In MySQL cast to unsigned integer
  } else {
    return sql<number>`cast(count(*) as integer)`; // In MySQL cast to unsigned integer
  }
};

await db.select({ count: customCount() }).from(products);
await db.select({ count: customCount(products.discount) }).from(products);
select cast(count(*) as integer) from products;
select cast(count("discount") as integer) from products;

在 SQLite 中，count() 结果以整数形式返回。

import { sql } from 'drizzle-orm';

await db.select({ count: sql<number>`count(*)` }).from(products);
await db.select({ count: sql<number>`count(${products.discount})` }).from(products);
select count(*) from products;
select count("discount") from products;
IMPORTANT

指定 sql<number> 即表示你告知 Drizzle，该字段的预期类型为 number.
。如果你指定错误（例如，将 sql<string> 用于将返回为数字的字段），则运行时值将与预期类型不匹配。Drizzle 无法基于提供的泛型执行任何类型转换，因为该信息在运行时不可用。

如果你需要对返回值进行运行时转换，你可以使用 .mapWith() 方法。

要统计符合条件的行，可以使用 .where() 方法：

import { count, gt } from 'drizzle-orm';

await db
  .select({ count: count() })
  .from(products)
  .where(gt(products.price, 100));
select count(*) from products where price > 100

以下是如何将 count() 函数与连接和聚合一起使用：

index.ts
schema.ts
import { count, eq } from 'drizzle-orm';
import { countries, cities } from './schema';

// Count cities in each country
await db
  .select({
    country: countries.name,
    citiesCount: count(cities.id),
  })
  .from(countries)
  .leftJoin(cities, eq(countries.id, cities.countryId))
  .groupBy(countries.id)
  .orderBy(countries.name);
select countries.name, count("cities"."id") from countries
  left join cities on countries.id = cities.country_id
  group by countries.id
  order by countries.name;