2008년 1월 7일 월요일

ORACLE - Multiple distinct combinations

Let's assume we have a table T with the following columns:



dc1 dc2 fc1 fc2 lc2
1 1 11 12 21
1 1 11 12 22
1 1 11 12 23
2 2 22 22 31
3 3 33 35 32
3 3 33 37 32


I want a SQL query that returns all the rows that satsfy the following criteria: dc1, dc2 has multiple distinct fc1, lc2 combinations (count > 1). For the above sample data, the SQL would return:


dc1 dc2 fc1 fc2 lc2
1 1 11 12 21
1 1 11 12 22
1 1 11 12 23


-------------------------------------------------------------------------------------------
The solution is straightforward and utilizes COUNT DISTINCT:



select T.*
from T
inner join (
select dc1
, dc2
from T
group
by dc1
, dc2
having count( distinct fc1,lc2 ) > 1
)
as multiples
on T.dc1 = multiples.dc1
and T.dc2 = multiples.dc2



Unfortunately, not every database system supports COUNT DISTINCT.
For those that don't, you can obtain the correct results, perhaps not as efficiently, with concatenation or some other expression to combine the two column values. For example, using Microsoft SQL Server syntax --



select T.*
from T
inner
join (
select dc1
, dc2
from T
group
by dc1
, dc2
having count( distinct
cast(fc1 as
varchar)
+cast(lc2 as varchar) ) > 1
) as multiples
on T.dc1 = multiples.dc1
and T.dc2 = multiples.dc2

댓글 없음: