name id NO.
张三 1 1
张三 1 2
张三 2 3
张三 2 2需要得的结果是
张三 2 5就是把同名字求出同id的sum
name id No.
张三 1 3
张三 2 5然后输出No最大的5
张三 2 5请问这个sql怎么样写啊?谢谢了
张三 1 1
张三 1 2
张三 2 3
张三 2 2需要得的结果是
张三 2 5就是把同名字求出同id的sum
name id No.
张三 1 3
张三 2 5然后输出No最大的5
张三 2 5请问这个sql怎么样写啊?谢谢了
from (
select name,id,no,row_number()over(partition by name order by no desc) rn
from (
select name,id,sum(NO) no
from tablename
group by name,id ) )
where rn=1
;
select name,id,dense_rank() over(order by no desc) rn
from (select name,id,sum(no) no
from t_13
group by name,id)) where rn=1
from(
select a.* row_number() over(partition by name order by no. desc) rnselect name,id,sum(no.) no.
from a
group by name,id
)
where rn=1
select name,id,sum(no)no
from table
group by name,id
order by no desc
)a
where rownum=1
SELECT NAME,
Id,
SUM(Nvl(No, 0)) No
FROM table
GROUP BY NAME,
Id
ORDER BY No DESC)
SELECT * FROM RESULT WHERE Rownum = 1;
FROM(SELECT name,id,SUM(no) no
FROM table
GROUP BY name,id) a,
(SELECT name,id,MAX(SUM(no)) no
FROM table
GROUP BY name,id) b
WHERE a.name=b.name AND a.id=b.id
insert into tb_a values('张三',1,1);
insert into tb_a values('张三',1,2);
insert into tb_a values('张三',2,3);
insert into tb_a values('张三',2,2);
commit;
select * from tb_a t;select name, id, no
from (select name, id, sum(no) no
from tb_a
group by name, id
order by no desc) a
where rownum = 1
FROM (SELECT A.NAME,
A.ID,
SUM(A.NO) SUMNO,
ROW_NUMBER() OVER(ORDER BY SUM(A.NO) DESC) SUMNOGRD
FROM CSDN A
GROUP BY A.NAME, A.ID) B
WHERE B.SUMNOGRD = 1;