mysql - 过滤 IN 语句

Question

我需要优化这个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

但我只需要存在“现金” 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

Answer 1

SELECT ch_num, payment_type, Total
FROM transactions 
WHERE ch_num IN 
(
       SELECT ch_num 
       FROM transactions 
       GROUP BY ch_no
       HAVING count('ch_num') > 1 
       and sum(payment_type='cash') >= 1
)
ORDER BY `ch_num`