我必须将行平均划分
所以这里有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

最佳答案

如果您使用的是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;

关于sql - 在SQL Server中将输出行分组,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/1756293/

10-15 03:00
查看更多