精华内容
下载资源
问答
  • 查询表中重复记录

    千次阅读 2011-05-06 16:47:00
    联表查询、去除重复值、查找表中 多余的重复记录 、查找表中 没有重复记录的行 ... <br />  select * from table1 <br /> ...-- 查询表 1 列X有重复的值,则只取一条 s

    联表查询、去除重复值、查找 表中 多余 的重复记录 、查找表中 没有重复记录的行

     

    select * from table1


    select * from table2

     

    select a.*,b.* from table1 a, table2 b where a.id=b.e


    -- 查询表 1 列X有重复的值,则只取一条
    select min(id) as id,b,c from table1 group by b,c order by id asc

     

    -- 1、查找表中 多余的重复记录 ,重复记录是根据单个字段(peopleId)来判断
    select * from table1 where b in (select  b  from  table1  group  by  b  having  count(b) > 1)


    -- 1、查找表中 没有重复记录的行
    select * from table1 where b in (select  b  from  table1  group  by  b  having  count(b) = 1)

     

     

    select a.*,b.id,b.e from table1 a inner join (select min(id) as id,e from table2 group by e) b on a.id=b.e

     

    -- 查询2表关联的,而且不重复的记录
    select aa.*,bb.f from (select a.*,b.ids,b.e from table1 a inner join (select min(id) as ids,e from table2 group by e) b on a.id=b.e) aa inner join table2 bb on aa.ids=bb.id

    展开全文
  • SQL查询表中重复记录

    千次阅读 2017-12-27 16:52:39
    SQL查询表中重复记录的几种情况例如现在有个商品表t_product 表结构如图所示 根据单个字段查询重复记录,查询表中productName字段重复的记录 SELECT * FROM t_product WHERE recordNo IN ( SELECT ...

    SQL查询表中重复记录的几种情况

    例如现在有个商品表t_product
    表结构如图所示
    表结构

    1. 根据单个字段查询重复记录,查询表中productName字段重复的记录
    SELECT
        *
    FROM
        t_product
    WHERE
        productName IN (
            SELECT
                productName
            FROM
                t_product
            GROUP BY
                productName
            HAVING
                count(productName) > 1
        )
    1. 根据多个字段查询重复记录,查询表中productName,productStock相同的数据
    SELECT
        *
    FROM
        t_product p
    WHERE
        (p.productName, p.productStock) IN (
            SELECT
                productName,
                productStock
            FROM
                t_product
            GROUP BY
                productName,
                productStock
            HAVING
                count(*) > 1
        )
    展开全文
  • 查询表中重复记录

    2013-10-12 17:17:02
    查询表中重复记录 联表查询、去除重复值、查找 表中 多余 的重复记录 、查找表中 没有重复记录的行   select * from table1 select * from table2   select a.*,b.* from table1 a, table2 b...
     

    查询表中重复记录


    联表查询、去除重复值、查找 表中 多余 的重复记录 、查找表中 没有重复记录的行

     

    select * from table1


    select * from table2

     

    select a.*,b.* from table1 a, table2 b where a.id=b.e


    -- 查询表 1 列X有重复的值,则只取一条
    select min(id) as id,b,c from table1 group by b,c order by id asc;//列X指的是id?

    [与select min(id) as id,b,c from table1 group by b,c;结果集是一样的]

     

    -- 1、查找表中 多余的重复记录 ,重复记录是根据单个字段(peopleId)来判断
    select * from table1 where b in (select  b  from  table1  group  by  b  having  count(b) > 1)


    -- 1、查找表中 没有重复记录的行
    select * from table1 where b in (select  b  from  table1  group  by  b  having  count(b) = 1)

     

     

    select a.*,b.id,b.e from table1 a inner join (select min(id) as id,e from table2 group by e) b on a.id=b.e

     

    -- 查询2表关联的,而且不重复的记录 
    select aa.*,bb.f from (select a.*,b.ids,b.e from table1 a inner join (select min(id) as ids,e from table2 group by e) b on a.id=b.e) aa inner join table2 bb on aa.ids=bb.id

    ====================================================================================


    查询及删除重复记录的SQL语句
     
    1、查找表中多余的重复记录,重复记录是根据单个字段(Id)来判断
     
    select * from 表 where Id in (select Id from 表 group byId having count(Id) > 1)
     
    2、删除表中多余的重复记录,重复记录是根据单个字段(Id)来判断,只留有rowid最小的记录
     
    DELETE from 表 WHERE (id) IN ( SELECT id FROM 表 GROUP BY id HAVING COUNT(id) > 1) AND ROWID NOT IN (SELECT MIN(ROWID) FROM 表 GROUP BY id HAVING COUNT(*) > 1);
     
    3、查找表中多余的重复记录(多个字段)
     
    select * from 表 a where (a.Id,a.seq) in(select Id,seq from 表 group by Id,seq having count(*) > 1)
     
    4、删除表中多余的重复记录(多个字段),只留有rowid最小的记录
     
    delete from 表 a where (a.Id,a.seq) in (select Id,seq from 表 group by Id,seq having count(*) > 1) and rowid not in (select min(rowid) from 表 group by Id,seq having count(*)>1)
     
    5、查找表中多余的重复记录(多个字段),不包含rowid最小的记录
     
    select * from 表 a where (a.Id,a.seq) in (select Id,seq from 表 group by Id,seq having count(*) > 1) and rowid not in (select min(rowid) from 表 group by Id,seq having count(*)>1)

    展开全文
  • 表:TEMP_CUSTG_IMPORT_DETAIL ...根据某一条记录的两个字段查询表中重复记录 方法一:IN SELECT * FROM TEMP_CUSTG_IMPORT_DETAIL A WHERE (A.CONTACT_NAME, A.IDEN_NUM) IN (SELECT CONTACT_NAME, ID...

    表:TEMP_CUSTG_IMPORT_DETAIL

    查重字段:CONTACT_NAME、IDEN_NUM

    根据某一条记录的两个字段查询表中重复记录

    方法一:IN

    SELECT *
      FROM TEMP_CUSTG_IMPORT_DETAIL A
     WHERE (A.CONTACT_NAME, A.IDEN_NUM) IN
           (SELECT CONTACT_NAME, IDEN_NUM
              FROM TEMP_CUSTG_IMPORT_DETAIL
             WHERE ID = '201904190000000760')

     

    方法二:EXISTS

     

    SELECT *
      FROM TEMP_CUSTG_IMPORT_DETAIL T
     WHERE EXISTS (SELECT A.ID
              FROM TEMP_CUSTG_IMPORT_DETAIL A
             WHERE A.CONTACT_NAME = T.CONTACT_NAME
               AND A.IDEN_NUM = T.IDEN_NUM
               AND A.ID = '201904190000000760')

     

     

    展开全文
  • 查询表中重复的数据,重复的次数SELECT id,count(*) FROM orders GROUP BY id HAVING count(*)&gt;1;
  • Sql查询表中重复记录

    2011-09-16 15:22:17
    (一) 1、查找表中多余的重复记录重复记录是根据单个字段(peopleId)来判断 select * from people where peopleId in (select peopleId from people group by peopleId having c
  • 数据重复可以表现为某几个字段重复,也可以表示为全部字段都重复了(没有主键的情况下)查询某一字段的重复项:select user_name,count(*) as count from user group by user_name having count>1;查询多个字段字段...
  • 文章一: 假设有一样一张数据库表TNames,有两个字段 ID:...TNames表中有一些重复数据行 例如:  ID Name ---------------------------  1 wang  2 wang  3 lee  4 lee  5 ...
  • MySQL查询表重复记录   查询及删除重复记录的方法(一)1、查找表中多余的重复记录重复记录是根据单个字段(peopleId)来判断select * from peoplewhere peopleId in (select peopleId from people group by ...
  • 1、查找表中多余的重复记录重复记录是根据单个字段(peopleId)来判断 代码如下: select * from people where peopleId in (select peopleId from people group by peopleId having count (peopleId) &...
  • 查找全部重复记录Select * From Where 重复字段 In (Select 重复字段 From Group By 重复字段 Having Count(*)>1)2。过滤重复记录(只显示一条)Select * From Where ID In (Select Max(ID) From Group By...
  • 查找表中多余的重复记录重复记录是根据单个字段(peopleId)来判断 select * from people where peopleId in (select peopleId from people group by peopleId having count(peopleId) &gt; 1) 删除...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 3,257
精华内容 1,302
关键字:

查询表中重复记录