Oracle/sql中如何根据两列进行分区
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/32636745/
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 partition based on two columns in Oracle/sql
提问by Ashish B Sathish
Please help me with the following
请帮我解决以下问题
Question:
题：
+------+----------+ | Name | Sub-name | +------+----------+ | A | x | | A | x | | B | x | | A | y | | B | y | +------+----------+
Desired Result:
预期结果：
+------+----------+-------+ | Name | Sub-name | Count | +------+----------+-------+ | A | x | 2 | | A | x | 2 | | B | x | 1 | | A | y | 1 | | B | y | 1 | +------+----------+-------+
Three columns Name, Subname, Count
三列名称、子名称、计数
I want to partition based on both name and subname.
我想根据名称和子名称进行分区。
回答by MT0
Oracle 11g R2 Schema Setup:
Oracle 11g R2 架构设置：
CREATE TABLE test ( Name, "Sub-name" ) AS
SELECT 'A', 'x' FROM DUAL
UNION ALL SELECT 'A', 'x' FROM DUAL
UNION ALL SELECT 'B', 'x' FROM DUAL
UNION ALL SELECT 'A', 'y' FROM DUAL
UNION ALL SELECT 'B', 'y' FROM DUAL;
Query 1:
查询 1：
SELECT Name,
"Sub-name",
COUNT( 1 ) OVER ( PARTITION BY "Sub-name", Name ) AS "Count"
FROM test
结果：
| NAME | Sub-name | Count |
|------|----------|-------|
| A | x | 2 |
| A | x | 2 |
| B | x | 1 |
| A | y | 1 |
| B | y | 1 |
回答by chris
Try this:
尝试这个：
select name, sub_name, count(name) over (partition by name, sub_name) as count from table
select name, sub_name, count(name) over (partition by name, sub_name) as count from table
回答by pari elanchezhiyan
select ra.Name,ra.sub-name,ta.count from table ra inner join (select Name,sub-name,count(*) from table group by Name,sub-name)ta on ra.Name=ta.Name on ra.sub-name=ta.sub-name order by sub-name desc
select ra.Name,ra.sub-name,ta.count from table ra inner join (select Name,sub-name,count(*) from table group by Name,sub-name)ta on ra.Name=ta.Name on ra.sub-name=ta.sub-name 按子名 desc 排序
Really i don't understand why we need to use partition for this solution.Even the above join query works fine...hope it should....
我真的不明白为什么我们需要为这个解决方案使用分区。即使上面的连接查询也能正常工作......希望它应该......
