在 NestJS 项目中，this.xxxRepository.createQueryBuilder()是最常用、最规范的 QueryBuilder 使用方式。
本文以UserEntity为例，系统整理QueryBuilder 对应的常见 SQL 写法，所有示例可直接复制使用。
一、基础准备
@Injectable() export class UserService { constructor( @InjectRepository(UserEntity) private readonly userRepository: Repository<UserEntity>, ) {} }以下示例默认写在UserService中。
一、新增 & 更新 & 删除
1. INSERT
return this.userRepository .createQueryBuilder() .insert() .into(UserEntity) .values({ name: '张三', age: 18, status: 1, }) .execute();批量新增
return this.userRepository .createQueryBuilder() .insert() .into(UserEntity) .values([ { name: '张三', age: 18 }, { name: '李四', age: 20 }, ]) .execute();2. UPDATE
return this.userRepository .createQueryBuilder() .update(UserEntity) .set({ status: 0 }) .where('id = :id', { id }) .execute();3. DELETE
return this.userRepository .createQueryBuilder() .delete() .from(UserEntity) .where('id = :id', { id }) .execute();二、基础查询（SELECT）
1. 查询单表数据
SQL
SELECT * FROM user WHERE status = 1;QueryBuilder
return this.userRepository .createQueryBuilder('u') .where('u.status = :status', { status: 1 }) .getMany();2. 查询指定字段
SQL
SELECT id, name FROM user;return this.userRepository .createQueryBuilder('u') .select(['u.id', 'u.name']) .getMany();3. 查询单条数据
return this.userRepository .createQueryBuilder('u') .where('u.id = :id', { id }) .getOne();三、WHERE 条件用法
1. 多条件 AND / OR
SQL
SELECT * FROM user WHERE age > 18 AND status = 1;return this.userRepository .createQueryBuilder('u') .where('u.age > :age', { age: 18 }) .andWhere('u.status = :status', { status: 1 }) .getMany();OR
.orWhere('u.role = :role', { role: 'admin' })2. IN 查询
SQL
SELECT * FROM user WHERE id IN (1,2,3);return this.userRepository .createQueryBuilder('u') .where('u.id IN (:...ids)', { ids: [1, 2, 3] }) .getMany();3. LIKE 模糊查询
SQL
SELECT * FROM user WHERE name LIKE '%张%';return this.userRepository .createQueryBuilder('u') .where('u.name LIKE :name', { name: `%张%` }) .getMany();四、排序 & 分页
1. ORDER BY
return this.userRepository .createQueryBuilder('u') .orderBy('u.createTime', 'DESC') .getMany();2. 分页查询
return this.userRepository .createQueryBuilder('u') .skip((page - 1) * pageSize) .take(pageSize) .getMany();3. 分页 + 总数
return this.userRepository .createQueryBuilder('u') .skip((page - 1) * pageSize) .take(pageSize) .getManyAndCount();五、JOIN 多表查询
1. LEFT JOIN
SQL
SELECT * FROM user u LEFT JOIN order o ON u.id = o.user_id;return this.userRepository .createQueryBuilder('u') .leftJoin(OrderEntity, 'o', 'o.userId = u.id') .getMany();2. LEFT JOIN 并返回关联数据
return this.userRepository .createQueryBuilder('u') .leftJoinAndSelect('u.orders', 'o') .getMany();⚠️ 前提：UserEntity中定义了@OneToMany(() => OrderEntity, ...)
六、聚合函数（COUNT / SUM）
1. COUNT 查询
return this.userRepository .createQueryBuilder('u') .where('u.status = :status', { status: 1 }) .getCount();2. GROUP BY 统计
SQL
SELECT status, COUNT(*) total FROM user GROUP BY status;return this.userRepository .createQueryBuilder('u') .select('u.status', 'status') .addSelect('COUNT(*)', 'total') .groupBy('u.status') .getRawMany();3. HAVING
return this.userRepository .createQueryBuilder('u') .select('u.status', 'status') .addSelect('COUNT(*)', 'total') .groupBy('u.status') .having('COUNT(*) > :count', { count: 10 }) .getRawMany();七、子查询
1. 子查询 IN
SQL
SELECT * FROM user WHERE id IN (SELECT user_id FROM order WHERE amount > 100);return this.userRepository .createQueryBuilder('u') .where(qb => { const subQuery = qb .subQuery() .select('o.userId') .from(OrderEntity, 'o') .where('o.amount > :amount') .getQuery(); return 'u.id IN ' + subQuery; }) .setParameter('amount', 100) .getMany();
