在SQL中查找重复值 [英] Finding duplicate values in a SQL

问题描述

在下面的查询中有一个名为lead_id的列我希望在我的结果中找到重复的lead_id数量

如果有10个唯一的潜在客户ID我必须得到10

但必须根据created_time进行分组，即..如果今天的日期有2个唯一的lead_id，则结果为2 ..





PS：我只想重复数据，但我需要按创建时间分组



i试试这样

In below query there is a column called lead_id i want to find count of duplicate lead_id
in my result if there are 10 unique lead id i must get 10
but it must be grouped based on created_time ie.. if there are 2 unique lead_id for today's date then the result would be 2 ..


PS: i want only count of the duplicate data but i need to group by created_time only

i tried like this

SELECT COUNT(*) as repetitions ,lead_id
 FROM mg_lead_suggested_listing
 GROUP BY lead_id
 HAVING repetitions > 1;

但它不符合我的要求，因为我需要按time_created分组

but it will not fit to my requirement as i need to group by time_created

select
    t.created_time,
    t.timecreated,
    sum(t.suggested_pending_cnt),
    sum(t.suggested_dropped_cnt)
from
    (select
        date_format(timecreated, '%d-%b-%Y') created_time,
            timecreated,
            case
                when source = 2 then 1
                else 0
            end suggested_pending_cnt,
            case
                when (source = 2 && directory_status = 4) then 1
                else 0
            end suggested_dropped_cnt
    from
        mg_lead_suggested_listing) t
group by t.created_time
order by t.timecreated desc
limit 10

推荐答案

检查这个

check this

SELECT COUNT(lead_id) as repetitions ,Convert(Date,created_time)
FROM mg_lead_suggested_listing
GROUP BY Convert(Date,created_time)
HAVING repetitions > 0;

此外你还可以试试

Additionally you can also try

select * from
(
select  
   Row_number() Over 
             (Partition by Convert(Date,created_time) Order by   
                          Convert(Date,created_time)) as repetitions 
   ,*
from mg_lead_suggested_listing 
) a where repetitions>1 

; cte_dup

as

（

选择ROW_NUMBER（）结束（按columnname1排序按列名1分区）为rno，*来自Tablename

）

从cte_dup中选择*，其中rno> = 2

;with cte_dup
as
(
select ROW_NUMBER() over( partition by columnname1 order by columnname1) as rno, * from Tablename
)
select * from cte_dup where rno>=2

这篇关于在SQL中查找重复值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！