mysql - SQL检查不同字段中的重复值

Question

你好，我有一个数据库和一个名为 datalist 的表，它有一些虚拟数据。这是 phpmyadmin 中表格的屏幕截图。

我想做的是在每个字段中输出重复项。如您所见，有 2 个 1111 和 2 个 2222 数据，但它们都来自不同的领域。我怎样才能输出这样的东西:

Duplicates:
1111
2222

很抱歉我的问题，但我只知道检查同一字段上的重复数据。希望有人能帮帮我，谢谢!

Answer 1

SELECT
  f,
  COUNT(*)   overall_occurrences,
  COUNT(CASE WHEN field = 1 THEN f END)   AS f1_occurrences,
  COUNT(CASE WHEN field = 2 THEN f END)   AS f2_occurrences,
  COUNT(CASE WHEN field = 3 THEN f END)   AS f3_occurrences,
  COUNT(CASE WHEN field = 4 THEN f END)   AS f4_occurrences
FROM
(

    SELECT 1 AS field, f1 AS f, datalist.* FROM datalist
    UNION ALL
    SELECT 2 AS field, f2 AS f, datalist.* FROM datalist
    UNION ALL
    SELECT 3 AS field, f3 AS f, datalist.* FROM datalist
    UNION ALL
    SELECT 4 AS field, f4 AS f, datalist.* FROM datalist
)
   pivotted
WHERE
   somefield = 0
GROUP BY
   f
HAVING
   COUNT(*) > 1

编辑:已更新以额外显示重复出现的位置。

编辑；稍微减少逻辑重复的替代方案......

(这里可能不是必需的，但是在更复杂的场景中可能有用的方法示例。)

SELECT
  f,
  COUNT(*)   overall_occurrences,
  COUNT(CASE WHEN field = 1 THEN f END)   AS f1_occurrences,
  COUNT(CASE WHEN field = 2 THEN f END)   AS f2_occurrences,
  COUNT(CASE WHEN field = 3 THEN f END)   AS f3_occurrences,
  COUNT(CASE WHEN field = 4 THEN f END)   AS f4_occurrences
FROM
(

    SELECT
        pivotter.field,
        CASE pivotter.field
            WHEN 1 THEN datalist.f1
            WHEN 2 THEN datalist.f2
            WHEN 3 THEN datalist.f3
            WHEN 4 THEN datalist.f4
        END   AS f,
        datalist.*
    FROM
        datalist
    CROSS JOIN
    (
        SELECT 1 AS field
        UNION ALL
        SELECT 2 AS field
        UNION ALL
        SELECT 3 AS field
        UNION ALL
        SELECT 4 AS field
    )
        AS pivotter
)
   pivotted
WHERE
   somefield = 0
GROUP BY
   f
HAVING
   COUNT(*) > 1