标题不好理解,举个例子,表x,4个字段a、b、c、d
a b c d
1 f s l
1 g f k
1 f s l
2 z n k
2 z n k
如何作查询,选出字段a和b组合的结果是重复的,如结果应为
a b c d
1 f s l
2 z n k
a b c d
1 f s l
1 g f k
1 f s l
2 z n k
2 z n k
如何作查询,选出字段a和b组合的结果是重复的,如结果应为
a b c d
1 f s l
2 z n k
where ta.rowid !=
(
select max(tb.rowid) from t tb
where ta.a = tb.a and
ta.b = tb.b
)
With t As (
Select '1' a, 'f' b, 's' c ,'l' d From dual Union All
Select '1', 'g', 'f', 'k' From dual Union All
Select '1', 'g', 'f', 'c' From dual Union All
Select '1', 'f' ,'s', 'l' From dual Union All
Select '2', 'z', 'n', 'k' From dual Union All
Select '2', 'z', 'n', 'k' From dual
) Select
Distinct t.*
From (
Select a,b From t
Group By a,b
Having Count(1) > 1
)t1,t
Where t.a = t1.a
And t.b = t1.b
from table1 t
where exists(select 1
from (select a,b
from table1
group by a,b having count(1) >= 2
) m
where m.a = t.a and m.b = t.b
)
(
select max(c) as c,a,b from [repeat]
group by a,b
)d
where
t.a=d.a and t.b=d.b and t.c=d.c
order by a----说明:
repeat表有 abcd 四列,可以随便插什么数据进去,这样查询的结果就是:
如果ab 两列有重复,只要C列值最大的那一行。----完解--------
with x as (
Select '1' a, 'f' b, 's' c ,'l' d From dual Union All
Select '1', 'g', 'f', 'k' From dual Union All
Select '1', 'f' ,'s', 'l' From dual Union All
Select '2', 'z', 'n', 'k' From dual Union All
Select '2', 'z', 'n', 'k' From dual
)
select distinct x.* from
(
select a,b from x group by a,b having count(1)>1
) y
join x
on x.a = y.a
and x.b = y.b
/*
A B C D
1 1 f s l
2 2 z n k
*/
group by a,b,c,d
having count(a||b)>1
查出来的结果是:
A B C D
-- -- -- --
1 f s l
1 f s l
2 z n k
FROM tt a
WHERE ROWID != (SELECT MAX(ROWID)
FROM tt b
WHERE a.a = b.a AND
a.b = b.b OR
a.a = b.b AND
a.b = b.a);
SELECT *
FROM tt a
WHERE ROWID = (SELECT MAX(ROWID)
FROM tt b
WHERE a.a = b.a AND
a.b = b.b OR
a.a = b.b AND
a.b = b.a);