SQL Oracle rownum上有多个where子句? [英] SQL Oracle rownum on multiple where clauses?

问题描述

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语句.预先感谢..

I am trying to run a query that returns matches on a field (ie equipment) and limits the output of each type of equipment to 2 records or less per equipment type.. I know this probably not the best way to use multiple where clauses but i have used this in the past separated by or statements but does not work with rownum. It seems it is only returning the very last where statement. thanks in advance..

推荐答案

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部分，以将优先级最高的元素排在顺序的首位.

If you want to prioritize which rows are selected based on other columns then modify the ORDER BY NULL part of the query to put the highest priority elements first in the order.

修改

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

To just pull out rows where the equipment matches and the status is active then use:

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

这篇关于SQL Oracle rownum上有多个where子句?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！