一、判断某个逗号隔开的字段中有某个值
FIND_IN_SET('a','a,b,c,d') 用 CHARINDEX(','+'b'+',' , ','+'a,b,c'+',') > 0 替代
二、模仿mysql的group_concat的示例
1
2
3
4
5
6
7
|
SELECT
id,
val=(
SELECT
[value] +
','
FROM
tb
AS
b
WHERE
b.id = a.id
FOR
XML PATH(
''
) )
FROM
tb
AS
a
GROUP
BY
id
|
1
2
3
4
5
6
7
|
SELECT
id,
[val]=STUFF( (
SELECT
','
+[value]
FROM
tb
AS
b
WHERE
b.id = a.id
FOR
XML PATH(
''
)) , 1 , 1 ,
''
)
FROM
tb
AS
a
GROUP
BY
id
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
|
--删除、创建临时表
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(
''
)) , 1 , 1 ,
''
)
from
#table1 ta
group
by
myId
) dd
order
by
LEN(val)
desc
|
本文转自 独孤环宇 51CTO博客,原文链接:http://blog.51cto.com/snowtiger/1929498