如何用Knex查询拥有X/Y设备但无Z设备的用户？

要排除拥有Z类型设备的用户，核心是确保目标用户不存在任何Z类型的设备记录——单纯用whereNot因分组逻辑限制无法生效，下面提供两种可行的Knex实现方式：

方案一：子查询排除法 #

通过子查询找出所有拥有Z设备的用户ID，再从结果中直接排除这些ID：

knex
  .select('user_id')
  .groupBy('user_id')
  .where('resource_type', 'Device')
  .andWhere(function() {
    this.where('device_type', 'X').orWhere('device_type', 'Y')
  })
  // 关键：排除所有拥有Z设备的用户
  .whereNotIn('user_id', knex('your_table_name')
    .select('user_id')
    .where('resource_type', 'Device')
    .where('device_type', 'Z')
  )

方案二：Having子句校验法 #

利用分组后的聚合函数，在having中同时校验两个核心条件：

1. 该用户没有任何Z类型的设备
2. 该用户至少拥有一个X或Y类型的设备

knex
  .select('user_id')
  .groupBy('user_id')
  .where('resource_type', 'Device')
  .havingRaw('COUNT(CASE WHEN device_type = ? THEN 1 END) = 0', ['Z'])
  .havingRaw('COUNT(CASE WHEN device_type IN (?, ?) THEN 1 END) > 0', ['X', 'Y'])

逻辑说明 #

• 方案一逻辑直观，适合数据量中等的场景，通过子查询精准定位需排除的用户
• 方案二无需额外子查询，用聚合函数一次完成校验，性能更优，适合大数据量场景

注意将代码中的your_table_name替换为你实际使用的表名。

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