sqlserver模仿mysql函数FIND_IN_SET,group_concat的功能

一、判断某个逗号隔开的字段中有某个值

FIND_IN_SET('a','a,b,c,d') 用 CHARINDEX(','+'b'+',','+'a,c'+',') > 0 替代

二、模仿mysql的group_concat的示例

SELECT?id,?????
???????val=(?SELECT?[value]?+','?????
???????????????FROM?tb?AS?b?????
???????????????WHERE?b.id?=?a.id
???????????????FOR?XML?PATH('')??)?????
FROM?tb?AS?a??????
GROUP?BY?id

SELECT?id,?????
???????[val]=STUFF(?(SELECT?','+[value]?????
?????????????????????FROM?tb?AS?b?????
?????????????????????WHERE?b.id?=?a.id?????
?????????????????????FOR?XML?PATH(''))?,?1?,?''?)????
FROM?tb?AS?a??????
GROUP?BY?id

--删除、创建临时表
drop?table?#table1;
select?*?into?#table1
from?(
SELECT?myId,'auto'?as?type
)allData
--把数据按myId分类链接，然后按链接后数据的长度倒序排序
select?*?from?(
??select?myId,??val=STUFF(?(SELECT?'、'+type?????
?????????????????????FROM?#table1?AS?b?????
?????????????????????WHERE?b.myId?=?ta.myId?????
?????????????????????FOR?XML?PATH(''))?,?''?)
??from?#table1?ta
??group?by?myId
)?dd?order?by?LEN(val)?desc

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!