显示重复的行号 [英] Displaying duplicate row numbers

问题描述

我加入了一个表，以查找重复的行

  select a.data，a.rowNumber，b.rowNumber 
从DuplicateRows作为
加入DuplicateRows作为b 
 
 a.data = b.data和a.Id！= b.Id 
 group by a.data， a.rowNumber，b.rowNumber 
  

此查询给出的结果如

 content| 1 | 2 
content| 1 | 6 
content| 2 | 1 
content| 2 | 6 
 ...等等
  

如果我重写它，使结果形成为

 content| 1 | 2，6 
  

编辑

我认为这个问题应该有点纠正。
你看我不想得到反转的结果，我的意思是我只想得到
一个条目

 `1  - > 2，6` 
  

不

 `1  - > 2，6和`2  - > 1，6` 
  

谢谢！

解决方案

根据您的最新编辑，我认为这是您需要的：

  SELECT a.data，aa，GROUP_CONCAT（DISTINCT ba）
 FROM TableName as a 
 JOIN TableName as b 
 ON a.data = b.data AND aa< ba 
 GROUP BY a.data 
  

SQLFiddle Demo

PS

编辑

一个比较好的此查询的结果：

  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 
  

小提琴是这里。

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!

解决方案

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.

这篇关于显示重复的行号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！