查询以查找表中名称和数字之间的重复项 [英] Query to find the duplicates between the name and number in table

问题描述

SELECT count(*), lower(name), number
FROM   tbl
GROUP  BY lower(name), number
HAVING count(*) > 1;

输入tb1

slno name number
1    aaa   111
2    Aaa   111
3    abb   221
4    Abb   121
5    cca   131
6    cca   141
7    abc   222
8    cse   222

这个查询只能找到编号和名称相同的重复项，但无法找到第 3 行和第 4 行的重复项！！！

This query can just find the duplicates in the number and names which are same but it wont be able find the duplicates in the 3 and 4th row!!!

 SELECT count(*), lower(name)
    FROM   tbl
    GROUP  BY lower(name)
    HAVING count(lower(name)) > 1

此查询可以找到名称中的所有重复项！！！完美运行

this query can find all the duplicates in name!!! it works perfectly

 SELECT count(*), number
    FROM   tbl
    GROUP  BY number
    HAVING count(number) > 1

此查询可以找到 number 中的所有重复项！！！完美运行

this query can find all the duplicates in number!!! it works perfectly

我想要一个查询，它可以找到名称和数字中的所有重复项，名称是否由小写和大写组成

I want a query which can find all the duplicates in both name and number whether the name consists of lower case and upper case

output
name number count
2    111     aaa
2    ---     abb
2    ---     cca
2    222     ---

推荐答案

更新问题

获取号码和姓名的重复";...名称和编号作为不同的列"
这里可以计算两次行！

Updated question

"Get duplicate on both number and name" ... "name and number as different column"
Rows can be counted twice here!

SELECT lower(name), NULL AS number, count(*) AS ct
FROM   tbl
GROUP  BY lower(name)
HAVING count(*) > 1

UNION  ALL
SELECT NULL, number, count(*) AS ct
FROM   tbl
GROUP  BY number
HAVING count(*) > 1;

->sqlfiddle

问题在于查询分组

GROUP  BY lower(name), number

由于第 3 行和第 4 行具有不同 编号，因此对于此查询，它们不相同.

As row 3 and 4 have a different number, they are not the same for this query.

如果您想忽略此查询的不同数字，请尝试以下操作:

If you want to ignore different numbers for this query, try something like:

SELECT lower(name)
     , count(*) AS ct
FROM   tbl
GROUP  BY lower(name)
HAVING count(*) > 1;

这篇关于查询以查找表中名称和数字之间的重复项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！