Question

sql语句问题：多表关联查询问题？在找不到条件时如何保留a表的group by的全量数据？

--sql语句如下：
select a.bank_num, a.trans_date, a.mec_code, b.mec_desc, a.txn_sub_num,c.trans_desc, a.sum_num, a.success_num from
(
select bank_num, substr(trans_date,1,6) as trans_date, mec_code, txn_sub_num, sum(sum_num) as sum_num, sum(success_num) as success_num
from tbl_report_mecsms_sum a
where trans_date like '201402%' and bank_num = '1429'
group by bank_num,substr(trans_date,1,6),mec_code,txn_sub_num
order by bank_num,substr(trans_date,1,6),mec_code,txn_sub_num
) a,
tbl_mec_desc b,
tbl_txn_inf c
where a.mec_code = b.mec_code
and a.txn_sub_num =c.trans_num;

我的本意是在a表的查询group by 条件下添加两个字段描述，需要根据mec_code 查询b表获取mec_desc. 根据 txn_sub_num去c表查询 trans_dsc字段。
现在的脚本对于b表和c表查找不到记录的情况会造成数据丢失，如何设置当b表或者c表找不到匹配的记录的情况下将mec_desc字段显示为' '从而保留a表查询的group by 数据不丢失呢？
还有，如果根据mec_code查询b表关联到了多个mec_desc，如何只取第一条匹配的记录呢？

Answer 1

select a.bank_num
, a.trans_date
, a.mec_code
, isNull(b.mec_desc,'')
, a.txn_sub_num
,isNull(c.trans_desc,'')
, a.sum_num
, a.success_num from
(
select bank_num, substr(trans_date,1,6) as trans_date, mec_code, txn_sub_num, sum(sum_num) as sum_num, sum(success_num) as success_num
from tbl_report_mecsms_sum a
where trans_date like '201402%' and bank_num = '1429'
group by bank_num,substr(trans_date,1,6),mec_code,txn_sub_num
order by bank_num,substr(trans_date,1,6),mec_code,txn_sub_num
) a

left join tbl_mec_desc b
on a.mec_code = b.mec_code
left join tbl_txn_inf c
on a.txn_sub_num =c.trans_num

Answer 2

前一链槐闭个问题棚裂，可以用左连接left join，使b、c无匹配时显明庆示空值；
后一个问题，可以先对b做一次子查询，确保只返回一个mec_desc。