sql查询过程解析(SQL 联合查询与XML解析实例详解)
类别:数据库 浏览量:1928
时间:2022-01-25 00:04:05 sql查询过程解析
SQL 联合查询与XML解析实例详解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把两组都查询出来的表放到一个里面
感谢阅读,希望能帮助到大家,谢谢大家对本站的支持!
您可能感兴趣
- sqlserver触发器修改当前字段(利用SQL Server触发器实现表的历史修改痕迹记录)
- sql服务器停止后无法启动(SQL服务器无法启动的解决方法)
- sqlserver怎么加check约束(浅析SQL Server的分页方式 ISNULL与COALESCE性能比较)
- sqlserver数据库备份还原(sqlserver数据库高版本备份还原为低版本的方法)
- mysql中如何进行模糊查询(MySQL模糊查询用法大全正则、通配符、内置函数)
- sql取整数函数(SQL中位数函数实例)
- centosdocker镜像安装mysql(linux下利用Docker安装mysql的步骤)
- SQL replace的使用方法
- zabbix如何配置监控(zabbix监控mysql的实例方法)
- mysql5.7.36详细安装(CenOS6.7下mysql 8.0.22 安装配置方法图文教程)
- mysql怎么把行变列(MySQL 中行转列的方法)
- sql 多列求和 当某列有null值时的处理
- sql server没有服务器怎么办(SQL Server 2012安装后服务器名称找不到的解决办法)
- mysql数据表怎么复制(MySQL 复制表的方法)
- sql server网络配置
- mysql字段多有什么问题(MySQL编码不一致可能引起的一些问题)
- 小米音乐与 QQ 音乐合作,便捷迁移会员(小米音乐与QQ音乐合作)
- 小米推出米兔儿童电话手表奥特曼版,799 元,支持微信 QQ(小米推出米兔儿童电话手表奥特曼版)
- 贾怀胤唱《白龙马》 炸场 了 没想到京剧还能这么玩(贾怀胤唱白龙马)
- 白龙马的改编学生版,快来看看(白龙马的改编学生版)
- 萌娃唱《白龙马》走红,那生动的小表情,网友直呼 简直是戏精(萌娃唱白龙马走红)
- 朱鹤松被不断认可,凤凰传奇玲花喊话岳云鹏,索要老朱演出门票(朱鹤松被不断认可)
热门推荐
- sqlserver2016标准分区(Sql Server 2016新功能之Row-Level Security值得关注)
- mysql怎么看脱机数据(MYSQL电话号码,身份证数据脱敏的实现)
- mysql创建表存在哪里(MySQL的表空间是什么)
- docker显示出错应用图标怎么处理(docker 命令报异常permission denied的解决方案)
- smart工具包括哪些元素(Smarty模板语法详解)
- 数据库差异备份
- sqlifnull如何使用(在 SQL 语句中处理 NULL 值的方法)
- python冒泡排序代码通俗理解(详解python算法之冒泡排序)
- laravel服务提供者应用场景(Laravel如何创建服务器提供者实例代码)
- php脚本控制方法(php swoole多进程/多线程用法示例基于php7nts版)
排行榜
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9