sql - 在 SQL Server 中按组拆分输出行

Question

我必须平均划分行，例如，这里有 15 行。我想平分，分为三组，但我希望名称只出现在每组的第一个条目前面，如图:

DECLARE @NAMES TABLE
(
[ID] INT IDENTITY,
[NAME] VARCHAR(20)
)


INSERT INTO @NAMES
SELECT 'NAME1' UNION ALL
SELECT 'NAME2' UNION ALL
SELECT 'NAME3' UNION ALL
SELECT 'NAME4' UNION ALL
SELECT 'NAME5' UNION ALL
SELECT 'NAME6' UNION ALL
SELECT 'NAME7' UNION ALL
SELECT 'NAME8' UNION ALL
SELECT 'NAME9' UNION ALL
SELECT 'NAME10' UNION ALL
SELECT 'NAME11' UNION ALL
SELECT 'NAME12' UNION ALL
SELECT 'NAME13' UNION ALL
SELECT 'NAME14' UNION ALL
SELECT 'NAME15' 

期望的输出:

ID          NAME
----------- --------------------
1           NAME1
2           
3           
4           
5           
6           NAME6
7           
8           
9           
10          
11          NAME11
12          
13          
14    
15

Answer 1

如果您使用的是 SQL 2005 或更高版本，则对于任意数量的行，以下内容应该可以完成:

declare @numBuckets;
select @numBuckets = 3;

;with nameBase as
(
    select  ntile(@numBuckets) over(order by ID) as bucket,
            NAME, ID
    from    @NAMES
),
nameRows as
(
    select  row_number() over(partition by bucket order by ID) as rn,
            NAME, ID
    from    nameBase

)
select  n.ID, case when rn = 1 then n.NAME else null end as NAME
from    nameRows n
order by ID;

如果您想要 SQL 2000 或 ANSI 的解决方案，请尝试以下操作:

declare @numRecs int, @numBuckets int, @recsPerBucket int;
select @numRecs = count(*) from @NAMES;
select @numBuckets = 3;
select @recsPerBucket = @numRecs / @numBuckets;

select  n.ID, case when d1.minIdInBucket is null then null else n.NAME end as NAME
from    @NAMES n
left join (
            select  min(n2.ID) as minIdInBucket
            from    (
                        select  n1.ID, n1.NAME,
                                (
                                    select  count(*) / @recsPerBucket
                                    from    @NAMES n2
                                    where   n2.ID < n1.ID
                                ) as bucket
                        from    @NAMES n1
                    ) n2
            group by n2.bucket
        ) d1
on      n.ID = d1.minIdInBucket
order by n.ID;