我有一个varchar列的表,我想找到在这个列中有重复值的所有记录。我可以使用什么查询来查找重复项?
当前回答
我没有看到任何JOIN方法,它在复制方面有很多用途。
这种方法会给您带来实际的双倍结果。
SELECT t1.* FROM my_table as t1
LEFT JOIN my_table as t2
ON t1.name=t2.name and t1.id!=t2.id
WHERE t2.id IS NOT NULL
ORDER BY t1.name
其他回答
感谢@novocaine的精彩回答,他的解决方案对我很有效。我稍微改变了它,以包括一个百分比的循环值,这在我的例子中是需要的。以下是修改后的版本。它将百分比减少到小数点后两位。如果你把2改成0,它就不会显示小数,改成1,它就会显示一位小数,以此类推。
SELECT GROUP_CONCAT(id), name, COUNT(*) c,
COUNT(*) OVER() AS totalRecords,
CONCAT(FORMAT(COUNT(*)/COUNT(*) OVER()*100,2),'%') as recurringPecentage
FROM table
GROUP BY name
HAVING c > 1
假设您的表名为TableABC,您想要的列是Col, T1的主键是key。
SELECT a.Key, b.Key, a.Col
FROM TableABC a, TableABC b
WHERE a.Col = b.Col
AND a.Key <> b.Key
与上面的答案相比,这种方法的优点是它给出了Key。
SELECT *
FROM mytable mto
WHERE EXISTS
(
SELECT 1
FROM mytable mti
WHERE mti.varchar_column = mto.varchar_column
LIMIT 1, 1
)
ORDER BY varchar_column
这个查询返回完整的记录,而不仅仅是不同的varchar_column。
这个查询不使用COUNT(*)。如果有很多重复项,COUNT(*)是昂贵的,并且不需要整个COUNT(*),只需要知道是否有两行具有相同的值。
这是通过相关查询底部的LIMIT 1,1来实现的(本质上意味着“返回第二行”)。EXISTS只在前面提到的第二行存在时才返回true(即至少有两行具有相同的varchar_column值)。
当然,在varchar_column上建立索引将大大加快此查询的速度。
Select column_name, column_name1,column_name2, count(1) as temp from table_name group by column_name having temp > 1
一个非常晚的贡献…万一这能帮助到以后的任何人…我有一个任务是在一个银行应用程序中找到匹配的交易对(实际上是账户到账户转账的双方),以识别每个账户间转账交易的“从”和“到”,所以我们最终得到了这个:
SELECT
LEAST(primaryid, secondaryid) AS transactionid1,
GREATEST(primaryid, secondaryid) AS transactionid2
FROM (
SELECT table1.transactionid AS primaryid,
table2.transactionid AS secondaryid
FROM financial_transactions table1
INNER JOIN financial_transactions table2
ON table1.accountid = table2.accountid
AND table1.transactionid <> table2.transactionid
AND table1.transactiondate = table2.transactiondate
AND table1.sourceref = table2.destinationref
AND table1.amount = (0 - table2.amount)
) AS DuplicateResultsTable
GROUP BY transactionid1
ORDER BY transactionid1;
The result is that the DuplicateResultsTable provides rows containing matching (i.e. duplicate) transactions, but it also provides the same transaction id's in reverse the second time it matches the same pair, so the outer SELECT is there to group by the first transaction ID, which is done by using LEAST and GREATEST to make sure the two transactionid's are always in the same order in the results, which makes it safe to GROUP by the first one, thus eliminating all the duplicate matches. Ran through nearly a million records and identified 12,000+ matches in just under 2 seconds. Of course the transactionid is the primary index, which really helped.