大家好。请帮忙解决一个sql问题我有一个表, student: ID,Name,Chengji数据如下:
id name chengji
1 zhang san 2
2 lisi 3
3 wangwu 2
4 zhaoliu 3
5 mati 4
检索结果如下:id name chengji
1 wangwu 2
1 zhang san 2
2 lisi 3
2 zhaoliu 3
3 mati 4
id name chengji
1 zhang san 2
2 lisi 3
3 wangwu 2
4 zhaoliu 3
5 mati 4
检索结果如下:id name chengji
1 wangwu 2
1 zhang san 2
2 lisi 3
2 zhaoliu 3
3 mati 4
INSERT INTO stuname1(
id, "name", score)
VALUES (1, 'zhangsan', 10);INSERT INTO stuname1(
id, "name", score)
VALUES (2, 'lisi', 10);
INSERT INTO stuname1(
id, "name", score)
VALUES (3, 'wangwu', 20);INSERT INTO stuname1(
id, "name", score)
VALUES (4, 'zhaoliu', 30);
INSERT INTO stuname1(
id, "name", score)
VALUES (5, 'maqi', 20);然后修改下查询select row_number()over(PARTITION BY t.score) ,t.name ,t.score from
stuname1 t检索结果如下:1;"zhangsan";10
2;"lisi";10
1;"wangwu";20
2;"maqi";20
1;"zhaoliu";30
实际上 rownumber 应该有三个,而不是两个,问题在哪呢?
inner join
(select rank() over( order by score) as px,score from
(select distinct score from stuname1 ) a) b
on a1.score=b.score