求SQL语句写法

Question

SQL语句写法如下select rtrim(c.card_name) + '(' + s.mer_on_card + ')' as card_name,rtrim(m.mcht_name) + '(' + m.term_id +')' as mcht_name,
--s.acq_mcht_id,
m.term_id,s.agent_mcht_id,sum(s.pnt_trans_num_successful) as pnt_trans_num_successful,sum(s.pnt_trans_amt_successful) as pnt_trans_amt_successful,sum(s.pnt_trans_acq_fee_successful) as pnt_trans_acq_fee_successful,
sum(s.pnt_trans_iss_fee_successful) as pnt_trans_iss_fee_successful,sum(s.ncr_trans_num_successful) As ncr_trans_num_successful,sum(s.ncr_trans_amt_successful) as ncr_trans_amt_successful,sum(s.ncr_trans_acq_fee_successful) as ncr_trans_acq_fee_successful,sum(s.ncr_trans_iss_fee_successful) as ncr_trans_iss_fee_successful,sum(s.per_trans_num_successful) as per_trans_num_successful,
sum(s.per_trans_amt_successful) as per_trans_amt_successful,sum(s.per_trans_acq_fee_successful) as per_trans_acq_fee_successful,sum(s.per_trans_iss_fee_successful) as per_trans_iss_fee_successful,sum(s.psr_trans_num_successful) as psr_trans_num_successful,sum(s.psr_trans_amt_successful) as psr_trans_amt_successful,sum(s.psr_trans_acq_fee_successful) as psr_trans_acq_fee_successful,
sum(s.psr_trans_iss_fee_successful) as psr_trans_iss_fee_successful,sum(s.rsa_trans_num_successful) As rsa_trans_num_successful,sum(s.rsa_trans_amt_successful) as rsa_trans_amt_successful,sum(s.rsa_trans_acq_fee_successful) as rsa_trans_acq_fee_successful,sum(s.rsa_trans_iss_fee_successful) as rsa_trans_iss_fee_successful,sum(s.tsi_trans_num_successful) as tsi_trans_num_successful,
sum(s.tsi_trans_amt_successful) as tsi_trans_amt_successful,sum(s.tsi_trans_acq_fee_successful) as tsi_trans_acq_fee_successful,sum(s.tsi_trans_iss_fee_successful) as tsi_trans_iss_fee_successful,sum(s.tso_trans_num_successful) as tso_trans_num_successful,sum(s.tso_trans_amt_successful) as tso_trans_amt_successful,sum(s.tso_trans_acq_fee_successful) as tso_trans_acq_fee_successful,
sum(s.tso_trans_iss_fee_successful) as tso_trans_iss_fee_successful,sum(s.psn_trans_num_successful) As psn_trans_num_successful,sum(s.psn_trans_amt_successful) as psn_trans_amt_successful,sum(s.psn_trans_acq_fee_successful) as psn_trans_acq_fee_successful,sum(s.psn_trans_iss_fee_successful) as psn_trans_iss_fee_successful 
 from l2_sum_transactiontwo s,l2_param_card_name c, l2_mcht m
 Where s.mer_on_card = c.mer_on_card  
 and substring(sett_date,1,6) =  '201302' 
 and s.mer_on_card=m.mer_on_card 
 and SUBSTRING(m.mcht_id,5,6)<>'573289' 
 and substring(m.mcht_role_type,2,1)='1' 
 and s.iss_mcht_id=m.mcht_id 
 --and s.acq_term_id=m.term_id 
 group by c.card_name, mcht_name, m.term_id, s.agent_mcht_id, s.mer_on_card--, s.acq_mcht_id查询结果如下图所示红色框内只保留一条记录，SQL语句怎样写，acq_mcht_id的值取哪一个都可以

Answer 1

显示了这么多行是因为group by 部分有 s.mer_on_card，这几条的 s.mer_on_card 值应该是不同的。：）请确认是否要按 s.mer_on_card 分组？

Answer 2

with t01 as(
select *,cardname from ROW_NUMBER()OVER(PARTITION BY cardName order by acq_mcht_id) as rowsid from table)
select *  from t01 where rowsid=1

Answer 3

上面写错了，走神了
with t01 as(
select *,,ROW_NUMBER()OVER(PARTITION BY cardName order by acq_mcht_id) as rowsid from table)
select *  from t01 where rowsid=1