是否计算Mysql表中的重复记录?

rkttyhzu  于 2023-06-21  发布在  Mysql
关注(0)|答案(8)|浏览(103)

我有一张table,结构如下。
tbl

id   name  
1    AAA
2    BBB
3    BBB
4    BBB
5    AAA
6    CCC

select count(name) c from tbl
group by name having c >1

返回此结果的查询:

AAA(2)  duplicate
BBB(3)  duplicate
CCC(1)  not duplicate

重复的名称为AAA和BBB。最后的结果,我想要的是这个重复记录的计数。
结果应该是这样的:重复产品总数(2

hvvq6cgz

hvvq6cgz1#

方法是使用一个嵌套查询,每个重复查询一行,外部查询只返回内部查询结果的计数。

SELECT count(*) AS duplicate_count
FROM (
 SELECT name FROM tbl
 GROUP BY name HAVING COUNT(name) > 1
) AS t
tcomlyy6

tcomlyy62#

使用IF语句获取所需的输出:

SELECT name, COUNT(*) AS times, IF (COUNT(*)>1,"duplicated", "not duplicated") AS duplicated FROM <MY_TABLE> GROUP BY name

输出:

AAA 2 duplicated
BBB 3 duplicated
CCC 1 not duplicated
5t7ly7z5

5t7ly7z53#

对于列表:

SELECT COUNT(`name`) AS adet, name
FROM  `tbl` WHERE `status`=1 GROUP BY `name`
ORDER BY `adet`  DESC

总数量:

SELECT COUNT(*) AS Total
    FROM (SELECT COUNT(name) AS cou FROM tbl GROUP BY name HAVING cou>1 ) AS virtual_tbl

//共5条

csga3l58

csga3l584#

为什么不把它 Package 在一个子查询中:

SELECT Count(*) TotalDups
FROM
(
    select Name, Count(*)
    from yourTable
    group by name
    having Count(*) > 1
) x

查看SQL Fiddle with Demo

ruarlubt

ruarlubt5#

接受的答案计算的是重复的行数,而不是重复的数量。如果要计算实际重复次数,请使用以下命令:

SELECT COALESCE(SUM(rows) - count(1), 0) as dupes FROM(

    SELECT COUNT(1) as rows
    FROM `yourtable`
    GROUP BY `name`
    HAVING rows > 1

) x

它的作用是将组中的重复项相加,然后减去具有重复项的记录的数量。原因是按总计分组并不都是重复的,这些分组中的每一个的一条记录是唯一的行。
小提琴:http://sqlfiddle.com/#!2/29639a/3

i1icjdpr

i1icjdpr6#

SQL代码是:

SELECT VERSION_ID, PROJECT_ID, VERSION_NO, COUNT(VERSION_NO) AS dup_cnt
FROM MOVEMENTS
GROUP BY VERSION_NO
HAVING (dup_cnt > 1 && PROJECT_ID = 11660)
vybvopom

vybvopom7#

我用这个查询我自己的表在PHP中,但它只给我一个结果,而我想每个用户名重复的数量,这是可能的吗?

SELECT count(*) AS duplicate_count
FROM (
 SELECT username FROM login_history
 GROUP BY username HAVING COUNT(time) > 1
) AS t;
bkkx9g8r

bkkx9g8r8#

以下是查询:

select user_name, count(*) 
from users 
group by user_name 
having count(user_name) > 1

这将返回:
| 用户名|计数|
| - -----|- -----|
| ABC| 5个|
| xyz| 3|

相关问题