MEMBER_ID LINE_TYPE_B COUNT(LINE_ID)
1 2 14 29
2 2 10 29
3 2 15 7
4 2 8 2
5 4 14 29
6 4 8 16
7 4 19 2
8 4 22 1
9 4 25 1
10 5512 8 29
11 6860 17 4
12 10498 10 32
13 10498 13 30
14 10905 25 6
15 11642 12 30
16 11642 11 30这一个结果集中我外面再套一层,需要得出每个MEMBER_ID中COUNT(LINE_ID)最大的那个LINE_TYPE_B. MEMBER_ID LINE_TYPE_B COUNT(LINE_ID)
1 2 14 29
5 4 14 29
10 5512 8 29
11 6860 17 4
12 10498 10 32
14 10905 25 6
15 11642 12 30
1 2 14 29
2 2 10 29
3 2 15 7
4 2 8 2
5 4 14 29
6 4 8 16
7 4 19 2
8 4 22 1
9 4 25 1
10 5512 8 29
11 6860 17 4
12 10498 10 32
13 10498 13 30
14 10905 25 6
15 11642 12 30
16 11642 11 30这一个结果集中我外面再套一层,需要得出每个MEMBER_ID中COUNT(LINE_ID)最大的那个LINE_TYPE_B. MEMBER_ID LINE_TYPE_B COUNT(LINE_ID)
1 2 14 29
5 4 14 29
10 5512 8 29
11 6860 17 4
12 10498 10 32
14 10905 25 6
15 11642 12 30
解决方案 »
- oracle小白求教在V$sqlarea 中SQL_TEXT查看执行过的SQL语句
- 关于oracle存储过程及事物的问题
- oracle游标打开机制
- (linux)多线程写数据库,iostat显示%user经常为0,入库效率很低
- 在sql plus查询中后面的Excution plan中的cost,card,byte代表什么意思?
- 产关于函数反加回值问题
- ORACLE日期运算求助!
- 请问可不可以在一个存储过程多次打开关闭同一个游标??
- exp/imp 需要那些文件才能运行?
- oracle的安裝
- sql navigator的执行动作在后台有没有相应的sql语句生成,如果有的话怎么查看?
- 行列转换问题(一得到答案就给分)
max(line_ytpe_b),
line_cnt
from (select numberid,
line_type,
count(line_id) line_cnt
from table1
group by number_id,line_type
)
group by number_id,line_cnt
INSERT INTO TT VALUES (2, 14, 29);
INSERT INTO TT VALUES (2, 10, 29);
INSERT INTO TT VALUES (2, 15, 7);
INSERT INTO TT VALUES (2, 8, 2);
INSERT INTO TT VALUES (4, 14, 29);
INSERT INTO TT VALUES (4, 8, 16);
INSERT INTO TT VALUES (4, 19, 2);
INSERT INTO TT VALUES (4, 22, 1);
INSERT INTO TT VALUES (4, 25, 1);
INSERT INTO TT VALUES (5512, 8, 29);
INSERT INTO TT VALUES (6860, 17, 4);
INSERT INTO TT VALUES (10498, 10, 32);
INSERT INTO TT VALUES (10498, 13, 30);
INSERT INTO TT VALUES (10905, 25, 6);
INSERT INTO TT VALUES (11642, 12, 30);
INSERT INTO TT VALUES (11642, 11, 30);
COMMIT;
SELECT *
FROM TT
WHERE (MEMBER_ID, CC) IN
(SELECT MEMBER_ID, MAX(CC)
FROM TT
GROUP BY MEMBER_ID);
SELECT MEMBER_ID, LINE_TYPE_B, CC
FROM (SELECT TT.*,
ROW_NUMBER() OVER(PARTITION BY MEMBER_ID ORDER BY LINE_TYPE_B DESC) RN
FROM TT
WHERE (MEMBER_ID, CC) IN
(SELECT MEMBER_ID, MAX(CC) FROM TT GROUP BY MEMBER_ID))
WHERE RN = 1;