查询不同数据库的两张表合并在一起，以某个字段为主键合并同主键的行
- 2019 年 10 月 22 日
- 筆記
一.背景
今日接到一个需求，需要统计AB两个库中表的数据然后合并到一起。
需要查询的两个库的结构一致，所以同事只给了其中一个库的查询语句，剩下的查询整理需要自己写。
首先看一下原始的数据
原始取数语句
SELECT distinct ( ltrim(rtrim(a.charge_code))+','+ltrim(rtrim(a.serial))+','+ltrim(rtrim(a.group_no))) AS drugMainKey, a.drugname AS drugCommName, (case when isnull(y.drugname,'')='' then a.drugname else y.drugname end) AS drugName, a.specification AS drugspecs, b.unit AS drugUnit, e.dosaname AS drugFrom, c.manu_name AS factoryName , a.charge_code AS charge_code, a.serial AS packSeqNo, a.group_no as pharmacyCode, z.ybbm FROM yp_mz_base a LEFT JOIN yp_base f ON a.charge_code = f.charge_code LEFT JOIN dbo.yp_unit b ON a.mini_unit = b.code LEFT JOIN dbo.yp_manufacture c ON f.manufactory = c.manu_code LEFT JOIN dbo.yp_dosage e ON f.dosage = e.dosacode LEFT JOIN dbo.yp_drugname y ON a.drug_id_link = y.drug_id and y.flag ='b' LEFT JOIN dbo.zd_ybdzyp z on a.charge_code=z.charge_code and a.serial=z.serial
（A库和B库查出来的数据结构一致，就不重复贴图了）
二.要求
①.对于A库查到的数据的字段drugmainkey的值末尾加’1’结尾，对于B库查到的数据的字段drugMainKey的值末尾加‘2’结尾，并且和原数据之间用‘,’分隔，将处理后的数据放在一起。
②.对于drugMainKey相同的行进行合并，合并后的drugname为各行的drugname的合集，用‘，’连接在一起。（因为除了drugname以外的其他字段值都是相同的，所以不需要做操作）
处理后的数据的效果图为：
这个问题可以用游标来操作，也可以用笨方法采取两步走的笨方法处理数据。
这里我用的是笨方法。
2.1.1先建立表一，用来存放①中的数据。
表一：
2.1.2 实现①的要求
这个没什么好说的，涉及到两个表用union all，以及字符串的拼接。
SELECT distinct ( ltrim(rtrim(a.charge_code))+','+ltrim(rtrim(a.serial))+','+ltrim(rtrim(a.group_no))+','+'1') AS drugMainKey, a.drugname AS drugCommName, (case when isnull(y.drugname,'')='' then a.drugname else y.drugname end) AS drugName, a.specification AS drugspecs, ltrim(rtrim(b.unit)) AS drugUnit, e.dosaname AS drugFrom, c.manu_name AS factoryName , a.charge_code AS charge_code, a.serial AS packSeqNo, a.group_no as pharmacyCode, z.ybbm, a.pack_size AS packSize, /*新增1*/ ltrim(rtrim(p.unit))AS drugUnit2 /*新增2*/ /*p.unit AS drugUnit2 新增2*/ FROM sdyychisdb_sz.dbo.yp_mz_base a LEFT JOIN sdyychisdb_sz.dbo.yp_base f ON a.charge_code = f.charge_code LEFT JOIN sdyychisdb_sz.dbo.yp_unit b ON a.mini_unit = b.code LEFT JOIN sdyychisdb_sz.dbo.yp_unit p ON a.pack_unit = p.code /*新增3*/ LEFT JOIN sdyychisdb_sz.dbo.yp_manufacture c ON f.manufactory = c.manu_code LEFT JOIN sdyychisdb_sz.dbo.yp_dosage e ON f.dosage = e.dosacode LEFT JOIN sdyychisdb_sz.dbo.yp_drugname y ON a.drug_id_link = y.drug_id and y.flag ='b' LEFT JOIN sdyychisdb_sz.dbo.zd_ybdzyp z on a.charge_code=z.charge_code and a.serial=z.serial UNION all SELECT distinct ( ltrim(rtrim(pa.charge_code))+','+ltrim(rtrim(pa.serial))+','+ltrim(rtrim(pa.group_no))+','+'2') AS drugMainKey, pa.drugname AS drugCommName, (case when isnull(py.drugname,'')='' then pa.drugname else py.drugname end) AS drugName, pa.specification AS drugspecs, ltrim(rtrim(pb.unit)) AS drugUnit, pe.dosaname AS drugFrom, pc.manu_name AS factoryName , pa.charge_code AS charge_code, pa.serial AS packSeqNo, pa.group_no as pharmacyCode, pz.ybbm, pa.pack_size AS packSize, /*新增1*/ ltrim(rtrim(pp.unit))AS drugUnit2 /*新增2*/ /*pp.unit AS drugUnit2 新增2*/ FROM sdyychisdb_pj.dbo.yp_mz_base pa LEFT JOIN sdyychisdb_sz.dbo.yp_base pf ON pa.charge_code = pf.charge_code LEFT JOIN sdyychisdb_sz.dbo.yp_unit pb ON pa.mini_unit = pb.code LEFT JOIN sdyychisdb_sz.dbo.yp_unit pp ON pa.pack_unit = pp.code /*新增3*/ LEFT JOIN sdyychisdb_sz.dbo.yp_manufacture pc ON pf.manufactory = pc.manu_code LEFT JOIN sdyychisdb_sz.dbo.yp_dosage pe ON pf.dosage = pe.dosacode LEFT JOIN sdyychisdb_sz.dbo.yp_drugname py ON pa.drug_id_link = py.drug_id and py.flag ='b' LEFT JOIN sdyychisdb_sz.dbo.zd_ybdzyp pz on pa.charge_code=pz.charge_code and pa.serial=pz.serial order by drugMainKey
2.2.1再建立表二，用来存放②中的数据。
表二
2.2.2代码实现②
select B.drugMainKey,B.drugCommName, left(namelist,len(namelist)-1)as drugname,B.drugsecs,concat(B.packSize,B.drugUnit,'/',B.drugUnit2)as drugUnit,B.drugFrom,B.factoryName,B.charge_code,B.packSeqNo,B.phamacyCode,B.ybbm,B.packSize,B.drugUnit2 from ( select drugMainKey,drugCommName,drugsecs,drugUnit,drugFrom,factoryName,charge_code,packSeqNo,phamacyCode,ybbm,packSize,drugUnit2, (select ltrim(rtrim(drugName))+',' from xlyyp where drugMainKey=A.drugMainKey for xml path('')) as namelist from xlyyp A group by drugMainKey,drugCommName,drugsecs,drugUnit,drugFrom,factoryName,charge_code,packSeqNo,phamacyCode,ybbm,packSize,drugUnit2 )B ORDER BY drugMainKey DESC
对于for xml path的用法的理解，可以参考另外一位园友的帖子。
https://www.cnblogs.com/yasuo2/p/6433697.html
