Oracle SQL 隐藏重复值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6311464/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle SQL hiding duplicate values
提问by Michael
I have a query with these results:
我有这些结果的查询：
A | 1
A | 2
B | 1
B | 2
B | 3
How do I get the results to be like this:
我如何得到这样的结果：
A | 1
| 2
B | 1
| 2
| 3
回答by DCookie
Here is one way:
这是一种方法：
SELECT CASE WHEN rn = 1 THEN c1 ELSE NULL END || ' | ' || c2
FROM (SELECT c1, c2, ROW_NUMBER() OVER (PARTITION BY c1 ORDER BY c2) rn
FROM your_table);
回答by Lazer
You can use BREAK ONif you are using sqlplus:
您可以使用BREAK ON，如果你用sqlplus：
SQL> desc tab1;
Name Null? Type
----------------------------------------- -------- ----------------------------
COL VARCHAR2(1)
COL2 NUMBER
SQL> select * from tab1;
C COL2
- ----------
A 1
A 2
B 1
B 2
B 3
SQL> break on col;
SQL> select * from tab1;
C COL2
- ----------
A 1
2
B 1
2
3
SQL>
More details here.
更多细节在这里。
回答by Chandu
I vaugely remember thereis a way to get this format in SQL PLus.. Another possible way is as given below:
我模糊地记得有一种方法可以在 SQL PLus 中获取这种格式。另一种可能的方法如下所示：
SELECT a.COLUMN_1,
CASE
WHEN a.rnk = 1 THEN a.COLUMN_2
ELSE NULL
END AS COLUMN_2
FROM (
SELECT a.*,
RANK() OVER(PARTITION BY COLUMN_1 ORDER BY COLUMN2) rnk
FROM <YOUR_TABLE> a
) a
回答by Randy
in oracle, check out the LEADand LAGfunctions.
在 oracle 中，查看LEAD和LAG函数。
you can look at the previous row, and if it is the same as the current row, change the value to NULL.
可以查看上一行，如果与当前行相同，则将该值更改为NULL。
回答by Steve Mayne
This presentation requirement is best served in the application you're using to display the results, rather than in the raw SQL. You could meet your requirements with a cursor, but it's not a very elegant solution.
这种表示要求最好在您用来显示结果的应用程序中满足，而不是在原始 SQL 中。您可以使用光标来满足您的要求，但这不是一个非常优雅的解决方案。
回答by FrustratedWithFormsDesigner
This is more of a formatting issue, best solved by whatever you are using to display the output. There's nothing wrong with the query result, as a query result.
这更像是一个格式问题，最好通过您用来显示输出的任何内容来解决。查询结果没有任何问题，作为查询结果。
