SQL 聯合查詢與XML解析實例
這里舉例說明如何實現該功能：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
|
(select a.EBILLNO,a.EMPNAME,a.APPLYDATE,b.HS_NAME,replace(replace(a.SUMMARY,char(10), ''),char(13),'') as SUMMARY,cast(c.XmlData as XML).value('(/List/item/No/text())[1]','NVARCHAR(300)') as No,cast(c.XmlData as XML).value('(/List/item/zje/text())[1]','NVARCHAR(300)') as zje,cast(c.XmlData as XML).value('(/List/item/yfje/text())[1]','NVARCHAR(300)') as yfje,cast(c.XMLData as XML).value('(/List/item/bcje/text())[1]','NVARCHAR(300)') as bcje,cast(c.XMLData as XML).value('(/List/item/URL/text())[1]','NVARCHAR(300)') as URL,cast(c.XMLData as XML).value('(/List/item/Remark/text())[1]','NVARCHAR(300)') as BZ,cast(p.XMLData as XML).value('(/NewDataSet/Table1/UserName/text())[1]','NVARCHAR(500)') as SKRXM,('http://……?sid=3&mid=7281&PID='+a.PID) as bxdljdzfrom Ex_Bill as a left join Ex_System_Cfg as b on(a.BILLSYSTEMID=b.HS_ID and a.DATASYSTEMID=b.SYSTEM_NAME)left join (select * from [10.2.3.39].AspireworkFlow.dbo.RepeaingTable) as c on (c.Keyword='URL' and c.ProcessID=a.PID)left join (select * from [10.2.3.39].AspireworkFlow.dbo.RepeaingTable) as d on (d.Keyword='FKXX_New' and d.ProcessID=a.PID or d.Keyword='FKXX' and d.ProcessID=a.PID)left join (select * from EX_BillExtension) as p on a.BILLNO=p.BILL_NOwhere applyempid='zhongxun' and a.EBILLNO is not nulland status>5 and status not in(200,100,7000)and a.APPLYDATE>'2011-01-01'and a.HT='是'and cast(d.XMLData as XML).value('(/List/item/SKRXM/text())[1]','NVARCHAR(300)') is null) union(select e.EBILLNO,e.EMPNAME,e.APPLYDATE,f.HS_NAME,replace(replace(e.SUMMARY,char(10), ''),char(13),'') as SUMMARY,cast(g.XmlData as XML).value('(/List/item/No/text())[1]','NVARCHAR(300)') as No,cast(g.XmlData as XML).value('(/List/item/zje/text())[1]','NVARCHAR(300)') as zje,cast(g.XmlData as XML).value('(/List/item/yfje/text())[1]','NVARCHAR(300)') as yfje,cast(g.XMLData as XML).value('(/List/item/bcje/text())[1]','NVARCHAR(300)') as bcje,cast(g.XMLData as XML).value('(/List/item/URL/text())[1]','NVARCHAR(300)') as URL,cast(g.XMLData as XML).value('(/List/item/Remark/text())[1]','NVARCHAR(300)') as BZ,cast(h.XMLData as XML).value('(/List/item/SKRXM/text())[1]','NVARCHAR(300)') as SKRXM,('http://……?sid=3&mid=7281&PID='+e.PID) as bxdljdzfrom Ex_Bill as e left join Ex_System_Cfg as f on(e.BILLSYSTEMID=f.HS_ID and e.DATASYSTEMID=f.SYSTEM_NAME)left join (select * from [10.2.3.39].AspireworkFlow.dbo.RepeaingTable) as g on (g.Keyword='URL' and g.ProcessID=e.PID)left join (select * from [10.2.3.39].AspireworkFlow.dbo.RepeaingTable) as h on (h.Keyword='FKXX_New' and h.ProcessID=e.PID or h.Keyword='FKXX' and h.ProcessID=e.PID)where applyempid='zhongxun' and e.EBILLNO is not nulland status>5 and status not in(200,100,7000)and e.APPLYDATE>'2011-01-01'and e.HT='是'and cast(h.XMLData as XML).value('(/List/item/SKRXM/text())[1]','NVARCHAR(300)') is not null) |
在寫SQL的時候，難點不在于SQL本身，而在于邏輯上，當寫出這個SQL以后，發現邏輯也沒有那么難了。
就是采用Union把兩組都查詢出來的表放到一個里面
感謝閱讀，希望能幫助到大家，謝謝大家對本站的支持！
