MSSQL2000统计、查询、过滤有关问题

MSSQL2000统计、查询、过滤问题

表名：test

想达到的效果是, 过滤掉userid重复的数据，只显示一条数据，统计userid的总量。并列出全部字段供调用

SQL code

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 ...

SQL code

select *,count(1) over(partition by userid) As px from test as a where a.id=(select max(id) from test where userid=a.userid)
[解决办法]
SQL code
with t as (select *,row_number() over(partition by userid order by id desc) as rnfrom tb1 )select * from t where rn=1;
[解决办法]
SQL code
select * from test a where not exists(select 1 from test b where a.userid =b.userid and a.id<b.id)
[解决办法]
SQL code
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)
[解决办法]
SQL code
select *,count(1) as num over(partition by userid)  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