表t1, 详细设计如下:
father, children, children_number
A A1 10
A A2 13
A A3 18
B B1 1
B B2 3
有没有办法写一个SQL,同一个father下根据children_number的大小产生序号.如:
father, children, children_number,order
A A1 10 1
A A2 13 2
A A3 18 3
B B1 1 1
B B2 3 2
注:不是整张表编序号,而是在同一个father下给children编号,序号的先后由number的大小决定谢谢!
father, children, children_number
A A1 10
A A2 13
A A3 18
B B1 1
B B2 3
有没有办法写一个SQL,同一个father下根据children_number的大小产生序号.如:
father, children, children_number,order
A A1 10 1
A A2 13 2
A A3 18 3
B B1 1 1
B B2 3 2
注:不是整张表编序号,而是在同一个father下给children编号,序号的先后由number的大小决定谢谢!
row_number() over(partition by father order by children_number ) order
row_number() over(partition by father order by children_number ) order
from t1
--row_number() 连续的号,相同大小的排序列,给的号不同
--rank() 相同大小的值排序,序列号相同
--DENSE_RANK() 类似rank(),但是不跳号
SELECT FATHER,
CHILDREN,
CHILDREN_NUMBER,
ROW_NUMBER() OVER(PARTITION BY FATHER ORDER BY CHILDREN_NUMBER) ORDER1,
RANK() OVER(PARTITION BY FATHER ORDER BY CHILDREN_NUMBER) ORDER2,
DENSE_RANK() OVER(PARTITION BY FATHER ORDER BY CHILDREN_NUMBER) ORDER3,
FROM T1;