问题描述:

Say you have a table like this:

ID | Type | Reference #1 | Reference #2

0 | 1 | [A] | {a}

1 | 2 | [B] | {b}

2 | 2 | [B] | {c}

3 | 1 | [C] | {d}

4 | 1 | [D] | {d}

5 | 1 | [E] | {d}

6 | 1 | [C] | {e}

Is there any good way to group by "Reference #1" and "Reference #2" as a "fallback", for lack of a better way of putting it...

For example, I would like to group the following IDs together:

{0} [Unique Reference #1],

{1,2} [Same Reference #1],

{3,4,5,6} [{3,4,5} have same Reference #2 and {3,6} have same Reference #1]

I am at a total loss as to how to do this... Any thoughts?

网友答案:

In mellamokb's query, the groupings are dependent on the order of the input.

ie.

VALUES
    (0, 1, '[A]', '{a}'),
    (1, 2, '[B]', '{b}'),
    (2, 2, '[B]', '{c}'),
    (3, 1, '[C]', '{d}'), // group 3
    (4, 1, '[D]', '{d}'), // group 3
    (5, 1, '[E]', '{d}'), // group 3
    (6, 1, '[C]', '{e}'); // group 3

produces a different result tahn

VALUES
    (0, 1, '[A]', '{a}'),
    (1, 2, '[B]', '{b}'),
    (2, 2, '[B]', '{c}'),
    (3, 1, '[C]', '{e}'), //group 3
    (4, 1, '[D]', '{d}'), // group 4
    (5, 1, '[E]', '{d}'), // group 4
    (6, 1, '[C]', '{d}'); // group 3

This might be intended, if there is some natural order to the References that you could specify, but its a problem if they are not. The way to 'solve' this or specify another problem is to say that all equal Reference1s create a set of elements whose members are themselves and those elements whose Reference2 is equal to at least one member of that set.

In SQL:

with groupings as (
  select
    ID,Reference1,Reference2,
    (select min(ID) from Table1 t2
     where t2.Reference1=t1.Reference1 or t2.Reference2=t1.Reference2 ) as minID
  from
    Table1 t1
)
select
    t1.ID,t1.Reference1,t1.Reference2,t1.minid as round1,
    (select min(t2.minid) from
            groupings t2
      INNER JOIN groupings t3 ON t1.Reference2=t2.Reference2
) as minID
  from
    groupings t1

This should produce the full grouping each time.

相关阅读:
Top