目前表里面的数据记录。
id color_nameparent_id
1 Blue
10
2 Blue
10
3 Red
10
4 Red
10
期望得到结果
id color_nameparent_idcolor_num
1 Blue
10
2
2 Blue
10
2
3 Red
10
2
4 Red
10
2
SQL> create table a(id number,color_name char(1),parent_id number);
表已创建。
SQL> select a.*,count(distinct color_name) over(partition by parent_id order by
1) color_num
2from a a;
select a.*,count(distinct color_name) over(partition by parent_id order by 1) co
lor_num
*
第 1 行出现错误:
ORA-30487: ORDER BY 在此禁用
果然,那就去掉咯,反正order by在这里没啥意义
SQL> select * from a;
ID COPARENT_ID
---------- -- ----------
1 a
10
2 a
10
3 a
10
4 b
11
5 c
10
6 c
11
已选择6行。
SQL> select a.*,count(distinct color_name) over(partition by parent_id) color_nu
m
2from a;
ID COPARENT_IDCOLOR_NUM
---------- -- ---------- ----------
1 a
10
2
2 a
10
2
3 a
10
2
5 c
10
2
4 b
11
2
6 c
11
2
已选择6行。
SQL>
原帖由 CaptainKobe 于 2010-1-13 22:05 发表
SQL> create table a(id number,color_name char(1),parent_id number);
表已创建。
SQL> select a.*,count(distinct color_name) over(partition by parent_id order by
1) color_num
2from a a;
select a.*,count(distinct color_name) over(partition by parent_id order by 1) co
lor_num
*
第 1 行出现错误:
ORA-30487: ORDER BY 在此禁用
果然,那就去掉咯,反正order by在这里没啥意义