SQL查询表中重复记录

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
    )