表"nb_camp_scenes_result"的结构及其数据如下:
NO IMSI CAMP_ID INSERT_TIME MSISDN FLAG
1 460001608078545 08081203341234 2008-09-11 10:56:11 13980988195 0
2 460001608078546 08082403341255 2008-09-11 10:56:19 13801971849 0
3 460001811291658 08082403341255 2008-09-11 10:56:23 13817306709 0
4 460028216028871 08082403341255 2008-09-11 10:56:24 13917380742 0
5 460028216028871 08082403341255 2008-09-11 10:56:25 13918181105 0
6 460001608078550 08082703341234 2008-09-11 10:56:25 13918181105 0
7 460001608078550 08082603341234 2008-09-11 10:56:25 13918181105 0
8 460001608078550 08082503341234 2008-09-11 10:56:25 13918181105 0
9 460001608078550 08082903341234 2008-09-11 10:56:25 13918181105 0
10 460028009113928 08082403341234 2008-09-11 10:56:10 8615800945428 0
11 460008303110541 08082403341238 2008-09-11 10:56:39 13540444865 0
12 460001608078546 08082403341255 2008-09-11 10:56:10 13540428802 0我现在想提取出这样的数据:
NO IMSI CAMP_ID INSERT_TIME MSISDN FLAG
1 460001608078545 08081203341234 2008-09-11 10:56:11 13980988195 0
2 460001608078546 08082403341255 2008-09-11 10:56:19 13801971849 0
3 460001811291658 08082403341255 2008-09-11 10:56:23 13817306709 0
4 460028216028871 08082403341255 2008-09-11 10:56:24 13917380742 0
9 460001608078550 08082903341234 2008-09-11 10:56:25 13918181105 0
10 460028009113928 08082403341234 2008-09-11 10:56:10 8615800945428 0
11 460008303110541 08082403341238 2008-09-11 10:56:39 13540444865 0
12 460001608078546 08082403341255 2008-09-11 10:56:10 13540428802 0也就是说,我现在想去除表"nb_camp_scenes_result"中有重复MSISDN的数据,并且保留重复MSISDN数据中
最大CAMP_ID的行.请问这样的SQL语句怎么写?
NO IMSI CAMP_ID INSERT_TIME MSISDN FLAG
1 460001608078545 08081203341234 2008-09-11 10:56:11 13980988195 0
2 460001608078546 08082403341255 2008-09-11 10:56:19 13801971849 0
3 460001811291658 08082403341255 2008-09-11 10:56:23 13817306709 0
4 460028216028871 08082403341255 2008-09-11 10:56:24 13917380742 0
5 460028216028871 08082403341255 2008-09-11 10:56:25 13918181105 0
6 460001608078550 08082703341234 2008-09-11 10:56:25 13918181105 0
7 460001608078550 08082603341234 2008-09-11 10:56:25 13918181105 0
8 460001608078550 08082503341234 2008-09-11 10:56:25 13918181105 0
9 460001608078550 08082903341234 2008-09-11 10:56:25 13918181105 0
10 460028009113928 08082403341234 2008-09-11 10:56:10 8615800945428 0
11 460008303110541 08082403341238 2008-09-11 10:56:39 13540444865 0
12 460001608078546 08082403341255 2008-09-11 10:56:10 13540428802 0我现在想提取出这样的数据:
NO IMSI CAMP_ID INSERT_TIME MSISDN FLAG
1 460001608078545 08081203341234 2008-09-11 10:56:11 13980988195 0
2 460001608078546 08082403341255 2008-09-11 10:56:19 13801971849 0
3 460001811291658 08082403341255 2008-09-11 10:56:23 13817306709 0
4 460028216028871 08082403341255 2008-09-11 10:56:24 13917380742 0
9 460001608078550 08082903341234 2008-09-11 10:56:25 13918181105 0
10 460028009113928 08082403341234 2008-09-11 10:56:10 8615800945428 0
11 460008303110541 08082403341238 2008-09-11 10:56:39 13540444865 0
12 460001608078546 08082403341255 2008-09-11 10:56:10 13540428802 0也就是说,我现在想去除表"nb_camp_scenes_result"中有重复MSISDN的数据,并且保留重复MSISDN数据中
最大CAMP_ID的行.请问这样的SQL语句怎么写?
from (select *,
rownnumber() over(partition by INSERT_TIME order by no desc) rn
from nb_camp_scenes_result) where rn=1
SQL> SELECT * FROM TEST_TT;FIELD1 FIELD2
---------- ----------
A 5
A 6
A 1
A 18
B 3
B 13
B 14
C 198 rows selectedSQL>
SQL> SELECT *
2 FROM TEST_TT T1
3 WHERE NOT EXISTS (SELECT 1
4 FROM TEST_TT T2
5 WHERE T1.FIELD1 = T2.FIELD1
6 AND T1.ROWID < T2.ROWID);FIELD1 FIELD2
---------- ----------
C 19
B 14
A 18SQL>
SQL> SELECT *
2 FROM TEST_TT T1
3 WHERE ROWID = (
4 SELECT MAX(ROWID)
5 FROM TEST_TT T2
6 WHERE T1.FIELD1 = T2.FIELD1
7 );FIELD1 FIELD2
---------- ----------
A 18
B 14
C 19SQL>
select NO, IMSI, CAMP_ID, INSERT_TIME, MSISDN, FLAG
from (select *,
rownnumber() over(partition by MSISDN order by no desc) rn
from nb_camp_scenes_result) where rn=1
select NO, IMSI, CAMP_ID, INSERT_TIME, MSISDN, FLAG
from (select NO,
IMSI,
CAMP_ID,
INSERT_TIME,
MSISDN,
FLAG,
row_number() over(partition by MSISDN order by no desc) rn
from nb_camp_scenes_result)
where rn = 1
order by 1
from (select NO,
IMSI,
CAMP_ID,
INSERT_TIME,
MSISDN,
FLAG,
rownnumber() over(partition by MSISDN order by CAMP_ID desc) rn
from nb_camp_scenes_result) where rn=1正解
from nb_camp_scenes_result
group by IMSI最后的count(IMSI) group by IMSI和前面的所有集合相当与连接起来
最后就是多了一行计算不同IMSI的计数。