消除重复并汇总

Question

有这样的两张表：
1.CL161
CL161_MTR_NO                 CL161_PRICE          CL161_CURRENCY            CL161_ORG_QTY 
           1                     10                    RMB                            10
           1                     10                    RMB                            20
           1                     10                    RMB                            302. CL162
CL162_MTR_NO    cl162_chi_name   CL162_CHI_UNIT    
         1          用品                  个
我想得到结果为：
CL161_MTR_NO    CL161_PRICE   CL161_CURRENCY   CL161_ORG_QTY 
      1           10            RMB                    60
cl162_chi_name   CL162_CHI_UNIT    
    用品                  个

Answer 1

select B.CL162_MTR_NO,A.CL161_PRICE,A.CL161_CURRENCY,A.CL161_ORG_QTY,B.cl162_chi_name,B.CL162_CHI_UNIT 
(
select CL161_MTR_NO,CL161_PRICE,CL161_CURRENCY,sum(CL161_ORG_QTY) CL161_ORG_QTY 
from CL161
group by CL161_MTR_NO,CL161_PRICE,CL161_CURRENCY 
) A,CL162 B
where A.CL161_MTR_NO=B.CL162_MTR_NO

Answer 2

少了个from ,修正一下：SELECT b.cl162_mtr_no, a.cl161_price, a.cl161_currency, a.cl161_org_qty,
       b.cl162_chi_name, b.cl162_chi_unit
  FROM (SELECT   cl161_mtr_no, cl161_price, cl161_currency,
                 SUM (cl161_org_qty) cl161_org_qty
            FROM cl161
        GROUP BY cl161_mtr_no, cl161_price, cl161_currency) a,
       cl162 b
 WHERE a.cl161_mtr_no = b.cl162_mtr_no