查詢不同資料庫的兩張表合併在一起，以某個欄位為主鍵合併同主鍵的行
- 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
