需求:筛选掉CREATE_TIME字段重复的数据,并显示整行信息
SELECT
ID,PTN_ID,CREATE_TIME
FROM
PR_T_TICKET_NEWSPAPER_DETAILS
WHERE PTN_ID IN
('1252786559219220482','1252755733517479939')
可以看到,根据原生SQL查出的数据如上图所示,CREATE_TIME这个字段下有两条是跟其他数据重复的,试了distinct,group by等关键字去重筛选,还是没有效果
解决办法:
1.首先把问题表加上伪列(rownum)
SELECT ROWNUM rn,a.*
FROM
( SELECT
ID,PTN_ID,CREATE_TIME
FROM PR_T_TICKET_NEWSPAPER_DETAILS
WHERE PTN_ID IN
('1252786559219220482','1252755733517479939')) a
SELECT ROWNUM,a.* FROM 你的表 a
2.然后再根据用过伪列的sql当作一张表进行子查询
子查询且分组
SELECT
*
FROM
(
SELECT ROWNUM rn,a.*
FROM
( SELECT
ID,PTN_ID,CREATE_TIME
FROM PR_T_TICKET_NEWSPAPER_DETAILS
WHERE PTN_ID IN
('1252786559219220482','1252755733517479939')) a
) B
WHERE
rn IN (
SELECT MIN( rn )
FROM
(SELECT ROWNUM rn,a.*
FROM
( SELECT
ID,PTN_ID,CREATE_TIME
FROM PR_T_TICKET_NEWSPAPER_DETAILS
WHERE PTN_ID IN
('1252786559219220482','1252755733517479939')) a
) B
GROUP BY CREATE_TIME)
效果如下:
可以看到 已经将原来的查询结果根据CREATE_TIME这个字段做了去重,两条重复的数据已经被筛选掉了
模板参考
SELECT *
FROM 你的表
WHERE 伪列名称 IN
( SELECT MAX (伪列名称)
FROM 你的表
GROUP BY 你要分组的列) --也可以进行where 筛选