Question[SQL]：Can you use a SQL statement to finding duplicate values!-优快云博客

本文介绍了一种使用SQL语句来查找具有相同姓氏的作者的方法。通过创建临时表并插入示例数据，演示了如何分组记录并计算每个姓氏出现的次数，从而找出姓氏重复的作者。

Question：Can you use a SQL statement to finding duplicate values!
How can I find authors with the same last name?
You can use the table authors in datatabase pubs. I want to get the result as below:
Output:
au_lname number_dups
---------------------------------------- -----------
Ringer 2

(1 row(s) affected)

Answer：

    if OBJECT_ID('dbo.Table101302') is not null
        drop table dbo.Table101302
    create table dbo.Table101302
    (
        [Id] int,
        [au_lname] varchar(10)
    )
    insert into dbo.Table101302 values(1, 'name1')
    insert into dbo.Table101302 values(2, 'name2')
    insert into dbo.Table101302 values(3, 'name3')
    insert into dbo.Table101302 values(4, 'name3')
    insert into dbo.Table101302 values(5, 'name4')
    insert into dbo.Table101302 values(6, 'name5');
    insert into dbo.Table101302 values(7, 'name5');

    select [au_lname], [number_dups]=COUNT(1)
        from dbo.Table101302
        group by [au_lname]
        --having(COUNT(1)>1)

    drop table dbo.Table101302

转载于:https://www.cnblogs.com/chenjunbiao/archive/2008/10/13/1760189.html