Knex -获取拥有X或Y设备但没有Z设备的用户

Question

我想获得拥有X或Y设备的用户，但排除所有拥有Z设备的用户。这大致就是表的样子

tbody> <<tr>

user_id	device_type	resource_type
123	X	设备
123	Y	设备
321	Y	设备
321	Z	设备
231	Y	设备
333	Q	其他

像这样应该可以工作

// First you create a subquery that will select the users with devices different from X and Y
const subquery = knex
.select('user_id')
.groupBy('user_id')
.where('resource_type', 'Device')
// Search users without X or Y device_type
.whereNotIn('device_type', ['X', 'Y']);
// And then you search the users with device_type in X and Y, but not in the subquery
knex
.select('user_id')
.groupBy('user_id')
.where('resource_type', 'Device')
// Search users with X or Y device_type
.whereIn('device_type', ['X', 'Y'])
// Remove users that have at least a device different of X AND Y (those in the subquery)
.whereNotIn('user_id', subquery);