SQL Oracle多个where子句上的rownum？

Question

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语句。提前致谢。

- emvee

Answer 1

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

如果您想根据其他列来优先选择哪些行，则修改查询的ORDER BY NULL部分，将最高优先级的元素放在顺序中的第一位。

编辑

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

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

- MT0

---

谢谢！这个有效...我如何为您提供的查询中的每个记录指定(status='ACTIVE')？只需在(WHERE row_num <= 2)旁边添加(and status='ACTIVE')即可，再次感谢！编辑：我希望它最多选择2个处于活动状态的资产，而不是在过滤结果后可能包括非活动状态。希望这样说得清楚... - emvee

没事了，我已经让它在活动资产上运行了，方法是加入：WHERE STATUS = 'A' and EQUIPMENT IN ...... - emvee

编辑已添加以提取仅具有活动状态的设备。 - MT0

Answer 2

每种设备类型的行数可以是特定的!

SELECT * FROM MYTABLE t 
where EQUIPMENT = 'KEYBOARD' and ROWNUM <= 2 
UNION ALL
SELECT * FROM MYTABLE t
WHERE EQUIPMENT = 'MOUSE' and ROWNUM <= 2
UNION ALL
SELECT * FROM MYTABLE t
WHERE EQUIPMENT = 'MONITOR' and ROWNUM <= 2; 

- Maheswaran Ravisankar

Answer 3

试试这个：

select * from (
    select * from MYTABLE t  where EQUIPMENT = 'KEYBOARD' and ROWNUM <= 2 
    union
    select * from MYTABLE t  where EQUIPMENT = 'MOUSE' and ROWNUM <= 2 
    union
    select * from MYTABLE t  where EQUIPMENT = 'MONITOR' and ROWNUM <= 2 )

- jcho360

---

这不会按设备类型返回两行 - 它只会返回两行。 - MT0

不，如果你想每种设备类型最多只显示两行，那么你不应该增加“ROWNUM”，否则你可能会得到四个键盘而不是两个键盘和两个鼠标。 - MT0

Answer 4

尝试:

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

- Brian McGinity