大家好举个例子 大体说下--一个表有10个数字
111,
112,
113,
114,
115,
116,
117,
118,
119,
120/*每6个数 为一组
希望得到*/
组 数字 行
1 111 1
1 112 2
1 113 3
1 114 4
1 115 5
1 116 6
2 117 1
2 118 2
2 119 3
2 120 4
就是 每6个数为一组 行 就是从1-6这个该怎么实现呢?
如果不知道表的具体数量 该怎么弄呢?
我想了好久 想不出来 所以麻烦大家了
111,
112,
113,
114,
115,
116,
117,
118,
119,
120/*每6个数 为一组
希望得到*/
组 数字 行
1 111 1
1 112 2
1 113 3
1 114 4
1 115 5
1 116 6
2 117 1
2 118 2
2 119 3
2 120 4
就是 每6个数为一组 行 就是从1-6这个该怎么实现呢?
如果不知道表的具体数量 该怎么弄呢?
我想了好久 想不出来 所以麻烦大家了
INSERT @TB
SELECT 111 UNION ALL
SELECT 112 UNION ALL
SELECT 113 UNION ALL
SELECT 114 UNION ALL
SELECT 115 UNION ALL
SELECT 116 UNION ALL
SELECT 117 UNION ALL
SELECT 118 UNION ALL
SELECT 119 UNION ALL
SELECT 120SELECT (ID-1)/6+1 AS GRP,COL,(ID-1)%6+1 AS RID
FROM (
SELECT *,ID=(SELECT COUNT(*) FROM @TB WHERE COL<=A.COL) FROM @TB AS A
) T
/*
GRP COL RID
----------- ----------- -----------
1 111 1
1 112 2
1 113 3
1 114 4
1 115 5
1 116 6
2 117 1
2 118 2
2 119 3
2 120 4
*/
-- Author: happyflystone
-- Date:2008-12-29 15:35:24
-------------------------------------- Test Data: TA
IF OBJECT_ID('TA') IS NOT NULL
DROP TABLE TA
Go
CREATE TABLE TA(col INT)
Go
INSERT INTO TA
SELECT 111 UNION ALL
SELECT 112 UNION ALL
SELECT 113 UNION ALL
SELECT 114 UNION ALL
SELECT 115 UNION ALL
SELECT 116 UNION ALL
SELECT 117 UNION ALL
SELECT 118 UNION ALL
SELECT 119 UNION ALL
SELECT 120
GO
--Start
;with t
as
(
SELECT
* ,rid=row_number() over (order by col)
FROM
TA)
select col,rid/7+1 as [group],rowid = row_number() over(partition by (rid/7) order by col)
from t
--Result:
/*
col group rowid
----------- -------------------- --------------------
111 1 1
112 1 2
113 1 3
114 1 4
115 1 5
116 1 6
117 2 1
118 2 2
119 2 3
120 2 4(10 行受影响)*/
--End
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([col] int)
insert [tb]
select '111' union all
select '112' union all
select '113' union all
select '114' union all
select '115' union all
select '116' union all
select '117' union all
select '118' union all
select '119' union all
select '120'
---查询---
select
(select count(1) from tb where col<=t.col)/6+1 as [组],
col as [数字],
(select count(1) from tb where col<t.col)%6+1 [行]
from [tb] t
---结果---
组 数字 行
----------- ----------- -----------
1 111 1
1 112 2
1 113 3
1 114 4
1 115 5
2 116 6
2 117 1
2 118 2
2 119 3
2 120 4(所影响的行数为 10 行)
select rowno/6+1 组,数字,(rowno-1)%6+1 行 from (
select row_number() OVER (ORDER BY 数字 ASC) as rowno,* from 表名
) as A
insert into tk select 111
insert into tk select 112
insert into tk select 113
insert into tk select 114
insert into tk select 115
insert into tk select 116
insert into tk select 117
insert into tk select 118
insert into tk select 119
insert into tk select 120
go
select
(select count(*) from tk where col1<t.col1)/6+1 as '组',
col1 as '数字',
row_number() over(partition by (select count(*) from tk where col1<t.col1)/6 order by col1) as '行'
from tk t组 数字 行
1 111 1
1 112 2
1 113 3
1 114 4
1 115 5
1 116 6
2 117 1
2 118 2
2 119 3
2 120 4
DECLARE @TB TABLE(COL INT)
INSERT @TB
SELECT 111 UNION ALL
SELECT 112 UNION ALL
SELECT 113 UNION ALL
SELECT 114 UNION ALL
SELECT 115 UNION ALL
SELECT 116 UNION ALL
SELECT 117 UNION ALL
SELECT 118 UNION ALL
SELECT 119 UNION ALL
SELECT 120SELECT *,ID=IDENTITY(INT,1,1) INTO # FROM @TB SELECT (SEQ-1)/6+1 AS GRP,COL,(SEQ-1)%6+1 AS RID
FROM (
SELECT *,SEQ=(SELECT COUNT(*) FROM # WHERE COL<=A.COL) FROM # AS A
) TDROP TABLE #
/*
GRP COL RID
----------- ----------- -----------
1 111 1
1 112 2
1 113 3
1 114 4
1 115 5
1 116 6
2 117 1
2 118 2
2 119 3
2 120 4
*/
-- Test Data: TA
IF OBJECT_ID('TA') IS NOT NULL
DROP TABLE TA
Go
CREATE TABLE TA(col varchar(10))
Go
INSERT INTO TA
SELECT 111 UNION ALL
SELECT 112 UNION ALL
SELECT 113 UNION ALL
SELECT 114 UNION ALL
SELECT 115 UNION ALL
SELECT 116 UNION ALL
SELECT 117 UNION ALL
SELECT 118 UNION ALL
SELECT 119 UNION ALL
SELECT 120
GO
select rid=identity(int,1,1),*
into #
from taselect col,rid/7+1 as [group]
into #1
from #select *,rowid=(select count(1) from #1 where [group] = a.[group] and col <= a.col)
from #1 as a
drop table #,#1/*
col group rowid
---------- ----------- -----------
111 1 1
112 1 2
113 1 3
114 1 4
115 1 5
116 1 6
117 2 1
118 2 2
119 2 3
120 2 4(10 行受影响)
*/
一样的
---测试数据---
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([col] varchar(5))
insert [tb]
select '111' union all
select '112' union all
select '113' union all
select '114' union all
select '115' union all
select '116' union all
select '117' union all
select '118' union all
select '119' union all
select '120'
---查询---
select
(select count(1) from tb where col<=t.col)/6+1 as [组],
col as [数字],
(select count(1) from tb where col<t.col)%6+1 [行]
from [tb] t
---结果---
组 数字 行
----------- ----- -----------
1 111 1
1 112 2
1 113 3
1 114 4
1 115 5
2 116 6
2 117 1
2 118 2
2 119 3
2 120 4(所影响的行数为 10 行)
(
number int
)
insert into #S
select 111 union all
select 112 union all
select 113 union all
select 114 union all
select 115 union all
select 116 union all
select 117 union all
select 118 union all
select 119 union all
select 120
create table #GR
(
id int identity(1,1),
number int
)
insert into #GR
select * from #S
select group1,number,ROW_NUMBER() over(partition by group1 order by number) from
(select (id/6+1) group1,number from #GR where id%6<>0
union all
select (id/6) group1,number from #GR where id%6=0
) A
INSERT @t SELECT 111
UNION ALL SELECT 112
UNION ALL SELECT 113
UNION ALL SELECT 114
UNION ALL SELECT 115
UNION ALL SELECT 116
UNION ALL SELECT 117
UNION ALL SELECT 118
UNION ALL SELECT 119
UNION ALL SELECT 120
/************/
/*Test Data*/
/***fcuandy**/
/*2008-12-30*/
/************/SELECT cid,f1,ROW_NUMBER() OVER(PARTITION BY cid ORDER BY id) idx
FROM
(
SELECT id=ROW_NUMBER() OVER(ORDER BY GETDATE()) ,
cid=(ROW_NUMBER() OVER(ORDER BY GETDATE())-1)/6+1 ,
f1
FROM @t
) X
/*
1 111 1
1 112 2
1 113 3
1 114 4
1 115 5
1 116 6
2 117 1
2 118 2
2 119 3
2 120 4
*/
insert into test(COL) values('111')
insert into test(COL) values('112')
insert into test(COL) values('113')
insert into test(COL) values('114')
insert into test(COL) values('115')
insert into test(COL) values('116')
insert into test(COL) values('117')
insert into test(COL) values('118')
insert into test(COL) values('119')
insert into test(COL) values('120')
godeclare @i int,@j int
select @i=1,@j=0update test
set
@i=case when @j=6 then @i+1 else @i end,
@j=case when @j=6 then 1 else @j+1 end,
GRP=@i,RID=@Jselect * from test/*
GRP COL RID
----------- -------------------- -----------
1 111 1
1 112 2
1 113 3
1 114 4
1 115 5
1 116 6
2 117 1
2 118 2
2 119 3
2 120 4
*/
godrop table test