- 论坛徽章:
- 0
|
如果数据中存在 不是按照你例子中的配对的,
mysql> select * From tt where (CodeA,CodeB) not in (
-> Select CA,CB
-> From (select if(CodeA<CodeB,CodeA,CodeB) as CA,if(CodeA<CodeB,CodeB,CodeA) as CB,vv From tt) A
-> Group by CA,CB ,vv
-> Having count(*)>=2 ) and (CodeB,CodeA) not in (
-> Select CA,CB
-> From (select if(CodeA<CodeB,CodeA,CodeB) as CA,if(CodeA<CodeB,CodeB,CodeA) as CB,vv From tt) A
-> Group by CA,CB ,vv
-> Having count(*)>=2 )
-> union
-> select CA,CB,vv From (select if(CodeA<CodeB,CodeA,CodeB) as CA,if(CodeA<CodeB,CodeB,CodeA) as CB,vv From tt) A
-> Group by CA,CB ,vv
-> Having count(*)=2
-> ;
+-------+-------+-------+
| CodeA | CodeB | vv |
+-------+-------+-------+
| k | j | 13.00 |
| j | k | 11.00 |
| b | c | 20.00 |
| f | h | 9.00 |
+-------+-------+-------+
4 rows in set (0.02 sec)
mysql> select * From tt ;
+-------+-------+-------+
| CodeA | CodeB | vv |
+-------+-------+-------+
| b | c | 20.00 |
| c | b | 20.00 |
| f | h | 9.00 |
| h | f | 9.00 |
| k | j | 13.00 |
| j | k | 11.00 |
+-------+-------+-------+
6 rows in set (0.00 sec)
mysql>
你试试 ?
最好把结果告诉我。 |
|