根据商户(业务)表条件选择所有计数(*)和合计(*)交易

Question

我有两个表

1. 事务[外键-profileid]和
2. 业务〔主键-profileid〕

我想要business.merchant_type为"AQ"的所有交易记录。

我需要做什么查询才能完全满足上述条件。

`

SELECT COUNT( CASE WHEN  bp.merchant_type='AQ' AND tm.profileid = bp.profileid THEN tm.txnid END ) AS  'Enterprise Total Txn count',
SUM( CASE WHEN bp.merchant_type='AQ' AND tm.profileid = bp.profileid THEN tm.PG_TXN_AMOUNT END ) AS 'Enterprise Total Txn Amount',
CASE WHEN tm.TXN_STATUS='1' THEN COUNT(
CASE WHEN bp.merchant_type='AQ' AND tm.profileid = bp.profileid THEN tm.TXNID END
) END AS 'Enterprise Sucess Txn count',
CASE WHEN tm.TXN_STATUS='1' THEN SUM(
CASE WHEN  bp.merchant_type='AQ' AND tm.profileid = bp.profileid THEN tm.TXNID END
) END AS 'Enterprise Sucess Txn Amount'
FROM
tbl_business_profiles bp,tbl_transaction_master tm;

`

输出在此处输入图像描述

Answer 1

试试这个：

select * from transaction as tr left join business as bs 
on tr.profileid = bs.profileid
where bs.merchant_type = 'AQ'