SQL Oracle rownum 在多个where 子句上？

select * from MYTABLE t 
where EQUIPMENT = 'KEYBOARD' and ROWNUM <= 2 or
EQUIPMENT = 'MOUSE' and ROWNUM <= 2 or
EQUIPMENT = 'MONITOR' and ROWNUM <= 2; 

我正在尝试运行一个查询，该查询返回字段（即设备）上的匹配项，并将每种设备类型的输出限制为每个设备类型 2 条记录或更少。我知道这可能不是使用多个 where 子句的最佳方法，但是我过去曾使用过此方法，用 or 语句分隔，但不适用于 rownum。看起来它只返回最后一个 where 语句。提前致谢..

WITH numbered_equipment AS (
  SELECT t.*,
         ROW_NUMBER() OVER( PARTITION BY EQUIPMENT ORDER BY NULL ) AS row_num
  FROM   MYTABLE t 
  WHERE  EQUIPMENT IN ( 'KEYBOARD', 'MOUSE', 'MONITOR' )
)
SELECT *
FROM   numbered_equipment
WHERE  row_num <= 2;

SQLFIDDLE http://sqlfiddle.com/#!4/c5182/2

如果您想根据其他列确定选择哪些行的优先级，请修改ORDER BY NULL查询的一部分，将优先级最高的元素按顺序放在前面。

Edit

要仅提取设备匹配且状态处于活动状态的行，请使用：

WITH numbered_equipment AS (
  SELECT t.*,
         ROW_NUMBER() OVER( PARTITION BY EQUIPMENT ORDER BY NULL ) AS row_num
  FROM   MYTABLE t 
  WHERE  EQUIPMENT IN ( 'KEYBOARD', 'MOUSE', 'MONITOR' )
  AND    STATUS = 'Active'
)
SELECT *
FROM   numbered_equipment
WHERE  row_num <= 2;

SQLFIDDLE http://sqlfiddle.com/#!4/ab02e/2