我需要优化此sql选择：
SELECT ch_num, payment_type, Total FROM transactions WHERE ( ch_num )
IN (
SELECT ch_num FROM transactions GROUP BY ch_num HAVING count('ch_num') > 1
)
ORDER BY `ch_num`
我得到的结果是：
ch_num payment_type Total
20001 visa 36.60
20001 visa 36.60
20001 mc 30.60
50019 cash 9.00
50019 mc 18.95
50023 cash 2.70
50023 visa 7.00
但是我只需要在有'cash'payment_type的地方才有结果行，因此应该省略'ch_no'20001。
正确的结果将是：
ch_num payment_type Total
50019 cash 9.00
50019 mc 18.95
50023 cash 2.70
50023 visa 7.00
最佳答案
这是一个完整的，经过验证的代码示例，最后是测试结果。我使用了Oracle，但是SQL SELECT的语法应该相同。
create table transactions (ch_num int, payment_type varchar2(100), total float);
insert into transactions values(20001,'visa',36.60);
insert into transactions values(20001,'mc',30.60);
insert into transactions values(50019,'cash',9.00);
insert into transactions values(50019,'mc',18.95);
insert into transactions values(50023,'cash',2.70);
insert into transactions values(50023,'visa',7.00);
SELECT ch_num, payment_type, Total FROM transactions a WHERE ( ch_num )
IN (
SELECT ch_num FROM transactions GROUP BY ch_num HAVING count(ch_num) > 1
)
AND EXISTS
(SELECT ch_num FROM transactions b where payment_type = 'cash' and a.ch_num = b.ch_num)
ORDER BY ch_num
结果：
CH_NUM PAYMENT_TYPE TOTAL
1 50019 cash 9
2 50019 mc 18.95
3 50023 cash 2.7
4 50023 visa 7
关于mysql - mysql-filter IN语句，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/12925385/