SQL 联合查询与XML解析实例详解
作者:lqh 时间:2024-01-26 18:26:58
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把两组都查询出来的表放到一个里面
感谢阅读,希望能帮助到大家,谢谢大家对本站的支持!
标签:SQL,联合查询,XML解析
0
投稿
猜你喜欢
CentOS7安装mysql5.7解压缩版简明教程
2024-01-20 14:57:18
Python 中10进制数与16进制数相互转换问题
2021-11-29 15:37:26
Pygame Event事件模块的详细示例
2022-04-23 14:55:46
超级实用的8个Python列表技巧
2021-01-14 06:27:12
vue如何搭建多页面多系统应用
2024-05-02 17:03:47
在ASP.NET 2.0中操作数据之三十九:在DataList的编辑界面里添加验证控件
2023-07-06 02:02:48
python实现图像高斯金字塔的示例代码
2023-05-06 02:02:32
php文件下载后无法打开的处理方案及代码
2023-06-13 19:12:03
Mysql中FIND_IN_SET()和IN区别简析
2024-01-23 12:12:04
python实现数据库跨服务器迁移
2023-09-23 04:54:03
程序猿新手学习必备的Python工具整合
2024-01-02 00:53:26
python3.6.3转化为win-exe文件发布的方法
2021-02-16 05:57:40
人工智能——K-Means聚类算法及Python实现
2022-02-04 19:04:43
Python sklearn预测评估指标混淆矩阵计算示例详解
2023-12-19 23:39:21
Tensorflow全局设置可见GPU编号操作
2021-04-21 12:41:46
FCKEidtor 自动统计输入字符个数(IE)
2023-01-28 10:07:54
Redis数据库基础与ASP.NET Core缓存实现
2024-01-26 18:19:09
关于Python-faker的函数效果一览
2023-12-02 21:20:06
Python顺序结构语句详解
2023-05-26 13:19:44
vue中echarts的用法及与elementui-select的协同绑定操作
2024-05-10 14:20:13