Displaying duplicate row numbers

问题:

I joined a table to itself to find duplicate rows

select a.data, a.rowNumber, b.rowNumber
from DuplicateRows as a
join DuplicateRows as b 
on
a.data = b.data and a.Id != b.Id
group by a.data, a.rowNumber, b.rowNumber

This query gives the results like

"content" | 1 | 2
"content" | 1 | 6
"content" | 2 | 1
"content" | 2 | 6 
...and so on

Howcome I rewrite it to have results formed like

"content" | 1 | 2, 6

EDIT

I think the question should be a little bit corrected. You see I don't want to get the inversed result, I mean I just want to get one entry

`1 -> 2, 6` 

not

`1 -> 2, 6 and `2 -> 1, 6`

Thanks!

回答1:

According to your latest edit, I think this is what you'll be needing:

SELECT a.data, a.a, GROUP_CONCAT( DISTINCT b.a)
FROM TableName as a
JOIN TableName as b 
    ON a.data = b.data AND a.a < b.a
GROUP BY a.data

SQLFiddle Demo

PS

I used(and modified) John Woo's table a little bit.

EDIT

A relatively better results from this query:

SELECT a.data, MIN(a.rowNumber), GROUP_CONCAT( DISTINCT b.rowNumber)
FROM DuplicateRows AS a
JOIN DuplicateRows AS b 
  ON a.data = b.data AND a.rowNumber < b.rowNumber
GROUP BY a.data

The fiddle is here.

回答2:

use GROUP_CONCAT

SELECT  a.data, 
        a.rowNumber, 
        GROUP_CONCAT(b.rowNumber)
FROM    DuplicateRows AS a
        INNER JOIN DuplicateRows AS b 
            ON  a.data = b.dataAND a.Id != b.Id
GROUP BY a.data, a.rowNumber

SQLFiddle Demo

回答3:

try GROUP_CONCAT function in mysql

select a.data, a.rowNumber, group_concat(b.rowNumber)
from DuplicateRows as a
join DuplicateRows as b 
on
a.data = b.data and a.Id != b.Id
group by a.data, a.rowNumber, b.rowNumber