计算机教程

当前位置:澳门娱乐场网址 > 计算机教程 > SQL 联合查询与XML解析实例详解

SQL 联合查询与XML解析实例详解

来源:http://www.ablakeforum.com 作者:澳门娱乐场网址 时间:2020-03-18 01:44

SQL 联合查询与XML解析实例

本文是参考本文末尾引用的文章,并加以整理修改而成

这里举例说明如何实现该功能:

  1. 查询全部
(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,('……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 status5 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,('……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 status5 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以后,发现逻辑也没有那么难了。

db.users.find()

就是采用Union把两组都查询出来的表放到一个里面

与SQL对比:

感谢阅读,希望能帮助到大家,谢谢大家对本站的支持!

 select * from users  
  1. 根据条件 = 查询
db.users.find({"age" : 27}) 

与SQL对比:

select * from users where age = 27 
  1. 澳门娱乐场网址,多条件查询 and
db.users.find({"username":"joe","age":27})

与SQL对比:

 select * from users where useranem = 'joe' and age = 27
  1. 返回部分查询结果
db.users.find({},{"username":1,"email":1})
db.users.find({},{"username":1,"email":1,"_id":0})

1表示返回该字段,0表示不返回,_id如果不表示的话,默认返回

与SQL对比:

 select username,email from users
  1. 查询条件大于小于 >,<
db.users.find({"age" : {"$gte" : 18, "$lte" : 30}})

与SQL对比:

 select * from users where age >=18 and age <= 30 
  1. 查询条件不等于 !=
b.users.find({"username" : {"$ne" : "joe"}}) 

与SQL对比:

select * from users where username <> "joe"
  1. 查询条件在范围内 in
db.users.find({"ticket_no" : {"$in" : [725, 542, 390]}}) 

与SQL对比:

select * from users where ticket_no in (725, 542, 390)  
  1. 查询条件不在范围内 not in
db.users.find({"ticket_no" : {"$nin" : [725, 542, 390]}})

与SQL对比:

 select * from users where ticket_no not in (725, 542, 390)  
  1. 查询条件or
db.users.find({"$or" : [{"ticket_no" : 725}, {"winner" : true}]})   

与SQL对比:

select * form users where ticket_no = 725 or winner = true
  1. 查询条件求余
db.users.find({"id_num" : {"$mod" : [5, 1]}}) 

与SQL对比:

select * from users where (id_num mod 5) = 1  

本文由澳门娱乐场网址发布于计算机教程,转载请注明出处:SQL 联合查询与XML解析实例详解

关键词: