表AA_ID
A_NAME
表BB_ID
B_NAME
A_ID表CC_ID
C_NAME
B_ID
A_ID A_NAME B_ID B_NAME C_ID C_NAME
1 A1 1 B1 1 C1
1 A1 1 B1 2 C2
1 A1 1 B1 3 C3
2 A2 2 B2 4 C4
2 A2 2 B2 5 C5
2 A2 3 B3 6 C6
如何去掉B_ID重复的记录,只保留一个即可!
A_NAME
表BB_ID
B_NAME
A_ID表CC_ID
C_NAME
B_ID
A_ID A_NAME B_ID B_NAME C_ID C_NAME
1 A1 1 B1 1 C1
1 A1 1 B1 2 C2
1 A1 1 B1 3 C3
2 A2 2 B2 4 C4
2 A2 2 B2 5 C5
2 A2 3 B3 6 C6
如何去掉B_ID重复的记录,只保留一个即可!
贴出sql吧
SELECT A.A_ID,A.A_NAME,B.B_ID,B.B_NAME,C.C_ID,C.C_NAME
FROM
A,B,C
WHERE B.A_ID = A.A_ID AND B.B_ID = C.B_ID
FROM
A,B,C
WHERE B.A_ID = A.A_ID AND B.B_ID = C.B_ID
)
WHERE RN<2;
--只取每个b_id第一条数据
select * from (
SELECT A.A_ID,A.A_NAME,B.B_ID,B.B_NAME,C.C_ID,C.C_NAME,
row_number() over(partition by a.a_id order by b.b_id) rn
FROM A,B,C
WHERE B.A_ID = A.A_ID AND B.B_ID = C.B_ID )
where rn=1
FROM
A,B,C
WHERE B.A_ID = A.A_ID AND B.B_ID = C.B_ID
)
WHERE RN<2;
可以了
FROM
(SELECT
WA.FILE_DIGEST,WA.SHOT_TIME,WA.SHOT_DISTRICT,WA.PROGRAM_NAME,WA.TML_ID,TER.DECARDNUMBER,F.FILE_NAME,F.FILE_PATH,F.WATERMARK_FILE_STATUS,ROW_NUMBER()
OVER (PARTITION BY WA.FILE_DIGEST,WA.SHOT_TIME,WA.SHOT_DISTRICT,WA.PROGRAM_NAME,WA.TML_ID,TER.DECARDNUMBER ORDER BY FILE_NAME) RN
FROM
WATERMARK AS WA,IXTERMINAL AS TER,FILE_INFO AS F
WHERE WA.TML_ID = TER.TML_ID AND WA.FILE_DIGEST = F.FILE_DIGEST
)
WHERE RN<2为什么提示缺少右括号呢?
FROM
(SELECT
WA.FILE_DIGEST,WA.SHOT_TIME,WA.SHOT_DISTRICT,WA.PROGRAM_NAME,WA.TML_ID,TER.DECARDNUMBER,F.FILE_NAME,F.FILE_PATH,F.WATERMARK_FILE_STATUS,ROW_NUMBER()
OVER (PARTITION BY WA.FILE_DIGEST,WA.SHOT_TIME,WA.SHOT_DISTRICT,WA.PROGRAM_NAME,WA.TML_ID,TER.DECARDNUMBER ORDER BY FILE_NAME) RN
FROM
WATERMARK AS WA,IXTERMINAL AS TER,FILE_INFO AS F
WHERE WA.TML_ID = TER.TML_ID AND WA.FILE_DIGEST = F.FILE_DIGEST
)
WHERE RN<2
试试,试玩这个帖子可以接了吧,这么长了
FROM
(SELECT
WA.FILE_DIGEST,WA.SHOT_TIME,WA.SHOT_DISTRICT,WA.PROGRAM_NAME,WA.TML_ID,TER.DECARDNUMBER,F.FILE_NAME,F.FILE_PATH,F.WATERMARK_FILE_STATUS,ROW_NUMBER()
OVER (PARTITION BY WA.FILE_DIGEST,WA.SHOT_TIME,WA.SHOT_DISTRICT,WA.PROGRAM_NAME,WA.TML_ID,TER.DECARDNUMBER ORDER BY FILE_NAME) RN
FROM
WATERMARK AS WA,IXTERMINAL AS TER,FILE_INFO AS F
WHERE WA.TML_ID = TER.TML_ID AND WA.FILE_DIGEST = F.FILE_DIGEST
)
WHERE RN<2
FROM
(SELECT
WA.FILE_DIGEST,WA.SHOT_TIME,WA.SHOT_DISTRICT,WA.PROGRAM_NAME,WA.TML_ID,TER.DECARDNUMBER,F.FILE_NAME,F.FILE_PATH,F.WATERMARK_FILE_STATUS,ROW_NUMBER()
OVER (PARTITION BY WA.FILE_DIGEST,WA.SHOT_TIME,WA.SHOT_DISTRICT,WA.PROGRAM_NAME,WA.TML_ID,TER.DECARDNUMBER ORDER BY FILE_NAME) RN
FROM
WATERMARK WA,IXTERMINAL TER,FILE_INFO F
WHERE WA.TML_ID = TER.TML_ID AND WA.FILE_DIGEST = F.FILE_DIGEST
)
WHERE RN<2
-- 各表的数据是什么情况?各表有主外键么?主外键是什么?
-- 能贴出各表的创建语句不?-- 你只贴出要得到的结果,有什么用呢?
-- 不同的表结构(主外键是否有),同一个SQL语句,其结果也会受其是否有主外键的影响滴!
SELECT dbms_metadata.get_ddl('TABLE','TABLE_NAME') FROM dual;
-- 要不就这样吧:(懒得啰嗦的话)
with a as (SELECT A.A_ID,A.A_NAME,B.B_ID,B.B_NAME,C.C_ID,C.C_NAME
FROM A,B,C
WHERE B.A_ID = A.A_ID AND B.B_ID = C.B_ID )
select a1.a_id, a1.a_name, a1.b_id, a1.b_name, a1.c_id, a1.c_name
from a a1
where exists (select 1 from a a2
where a2.a_id=a1.a_id
and a2.b_id=a1.b_id
group by a2.a_id, a2.b_id
having min(a2.c_id)=a1.c_id);
FROM
A,B,C
WHERE B.A_ID = A.A_ID AND B.B_ID = C.B_ID