SQL Oracle：仅在关键字的第一行输出'Count over Partition by'
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/28348382/
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 : 'Count over Partition by' output on first row of the keyword alone
提问by smilyface
I have to query a table which will look like as follows.
我必须查询一个如下所示的表。
select count(*) over (PARTITION BY offer_status) as count, name, status
from tablename
Output will be as:
3 | name1 | entered
3 | name1 | entered
3 | name2 | cleared
1 | name3 | completed
3 | name3 | cleared
3 | name1 | entered
3 | name2 | cleared
输出将是：
3 | 姓名1 | 进入
3 | 姓名1 | 进入
3 | 姓名2 | 清除
1 | 姓名3 | 完成
3 | 姓名3 | 清除
3 | 姓名1 | 进入
3 | 姓名2 | 清除
I would like to get it as :
3 | name1 | entered
| name3 | entered
3 | name2 | cleared
1 | name3 | completed
| name3 | cleared
| name3 | entered
| name3 | cleared
我想得到它：
3 | 姓名1 | 进入
| 姓名3 | 进入
3 | 姓名2 | 清除
1 | 姓名3 | 完成
| 姓名3 | 清除
| 姓名3 | 进入
| 姓名3 | 清除
To get the count of status only for the first occurance of the keyword (of status), as it is not necessary to get the count again and again.
仅获取关键字（of status）第一次出现的状态计数，因为没有必要一次又一次地获取计数。
Or you can suggest me any other way to get it done.
或者你可以建议我任何其他方式来完成它。
回答by Zajonc
I would do something like:
我会做这样的事情：
SELECT CASE WHEN rn=1 THEN cnt END cnt, order_name, status
FROM
(
SELECT count(*) OVER (PARTITION BY status) cnt,
ROW_NUMBER() OVER (PARTITION BY status ORDER BY order_name) rn,
order_name,status
FROM input_table
)
See SQL Fiddle
请参阅SQL 小提琴
As Gordon Linoff said, you need some sort of ordering. I ordered them by order_name but if you have some other field in the table you could use that instead.
正如 Gordon Linoff 所说，您需要某种排序。我按 order_name 对它们进行了排序，但是如果表中有其他字段，则可以改用它。
