我想選擇所有也符合特定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_idSELECT *FROM oddsWHERE time_stamp IN (SELECT time_stamp FROM odds GROUP BY time_stamp HAVING COUNT(time_stamp) > 1) and fix_id='245679'
2 回答
Cats萌萌
TA貢獻1805條經驗 獲得超9個贊
這樣做：
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'
LEATH
TA貢獻1936條經驗 獲得超7個贊
我建議使用：not exists
SELECT o.*
FROM odds o
WHERE EXISTS (SELECT 1
FROM odds o2
WHERE o2.time_stamp = o.time_stamp AND
o2.fix_id = o.fix_id AND
o2.id <> o.id
)
WHERE a.fix_id = 245679;
在子查詢中使用聚合是一個壞習慣，當或做這個技巧時。聚合需要處理所有數據，而 / 可以停止在第一個匹配記錄。EXISTSNOT EXISTSEXISTSNOT EXISTS
- 2 回答
- 0 關注
- 101 瀏覽
添加回答
舉報
0/150
提交
取消