SQL 联合查询与XML解析实例
这里举例说明如何实现该功能:
(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 bxdljdz from 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_NO where applyempid='zhongxun' and a.EBILLNO is not null and 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 bxdljdz from 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 null and 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把两组都查询出来的表放到一个里面
感谢阅读,希望能帮助到大家,谢谢大家对本站的支持!
免责声明:本站文章均来自网站采集或用户投稿,网站不提供任何软件下载或自行开发的软件!
如有用户或公司发现本站内容信息存在侵权行为,请邮件告知! 858582#qq.com
暂无“SQL 联合查询与XML解析实例详解”评论...
P70系列延期,华为新旗舰将在下月发布
3月20日消息,近期博主@数码闲聊站 透露,原定三月份发布的华为新旗舰P70系列延期发布,预计4月份上市。
而博主@定焦数码 爆料,华为的P70系列在定位上已经超过了Mate60,成为了重要的旗舰系列之一。它肩负着重返影像领域顶尖的使命。那么这次P70会带来哪些令人惊艳的创新呢?
根据目前爆料的消息来看,华为P70系列将推出三个版本,其中P70和P70 Pro采用了三角形的摄像头模组设计,而P70 Art则采用了与上一代P60 Art相似的不规则形状设计。这样的外观是否好看见仁见智,但辨识度绝对拉满。
更新动态
20240523
20240523
- 群星1990-没卵头家电影原声带与海拔河的人[日本东芝版][WAV+CUE]
- 云浩影《INTRODUCTION to PAIN》[320K/MP3][47.46MB]
- 云浩影《INTRODUCTION to PAIN》[FLAC/分轨][119.91MB]
- 洪嘉豪《Inner/Child》[320K/MP3][69.96MB]
- 陈雷.1991-恋恋恋.怀念的人【金圆唱片】【WAV+CUE】
- 群星.1993-风云际会·10年金马奖最佳电影主題曲【滚石】【WAV+CUE】
- 群星.1993-光荣岁月·10年金马奖最佳电影主题曲【滚石】【WAV+CUE】
- 万沙浪-《山地歌王》2CD丽风金典[低速原抓WAV+CUE]
- 群星1990-名冠连串金曲大集合1[日本东芝1A1][WAV+CUE]
- 群星1990-欢乐CHACHA36首[马来西亚版][WAV+CUE]
- dnf增益强化栏在哪
- dnf夏日宠物升级要买几套
- dnf夏日纹章和五一纹章对比
- 许茹芸.2011-许茹芸的微醺音乐你听见了(我)吗?【亚神音乐】【WAV+CUE】
- GAI周延.2024-底层逻辑【种梦音乐】【FLAC分轨】