表名:test想达到的效果是, 过滤掉userid重复的数据,只显示一条数据,统计userid的总量。并列出全部字段供调用
id userid txet1 txet2 text3 ......
-----------------------------------
1 3 23 hhh uuu ...
2 3 27 qqq iii ...
3 4 32 www ooo ...
4 2 12 rrr ppp ...
5 6 32 ttt sss ...
6 2 10 yyy fff ...想得到的结果:id userid txet1 txet2 text3 px ......
-----------------------------------
2 3 27 qqq iii 50 ...
3 4 32 www ooo 32 ...
5 6 32 ttt sss 32 ...
6 2 10 yyy fff 22 ...
with t as (
select *,row_number() over(partition by userid order by id desc) as rn
from tb1 )
select * from t where rn=1;
select * from test a where not exists(select 1 from test b where a.userid =b.userid and a.id<b.id)
select *,px=(select SUM(txet1) from test c where a.userid =c.userid ) from test a where not exists(select 1 from test b where a.userid =b.userid and a.id<b.id)
from test a
where a.id=(select max(id) from test where userid=a.userid)
CREATE TABLE pysql(ID NUMBER,userid NUMBER,t1 NUMBER,t2 VARCHAR2(20),t3 VARCHAR2(20));INSERT INTO pysql VALUES(1,3,23,'hhh','uuu');
INSERT INTO pysql VALUES(2,3,27,'qqq','iii');
INSERT INTO pysql VALUES(3,4,32,'www','ooo');
INSERT INTO pysql VALUES(4,2,12,'rrr','ppp');
INSERT INTO pysql VALUES(5,6,32,'ttt','sss');
INSERT INTO pysql VALUES(6,2,10,'yyy','fff');
COMMIT;SELECT d.Userid, d.T1, d.T2, d.T3, d.t
FROM (SELECT c.Userid,
c.T1,
c.T2,
c.T3,
b.T1 t,
Row_Number() Over(PARTITION BY c.Userid ORDER BY c.Userid) Rn
FROM (SELECT a.Userid, SUM(T1) T1
FROM Pysql a
GROUP BY a.Userid) b,
Pysql c
WHERE b.Userid = c.Userid) d
WHERE d.Rn = 1