SQL:如何过滤以仅返回结果集中的行，其中字段中的某些数据出现在未过滤结果集的多行中? [英] SQL: How to filter to return only the rows from a result set where certain data in a field appears in more than one row of the unfiltered result set?

问题描述

如果我有这些数据:

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 查询，其结果如下:

... And I want to write a single SQL query whose results are this:

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

(即，单个 SQL 查询返回 code1 列中的任何数据出现多次的所有行)...

(i.e, a single SQL query that returns all rows for which any data in the code1 column appears more than once)...

我可以写什么 SQL?可能吗?

What SQL can I write? Is it possible?

这是我到目前为止所拥有的，但不起作用:

Here is what I have so far, which does not work:

// WARNING!
// INVALID SQL

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

// WARNING!
// INVALID SQL

与其继续与之抗争……我想我会在 StackOverflow 上提问.谢谢！

Rather than continuing to fight with it... I thought I would ask on StackOverflow. Thanks!

(允许任何特定于 MySQL 的命令，如果它们可能有帮助.)

(Any MySQL-specific commands are ALLOWED, if they might help.)

推荐答案

你可以试试这个:

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

这篇关于SQL:如何过滤以仅返回结果集中的行，其中字段中的某些数据出现在未过滤结果集的多行中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！