电脑知识|欧美黑人一区二区三区|软件|欧美黑人一级爽快片淫片高清|系统|欧美黑人狂野猛交老妇|数据库|服务器|编程开发|网络运营|知识问答|技术教程文章 - 好吧啦网

您的位置:首頁技術(shù)文章
文章詳情頁

SQL 中的For Xml Path詳解

瀏覽:143日期:2023-03-06 14:25:13
目錄
  • FOR XML PATH是什么
  • 創(chuàng)建測(cè)試數(shù)據(jù)
  • 使用方法介紹
  • 具體實(shí)際應(yīng)用
  • 代碼優(yōu)化
  • STUFF()
  • STUFF()函數(shù)的作用
  • STUFF()函數(shù)的語法
  • 參數(shù)解釋
  • 返回類型
  • 注意事項(xiàng)

最近出的每日一題里面,有一道是關(guān)于合并同類型數(shù)據(jù)為一行的題,使用SQL Server 2017版本及以上的直接使用STRING_AGG()函數(shù)即可,但是2016版本以下是沒有這個(gè)功能的,那該如何求解?

今天就給大家介紹一下FOR XML PATH,它就是用來處理低版本數(shù)據(jù)庫中數(shù)據(jù)合并的,是一個(gè)比較古老的功能了,新版本中也依然還能使用。

FOR XML PATH是什么

FOR XML PATH 是將查詢結(jié)果集以XML形式展現(xiàn),將多行的結(jié)果,展示在同一行。

我們用實(shí)例來給大家介紹它的神奇之處。

創(chuàng)建測(cè)試數(shù)據(jù)

我們創(chuàng)建一個(gè)統(tǒng)計(jì)學(xué)生愛好的表

CREATE TABLE Stu_Hobby(Stu_Name NVARCHAR(20),--姓名Age INT,--年齡Hobby NVARCHAR(20)  --愛好)INSERT INTO Stu_HobbyVALUES  ( N"張三",19,N"踢足球"), ( N"張三",19,N"打籃球"), ( N"張三",19,N"游泳"), ( N"李四",21,N"看電影"), ( N"李四",21,N"閱讀"), ( N"王五",22,N"唱歌"), ( N"王五",22,N"玩游戲"), ( N"馬六",19,N"踢足球"), ( N"趙七",20,N"爬山"), ( N"趙七",20,N"跑步")

查詢學(xué)生愛好表Stu_Hobby里面的數(shù)據(jù):

使用方法介紹

測(cè)試數(shù)據(jù)建立好后,我們開始對(duì)這個(gè)表里面的數(shù)據(jù)進(jìn)行查詢,并使用上FOR XML PATH。

SELECT?*FROM?dbo.Stu_Hobby?FOR?XML?PATH;

結(jié)果如下:

它會(huì)生成一段XML代碼,我們點(diǎn)擊這行代碼會(huì)彈出一整個(gè)XML的頁面,由于篇幅較長(zhǎng),我們只截取一部分,具體如下:

此外我們還可以在FOR XML PATH的后面寫參數(shù),如果后面接參數(shù),會(huì)將節(jié)點(diǎn)換成參數(shù)名稱,例如:

SELECT *FROM dbo.Stu_Hobby FOR XML PATH(hobby)

結(jié)果如下圖:

已經(jīng)變成了我們添加的參數(shù)了。

跟我們實(shí)際需求相接近的是下面這個(gè)功能

我們可以單獨(dú)輸出某個(gè)字段的值,例如我們想看看學(xué)生愛好表中Hobby這一列具體有一些什么值,可以這樣寫:

SELECT Hobby+"、"FROM dbo.Stu_Hobby FOR XML PATH("")

注意:上面的+是字段拼接,就是將兩個(gè)字符串用+連成一個(gè)字符串。然后我們把XML中的給去掉。

結(jié)果如下:

可以看到我們寫的所有愛好都給列出來了,沒有去掉重復(fù)的,可以理解成把列里的值都顯示出來了。

具體實(shí)際應(yīng)用

我們現(xiàn)在想把上面的學(xué)生表里每個(gè)學(xué)生的愛好單獨(dú)顯示一行,愛好用"、"隔開。

SELECTA.Stu_Name,A.Age,(SELECT Hobby+"、"  FROM [dbo].Stu_HobbyWHERE--必須加的匹配條件Stu_Name=A.Stu_Name AND Age=A.Age  FOR XML PATH("")) AS HobbyFROM [dbo].Stu_Hobby AGROUP BY A.Stu_Name,A.Age

見證奇跡的時(shí)刻到了!!!

對(duì)比我們先前建的表,這里已經(jīng)將Hobby列的數(shù)據(jù)按每個(gè)學(xué)生變成了一行。

上面的WHERE條件是必須要的,如果去掉會(huì)怎么樣呢?我們把WHERE條件注釋掉看看會(huì)怎么樣?

SELECTA.Stu_Name,A.Age,(SELECT Hobby+"、"FROM [dbo].Stu_Hobby--WHERE--必須加的匹配條件--Stu_Name=A.Stu_Name AND Age=A.AgeFOR XML PATH("")) AS HobbyFROM [dbo].Stu_Hobby AGROUP BY A.Stu_Name,A.Age

結(jié)果如下:

就會(huì)將Hobby列所有值都顯示出來,很顯然這不是我們要的結(jié)果

代碼優(yōu)化

不知道小伙伴們有沒有發(fā)現(xiàn)Hobby列的結(jié)果尾部多了一個(gè)"、",看著好別扭,有沒有什么辦法將它去掉呢?答案是肯定的。

先用一個(gè)LEFT()和LEN()函數(shù)來處理一下Hobby列

SELECTT.Stu_Name,T.Age,LEFT(T.Hobby,LEN(T.Hobby)-1) AS HobbyFROM(SELECTA.Stu_Name,A.Age,(SELECT Hobby+"、"FROM [dbo].Stu_HobbyWHERE--必須加的匹配條件Stu_Name=A.Stu_Name AND Age=A.AgeFOR XML PATH("")) AS HobbyFROM [dbo].Stu_Hobby AGROUP BY A.Stu_Name,A.Age) T

結(jié)果如下:

這樣我們的需求就得到了完美解決,但是這個(gè)代碼有點(diǎn)長(zhǎng)額,能不能簡(jiǎn)短一點(diǎn)呀?答案也是肯定滴!在將代碼精簡(jiǎn)之前,我們需要先給大家介紹一個(gè)配合使用的函數(shù):

STUFF()

STUFF()函數(shù)的作用

STUFF()函數(shù)用于刪除指定長(zhǎng)度的字符,并可以在指定的起點(diǎn)處插入另一組字符。STUFF()函數(shù)中如果開始位置或長(zhǎng)度值是負(fù)數(shù),或者如果開始位置大于第一個(gè)字符串的長(zhǎng)度,將返回空字符串。如果要?jiǎng)h除的長(zhǎng)度大于第一個(gè)字符串的長(zhǎng)度,將刪除到第一個(gè)字符串中的第一個(gè)字符。

STUFF()函數(shù)的語法

STUFF ( character_expression , start , length ,character_expression )

參數(shù)解釋

character_expression:一個(gè)字符數(shù)據(jù)表達(dá)式。character_expression 可以是常量、變量,也可以是字符列或二進(jìn)制數(shù)據(jù)列。

start :一個(gè)整數(shù)值,指定刪除和插入的開始位置。如果 start 或 length 為負(fù),則返回空字符串。如果 start 比第一個(gè) character_expression 長(zhǎng),則返回空字符串。start 可以是 bigint 類型。

length:一個(gè)整數(shù),指定要?jiǎng)h除的字符數(shù)。如果 length 比第一個(gè) character_expression 長(zhǎng),則最多刪除到最后一個(gè) character_expression 中的最后一個(gè)字符。length 可以是 bigint 類型。

返回類型

如果 character_expression 是受支持的字符數(shù)據(jù)類型,則返回字符數(shù)據(jù)。如果 character_expression 是一個(gè)受支持的 binary 數(shù)據(jù)類型,則返回二進(jìn)制數(shù)據(jù)。

注意事項(xiàng)

1.如果開始位置或長(zhǎng)度值是負(fù)數(shù),或者如果開始位置大于第一個(gè)字符串的長(zhǎng)度,將返回空字符串。如果要?jiǎng)h除的長(zhǎng)度大于第一個(gè)字符串的長(zhǎng)度,將刪除到第一個(gè)字符串中的第一個(gè)字符。

2.如果結(jié)果值大于返回類型支持的最大值,則產(chǎn)生錯(cuò)誤。

--以上信息來源微軟官方文檔

這定義看的頭暈,我們還是來看看怎么使用吧

實(shí)例:

SELECT STUFF("abcdefg",1,0,"1234") --結(jié)果為"1234abcdefg"SELECT STUFF("abcdefg",1,1,"1234") --結(jié)果為"1234bcdefg"SELECT STUFF("abcdefg",2,1,"1234") --結(jié)果為"a1234cdefg"SELECT STUFF("abcdefg",2,2,"1234") --結(jié)果為"a1234defg"

說了這么多,我們看看STUFF怎么解決我們上面的問題吧,上代碼:

SELECTA.Stu_Name,A.Age,STUFF((SELECT "、"+HobbyFROM [dbo].Stu_HobbyWHERE--必須加的匹配條件Stu_Name=A.Stu_Name AND Age=A.Age    FOR XML PATH("")),1,1,"") AS HobbyFROM [dbo].Stu_Hobby AGROUP BY A.Stu_Name,A.Age

是不是比LEFT簡(jiǎn)短一些啦?我們看一下結(jié)果是不是我們想要的。

完美!

好了,F(xiàn)OR XML PATH就介紹到這里了,小伙伴可以對(duì)比以上兩種優(yōu)化的方法,自行比較哪種方式更加簡(jiǎn)單易懂。

到此這篇關(guān)于SQL 中的For Xml Path的文章就介紹到這了,更多相關(guān)SQL For Xml Path內(nèi)容請(qǐng)搜索以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持!

標(biāo)簽: MsSQL
相關(guān)文章:
主站蜘蛛池模板: RV减速机-蜗轮蜗杆减速机-洗车机减速机-减速机厂家-艾思捷 | 搜活动房网—活动房_集装箱活动房_集成房屋_活动房屋 | 掺铥光纤放大器-C/L波段光纤放大器-小信号光纤放大器-合肥脉锐光电技术有限公司 | 东莞市踏板石餐饮管理有限公司_正宗桂林米粉_正宗桂林米粉加盟_桂林米粉加盟费-东莞市棒子桂林米粉 | 高效节能电机_伺服主轴电机_铜转子电机_交流感应伺服电机_图片_型号_江苏智马科技有限公司 | 行星齿轮减速机,减速机厂家,山东减速机-淄博兴江机械制造 | 代理记账_免费注册公司_营业执照代办_资质代办-【乐财汇】 | 安徽净化板_合肥岩棉板厂家_玻镁板厂家_安徽科艺美洁净科技有限公司 | 3d可视化建模_三维展示_产品3d互动数字营销_三维动画制作_3D虚拟商城 【商迪3D】三维展示服务商 广东健伦体育发展有限公司-体育工程配套及销售运动器材的体育用品服务商 | 圆周直径尺-小孔内视镜-纤维研磨刷-东莞市高腾达精密工具 | 热风机_工业热风机生产厂家上海冠顶公司提供专业热风机图片价格实惠 | 南方珠江-南方一线电缆-南方珠江科技电缆-南方珠江科技有限公司 南汇8424西瓜_南汇玉菇甜瓜-南汇水蜜桃价格 | 无机纤维喷涂棉-喷涂棉施工工程-山东华泉建筑工程有限公司▲ | 破碎机锤头_耐磨锤头_合金锤头-鼎成机械一站式耐磨铸件定制服务 微型驱动系统解决方案-深圳市兆威机电股份有限公司 | 干法制粒机_智能干法制粒机_张家港市开创机械制造有限公司 | 即用型透析袋,透析袋夹子,药敏纸片,L型涂布棒-上海桥星贸易有限公司 | 四川实木门_成都实木门 - 蓬溪聚成门业有限公司 | 硫酸钡厂家_高光沉淀硫酸钡价格-河南钡丰化工有限公司 | 皮带机-带式输送机价格-固定式胶带机生产厂家-河南坤威机械 | 招商帮-一站式网络营销服务|搜索营销推广|信息流推广|短视视频营销推广|互联网整合营销|网络推广代运营|招商帮企业招商好帮手 | 细砂提取机,隔膜板框泥浆污泥压滤机,螺旋洗砂机设备,轮式洗砂机械,机制砂,圆锥颚式反击式破碎机,振动筛,滚筒筛,喂料机- 上海重睿环保设备有限公司 | 「钾冰晶石」氟铝酸钾_冰晶石_氟铝酸钠「价格用途」-亚铝氟化物厂家 | 英语词典_成语词典_日语词典_法语词典_在线词典网 | 螺杆式冷水机-低温冷水机厂家-冷冻机-风冷式-水冷式冷水机-上海祝松机械有限公司 | 工装定制/做厂家/公司_工装订做/制价格/费用-北京圣达信工装 | 纸塑分离机-纸塑分离清洗机设备-压力筛-碎浆机厂家金双联环保 | 西门子代理商_西门子变频器总代理-翰粤百科 | 阿里巴巴诚信通温州、台州、宁波、嘉兴授权渠道商-浙江联欣科技提供阿里会员办理 | 菏泽商标注册_菏泽版权登记_商标申请代理_菏泽商标注册去哪里 | 聚天冬氨酸,亚氨基二琥珀酸四钠,PASP,IDS - 远联化工 | 细胞染色-流式双标-试剂盒免费代做-上海研谨生物科技有限公司 | PCB厂|线路板厂|深圳线路板厂|软硬结合板厂|电路板生产厂家|线路板|深圳电路板厂家|铝基板厂家|深联电路-专业生产PCB研发制造 | 工业淬火油烟净化器,北京油烟净化器厂家,热处理油烟净化器-北京众鑫百科 | 酸度计_PH计_特斯拉计-西安云仪 纯水电导率测定仪-万用气体检测仪-低钠测定仪-米沃奇科技(北京)有限公司www.milwaukeeinst.cn | 佛山市钱丰金属不锈钢蜂窝板定制厂家|不锈钢装饰线条|不锈钢屏风| 电梯装饰板|不锈钢蜂窝板不锈钢工艺板材厂家佛山市钱丰金属制品有限公司 | 不锈钢水箱生产厂家_消防水箱生产厂家-河南联固供水设备有限公司 | 阁楼货架_阁楼平台_仓库仓储设备_重型货架_广州金铁牛货架厂 | 盘扣式脚手架-附着式升降脚手架-移动脚手架,专ye承包服务商 - 苏州安踏脚手架工程有限公司 | 物流之家新闻网-最新物流新闻|物流资讯|物流政策|物流网-匡匡奈斯物流科技 | 商标转让-商标注册-商标查询-软著专利服务平台 - 赣江万网 | 锂电混合机-新能源混合机-正极材料混料机-高镍,三元材料混料机-负极,包覆混合机-贝尔专业混合混料搅拌机械系统设备厂家 |