代码之家 › 专栏 › 技术社区 › Vijjendra

在SQL Server中将输出行分组

sql-server sql

Vijjendra · 技术社区 · 16 年前

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

3 回复 | 直到 16 年前

Andomar 16 年前

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;

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;

shahkalpesh 16 年前

SELECT ID, CASE WHEN (ID = 1 OR ID = 6 OR ID = 11) THEN Name Else NULL END
FROM @Names

Andomar 16 年前

select 
    id,
    case 
        when id = 1 then name 
        when id = total/3+1 then name 
        when id = total*2/3+1 then name 
        else '' 
    end
from (
    select row_number() over (order by id) as nr,
        (select count(*) from @names) as total,
        *
    from @names
) sub

推荐文章