例如:
item1 item2 name subject
1 1 lily maths
1 2 lily english
1 3 lily chinese
2 1 lucy maths
2 2 lucy english
现有的数据位 lily maths
lily english
lily chinese
lucy maths
lucy english
item1 item2 name subject
1 1 lily maths
1 2 lily english
1 3 lily chinese
2 1 lucy maths
2 2 lucy english
现有的数据位 lily maths
lily english
lily chinese
lucy maths
lucy english
insert into table (item1, item2, name, subject)
select tt.item1 , rank() over(partition by tt.rr order by tt.tml_type_cd,tt.tml_id) as item2, tt.name , tt.subject from (select dense_rank() over(order by t.name ) as item1,t.name,t.subject from table1 t ) tt;
呃,写错了....因为用的是我自己的表....改的时候没改完...
rank() over(partition by tt.rr order by tt.tml_type_cd,tt.tml_id) as item2
改成
rank() over(partition by tt.rr order by tt.name,tt.subject) as item2