mysql - SQL:如何过滤以仅返回结果集中的行，其中某个字段中的某些数据出现在未过滤结果集中的多行中？

如果我有这些数据：

code1 code2
  1    10       <-- Desired (1 appears more than once)
  1    11       <-- Desired (1 appears more than once)
  2    20
  3    30       <-- Desired (3 appears more than once)
  3    31       <-- Desired (3 appears more than once)
  4    40
  5    50

... 我想编写一个SQL查询，其结果如下：

code1 code2
  1    10       <-- This result appears because 1 appears more than once above
  1    11       <-- This result appears because 1 appears more than once above
  3    30       <-- This result appears because 3 appears more than once above
  3    31       <-- This result appears because 3 appears more than once above

（即，返回code1列中任何数据出现多次的所有行的单个SQL查询）。。。
我能写什么SQL？有可能吗？
到目前为止，我所拥有的这些还不起作用：

// WARNING!
// INVALID SQL

SELECT
  code1,
  code2
FROM
  mytable
GROUP BY code1,
  code2
HAVING COUNT(code1) > 1    <-- This line is invalid

// WARNING!
// INVALID SQL

而不是继续与之抗争。。。我想我可以问一下StackOverflow。谢谢！
（如果有帮助，可以使用任何MySQL特定的命令。）

最佳答案

你可以这样做：

SELECT code1, code2
FROM myTable
WHERE code1 IN
    (SELECT code1 FROM myTable GROUP BY code1 HAVING COUNT(code1) > 1)

或者像这样使用INNER JOIN：

SELECT t.code1, code2
FROM myTable t
  INNER JOIN
    (SELECT code1 FROM myTable GROUP BY code1 HAVING COUNT(code1) > 1)
     s on s.code1 = t.code1

SQLFiddle Demo