SQL server如何将top N查询出来的N个数据，作为一条记录插入一个有N个字段的表中

SQL server如何将top N查询出来的N个数据，作为一条记录插入一个有N个字段的表中
N会比较大，初步考虑30~50。
SQL SERVER 2008环境。
刚学，透视转换看了看，没整明白。

没什么其他特别要求。

9 个解决方案

#1

你是想这样？

--2005
create table ym
(col1 int, col2 int, col3 varchar(3))

insert into ym
select 1, 1, 'A' union all
select 1, 1, 'B' union all
select 1, 2, 'C' union all
select 1, 3, 'D' union all
select 1, 3, 'E'


select a.col1,a.col2,
stuff((select ','+col3 from ym b 
       where b.col1=a.col1 and b.col2=a.col2 
       for xml path('')),1,1,'') 'col3'
from ym a
group by  a.col1,a.col2

#2

列出测试数据及想要的结果

#3

引用 1 楼 DBA_Huangzj 的回复:

你是想这样？

--2005
create table ym
(col1 int, col2 int, col3 varchar(3))

insert into ym
select 1, 1, 'A' union all
select 1, 1, 'B' union all
select 1, 2, 'C' union all
select 1, 3, 'D' union all
select 1, 3, 'E'


select a.col1,a.col2,
stuff((select ','+col3 from ym b 
       where b.col1=a.col1 and b.col2=a.col2 
       for xml path('')),1,1,'') 'col3'
from ym a
group by  a.col1,a.col2

create table ym
(col1 int,col2 varchar(3))
 
insert into ym
select 3,  'A' union all
select 5,  'B' union all
select 2,  'C' union all
select 4,  'D' union all
select 1,  'E'

如果是通过col1取TOP 3的话希望的结果是：
B  D  A

#4

create table ym
(col1 int,col2 varchar(3))
 
insert into ym
select 3,  'A' union all
select 5,  'B' union all
select 2,  'C' union all
select 4,  'D' union all
select 1,  'E'

;WITH cte AS (
SELECT TOP 3 *
FROM ym
ORDER BY col1 DESC 

)
select DISTINCT 
stuff((SELECT  ','+col2 from cte b 
           
       for xml path('')),1,1,'') 'col2'
from cte a

/*
col2
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
B,D,A
*/

#5

这是不要逗号的

create table ym
(col1 int,col2 varchar(3))
 
insert into ym
select 3,  'A' union all
select 5,  'B' union all
select 2,  'C' union all
select 4,  'D' union all
select 1,  'E'

;WITH cte AS (
SELECT TOP 3 *
FROM ym
ORDER BY col1 DESC 

)
select DISTINCT 
stuff((SELECT  ' '+col2 from cte b 
           
       for xml path('')),1,1,'') 'col2'
from cte a

/*
col2
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
B D A
*/

#6

加了个变量@n用来控制top多少。准备下班。。

create table ym
(col1 int,col2 varchar(3))
 
insert into ym
select 3,  'A' union all
select 5,  'B' union all
select 2,  'C' union all
select 4,  'D' union all
select 1,  'E'
DECLARE @n INT
SET @n=4  --这里设变量控制返回多少个数
;WITH cte AS (
SELECT TOP (@n) *
FROM ym
ORDER BY col1 DESC 

)
select DISTINCT 
stuff((SELECT  ' '+col2 from cte b 
           
       for xml path('')),1,1,'') 'col2'
from cte a

/*
col2
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
B D A C
*/

#7

引用 6 楼 DBA_Huangzj 的回复:

加了个变量@n用来控制top多少。准备下班。。

create table ym
(col1 int,col2 varchar(3))
 
insert into ym
select 3,  'A' union all
select 5,  'B' union all
select 2,  'C' union all
select 4,  'D' union all
select 1,  'E'
DECLARE @n INT
SET @n=4  --这里设变量控制返回多少个数
;WITH cte AS (
SELECT TOP (@n) *
FROM ym
ORDER BY col1 DESC 

)
select DISTINCT 
stuff((SELECT  ' '+col2 from cte b 
           
       for xml path('')),1,1,'') 'col2'
from cte a

/*
col2
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
B D A C
*/

引用 6 楼 DBA_Huangzj 的回复:

加了个变量@n用来控制top多少。准备下班。。

create table ym
(col1 int,col2 varchar(3))
 
insert into ym
select 3,  'A' union all
select 5,  'B' union all
select 2,  'C' union all
select 4,  'D' union all
select 1,  'E'
DECLARE @n INT
SET @n=4  --这里设变量控制返回多少个数
;WITH cte AS (
SELECT TOP (@n) *
FROM ym
ORDER BY col1 DESC 

)
select DISTINCT 
stuff((SELECT  ' '+col2 from cte b 
           
       for xml path('')),1,1,'') 'col2'
from cte a

/*
col2
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
B D A C
*/

版主的方法好强大呀，B  D  A  C这个字符串的结果是对的。
但我想把B D A C作为一个4个字段的一条记录插入另一张表再进行其他处理，这个该如何处理呀， for xml path之类的实在是不懂呀
实际使用时N初步计划为50，谢谢。

#8

那直接行转列就可以了，没必要用for xml path

--drop table ym
create table ym
(col1 int,col2 varchar(3))
 
insert into ym
select 3,  'A' union all
select 5,  'B' union all
select 2,  'C' union all
select 4,  'D' union all
select 1,  'E' union all
select 5,  'B' union all
select 2,  'C' union all
select 4,  'D' union all
select 1,  'E'

IF OBJECT_ID('tempdb..#t','u')IS NOT NULL
DROP TABLE #t

DECLARE @n INT
SET @n=7  --这里设变量控制返回多少个数

SELECT TOP (@n) *INTO #T
FROM ym
ORDER BY col1 DESC 


declare @s nvarchar(max)
set @s=''
Select  @s=@s+','+quotename(N'列'+CAST(ROW_NUMBER()OVER(ORDER BY CURRENT_TIMESTAMP)AS VARCHAR(200)))+'=max(case when [col2]='+quotename(col2,'''')+' then [col2] else null end)'
from #T 
SET @s=SUBSTRING(@s,2,LEN(@s))
exec('select '+@s+' from #T')


/*
列1   列2   列3   列4   列5   列6   列7
---- ---- ---- ---- ---- ---- ----
B    B    D    D    A    C    C
*/

#9

引用 8 楼 DBA_Huangzj 的回复:

那直接行转列就可以了，没必要用for xml path

--drop table ym
create table ym
(col1 int,col2 varchar(3))
 
insert into ym
select 3,  'A' union all
select 5,  'B' union all
select 2,  'C' union all
select 4,  'D' union all
select 1,  'E' union all
select 5,  'B' union all
select 2,  'C' union all
select 4,  'D' union all
select 1,  'E'

IF OBJECT_ID('tempdb..#t','u')IS NOT NULL
DROP TABLE #t

DECLARE @n INT
SET @n=7  --这里设变量控制返回多少个数

SELECT TOP (@n) *INTO #T
FROM ym
ORDER BY col1 DESC 


declare @s nvarchar(max)
set @s=''
Select  @s=@s+','+quotename(N'列'+CAST(ROW_NUMBER()OVER(ORDER BY CURRENT_TIMESTAMP)AS VARCHAR(200)))+'=max(case when [col2]='+quotename(col2,'''')+' then [col2] else null end)'
from #T 
SET @s=SUBSTRING(@s,2,LEN(@s))
exec('select '+@s+' from #T')


/*
列1   列2   列3   列4   列5   列6   列7
---- ---- ---- ---- ---- ---- ----
B    B    D    D    A    C    C
*/

太强了，正是我要的，非常感谢。