因此,我所关注的问题如下:
如果我在第2列中得到了一个“匹配”,我需要按列1对分区排序,这可能吗?所以在下面的例子中,排名是1,2,3,因为在列1中,它有相同的列1 (1003),对于1004,它没有匹配,所以我将得到一个空值。
预期输出:
Column 1 | Column 2 | Rank
-----------------------------
1003 | Match | 1
1003 | Sorias | 2
1003 | Pythn | 3
1004 | Sorias | null
1004 | Pythn | null
1004 | Pythn | null发布于 2015-12-09 23:06:46
Oracle 11g R2架构设置
CREATE TABLE table_name ( Column1, Column2 ) AS
SELECT 1003, 'Match' FROM DUAL
UNION ALL SELECT 1003, 'Sorias' FROM DUAL
UNION ALL SELECT 1003, 'Pythn' FROM DUAL
UNION ALL SELECT 1004, 'Sorias' FROM DUAL
UNION ALL SELECT 1004, 'Pythn' FROM DUAL
UNION ALL SELECT 1004, 'Pythn' FROM DUAL查询1
SELECT Column1,
Column2,
CASE WHEN COUNT( CASE WHEN Column2 = 'Match' THEN 1 END ) OVER ( PARTITION BY Column1 ) > 0
THEN ROW_NUMBER() OVER ( PARTITION BY Column1 ORDER BY Column2 )
END AS Rank
FROM table_name| COLUMN1 | COLUMN2 | RANK |
|---------|---------|--------|
| 1003 | Match | 1 |
| 1003 | Pythn | 2 |
| 1003 | Sorias | 3 |
| 1004 | Pythn | (null) |
| 1004 | Pythn | (null) |
| 1004 | Sorias | (null) |发布于 2015-12-09 22:03:05
可以在窗口函数中使用和号和行号。下面是一个查询,它为您提供了所需的输出。
select x.*, case when sum(match) over (partition by column1) >= 1 then rnk else null end final_rank
from (select column1, column2, row_number() over (partition by column1 order by column2) rnk,
(case when column2 = 'Match' then 1 else 0 end) match
from table a) xhttps://stackoverflow.com/questions/34188498
复制相似问题