我有两个表loan（custno，acno&clearbal）和customer（custno，pan&name）。custno在这两个表中都是通用的。我想生成pan-wise数据。对于这个数据，我构建了下面提到的查询来获取pan-wise loan unfinding，我想在输出中有客户名称，但是在不同的客户id中提到了几个pan，所以
如果我在同一个查询中添加名称，它将拆分记录。可以将具有较低id的客户名称（如cusid 1和2中的“aaa”pan）合并，因此应显示cistomer nume in 1，并在“aaa”样本详细信息上执行group by
客户表

Custno Pan Name
1 aaa Kevin
2 bbb peter
3 aaa Josh

贷款表

Custno acno clearbal
1 20 100
2 21 50
3 22 200

我通过查询得到的输出

Pan Count Bal
aaa 2 300
bbb 1 50

我想要的是

Pan Name Count Bal
aaa Kevin 2 300
bbb peter 1 50

select c.pan,count(L.Acno) as Count,sum(L.ClearBal) as bal 
from LOAN as L 
inner join customer as C
on L.CustNo=c.CustNo where l.clearbal<0 
group by c.PAN 
order by bal desc

我尝试了下面提到的查询，但输出跳过了具有相同pan卡的记录。

select c.name,c.pan,count(L.Acno) as Count,sum(L.ClearBal) as bal from LON20190629 as L inner join customer as C
on L.CustNo=c.CustNo inner joiN
  (
    SELECT pan, MIN(CustNo) MinPoint
    FROM customer
    where len(pan)>6
    GROUP BY PAN
  ) tbl1
  ON tbl1.PAN = C.PAN
WHERE tbl1.MinPoint = C.CustNo and and l.ClearBal<0 group by c.PAN,c.Name order by bal desc