如下列表Sel_AA
序号 编号 标号1 标号2
1 AA B3 C3
2 BB B2 C2
3 AA B3 C3
4 AA B4 C4
5 BB B5 C5我的查询语句为SELECT [标号1]=stuff((select distinct '\n'+[标号1] from Sel_AA where 编号=t.编号 for xml path('')), 1, 1, ''),[标号1]=stuff((select distinct '\n'+[标号1] from Sel_AA where 编号=t.编号 for xml path('')), 1, 1, '') FROM Sel_AA t where 编号='AA'查询出来的结果是
标号1 B3\nB4
标号2 C3\nC4
也就是说,查询编号为AA的行,列里面相同项只出现一次,然后用分隔符\n连接起来,但是默认是正序的,我现在要逆序
也就是
标号1 B4\nB3
标号2 C4\nC3
请问如何实现
序号 编号 标号1 标号2
1 AA B3 C3
2 BB B2 C2
3 AA B3 C3
4 AA B4 C4
5 BB B5 C5我的查询语句为SELECT [标号1]=stuff((select distinct '\n'+[标号1] from Sel_AA where 编号=t.编号 for xml path('')), 1, 1, ''),[标号1]=stuff((select distinct '\n'+[标号1] from Sel_AA where 编号=t.编号 for xml path('')), 1, 1, '') FROM Sel_AA t where 编号='AA'查询出来的结果是
标号1 B3\nB4
标号2 C3\nC4
也就是说,查询编号为AA的行,列里面相同项只出现一次,然后用分隔符\n连接起来,但是默认是正序的,我现在要逆序
也就是
标号1 B4\nB3
标号2 C4\nC3
请问如何实现
go
create table [Sel_AA] (序号 int,编号 nvarchar(4),标号1 nvarchar(4),标号2 nvarchar(4))
insert into [Sel_AA]
select 1,'AA','B3','C3' union all
select 2,'BB','B2','C2' union all
select 3,'AA','B3','C3' union all
select 4,'AA','B4','C4' union all
select 5,'BB','B5','C5'select * from [Sel_AA]SELECT [标号1] = STUFF(( SELECT DISTINCT
'\n'+标号1
FROM Sel_AA
WHERE 编号 = t.编号
ORDER BY '\n'+标号1 DESC
FOR
XML PATH('')
), 1, 1, '')
,
[标号2] = STUFF(( SELECT DISTINCT
'\n' + [标号2]
FROM Sel_AA
WHERE 编号 = t.编号
ORDER BY '\n'+标号2 DESC
FOR
XML PATH('')
), 1, 1, '')
FROM Sel_AA t
WHERE 编号 = 'AA'
/*
标号1 标号2
nB4\nB3 nC4\nC3
nB4\nB3 nC4\nC3
nB4\nB3 nC4\nC3*/
SELECT [标号1]=stuff((select distinct '\n'+[标号1] from Sel_AA where 编号=t.编号 order by 编号 desc for xml path('')), 1, 1, ''),[标号1]=stuff((select distinct '\n'+[标号1] from Sel_AA where 编号=t.编号 for xml path('')), 1, 1, '') FROM Sel_AA t where 编号='AA'
我题目没有写清楚
SELECT [标号1] = STUFF(( SELECT '\n'+标号1
FROM (
SELECT
标号1,MAX(序号) AS 序号
FROM Sel_AA
WHERE 编号 = t.编号
GROUP BY 标号1)A --转一下即可
ORDER BY 序号 desc
FOR
XML PATH('')
), 1, 1, '')
,
[标号2] = STUFF(( SELECT '\n'+标号2
FROM (
SELECT
标号2,MAX(序号) AS 序号
FROM Sel_AA
WHERE 编号 = t.编号
GROUP BY 标号2)A
ORDER BY 序号 desc
FOR
XML PATH('')
), 1, 1, '')
FROM Sel_AA t
WHERE 编号 = 'AA'
/*
标号1 标号2
nB4\nB3 nC4\nC3
nB4\nB3 nC4\nC3
nB4\nB3 nC4\nC3*/