如何选择具有额外条件 sql 的重复行

Question

我想选择所有也符合特定fix_id的重复记录。

*------*-------------*---------*

|  id    time_stamp  | fix_id  |

*------*-------------*---------*

|  1   |   790       |  5679   |

|  2   |   1000      |  245679 |

|  3   |   1000      |  245679 |

|  4   |   12        |  245679 |

|  5   |   790       |  5679   |

*------*-------------*---------*

我想用下面的代码找回id 2和3，但它返回2，3和4所有具有相同fix_id

SELECT *

FROM odds

WHERE time_stamp IN (SELECT time_stamp

                  FROM odds 

                  GROUP BY time_stamp

                  HAVING COUNT(time_stamp) > 1) and fix_id='245679'

Answer 1

这样做：    SELECT *    FROM odds    a inner join (SELECT time_stamp, fix_id                      FROM odds                       GROUP BY time_stamp, fix_id                      HAVING COUNT(*) > 1) b on a.fix_id = b.fix_id and a.time_stamp = b.time_stamp WHERE a.fix_id='245679'

Answer 2

我建议使用：not existsSELECT o.*FROM odds oWHERE EXISTS (SELECT 1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FROM odds o2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; WHERE o2.time_stamp = o.time_stamp AND&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; o2.fix_id = o.fix_id AND&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; o2.id <> o.id&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;)WHERE a.fix_id = 245679;在子查询中使用聚合是一个坏习惯，当或做这个技巧时。聚合需要处理所有数据，而 / 可以停止在第一个匹配记录。EXISTSNOT EXISTSEXISTSNOT EXISTS