数 —.count(column|columns|raw, [options])
对指定的列或列数组执行计数(请注意,某些驱动程序不支持多列)。也接受原始表达式。从count(和其他聚合查询)返回的值是一个对象数组,例如:[{'COUNT(*)': 1}]。实际的键是特定于方言的,因此通常我们希望指定一个别名(请参见下面的示例)。请注意,在Postgres中,count返回一个bigint类型,该类型将是一个String而不是一个Number(更多信息)。
knex('users').count('active')
输出:
select count(`active`) from `users`
knex('users').count('active', {as: 'a'})
输出:
select count(`active`) as `a` from `users`
knex('users').count('active as a')
输出:
select count(`active`) as `a` from `users`
knex('users').count({ a: 'active' })
输出:
select count(`active`) as `a` from `users`
knex('users').count({ a: 'active', v: 'valid' })
输出:
select count(`active`) as `a`, count(`valid`) as `v` from `users`
knex('users').count('id', 'active')
输出:
select count(`id`) from `users`
knex('users').count({ count: ['id', 'active'] })
输出:
select count(`id`, `active`) as `count` from `users`
knex('users').count(knex.raw('??', ['active']))
输出:
select count(`active`) from `users`
TypeScript的用法
默认情况下,count的值类型为string | number。这可能是违反直觉的,但是当javascript的Number类型的值不够大时,某些连接器(例如postgres)会自动将BigInt结果转换为字符串。
knex('users').count('age') // Resolves to: Record<string, number | string>
knex('users').count({count: '*'}) // Resolves to { count?: string | number | undefined; }
有工作string | number,如果你不与大表时会带来不便。有两种选择:
// Be explicit about what you want as a result:
knex('users').count<Record<string, number>>('age');
// Setup a one time declaration to make knex use number as result type for all
// count and countDistinct invocations (for any table)
declare module "knex/types/result" {
interface Registry {
Count: number;
}
}
使用countDistinct在聚合函数中添加一个独特的表达式。
knex('users').countDistinct('active')
输出:
select count(distinct `active`) from `users`