oracle 仅当有多个结果时如何返回行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11258850/
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
How to return rows only if there is more than one result
提问by WolveFred
In a Oracle 10g database, I would like to build a SQL query that returns the result rows only if there is more than one row result.
在 Oracle 10g 数据库中，我想构建一个 SQL 查询，该查询仅在有多个行结果时才返回结果行。
Is it possible and how ?
有可能吗？
回答by mcha
you need to count the number of returned results, if you dont want to make a group by, you can use the following :
您需要计算返回结果的数量，如果您不想进行分组，则可以使用以下方法：
SELECT *
FROM (SELECT col1,
col2,
COUNT(*) OVER() cnt
FROM your_table
WHERE <conditions> )
WHERE cnt > 1
回答by manurajhada
select * from
(select column1,column2,column3,
(select count(*) from table1 where '*XYZ Condition*' ) as rowCount
from table1 where '*XYZ Condition*')
where rowCount > 1;
You just need to put same condition at both place in query i.e. 'XYZ Condition' is same at both whereclause.
您只需要在查询中的两个位置放置相同的条件，即“ XYZ 条件”在两个where子句中都相同。
回答by Dongqing
Do you need like the following result?
你需要像下面这样的结果吗？
c1 c2
1 AA
1 BB
2 CC
result:
结果：
c1 c2
1 AA,BB
2 CC
The following can meet your requirements.
以下内容可以满足您的要求。
select c1,ltrim(sys_connect_by_path(c2,','),',') from (
select c1,c2, row_number() over(partition by c1 order by c2)rn,
count(*) over(partition by id ) cnt from XXX -- XXX: your table
) a where level=cnt
start with rn=1 connect by prior c1=c1 and prior rn=rn-1
