create table t2 ( id int ) go declare @index int set @index = 1 while (@index < 100) begin insert t2 select @index set @index = @index + 1 end/*按取模排序*/ select pkid%10,max(id),min(id) from ( select row_number() over(order by id) pkid ,* from t2 ) tbl group by pkid%10go /*按ID排序取10条*/ select pkid/11,max(id),min(id) from ( select row_number() over(order by id) pkid ,* from t2 ) tbl group by pkid/11
如果2000,可以用identity代替row_number
declare @count int ,@sql varchar(8000),@i int,@j int select @count=count(*) from # set @i=1 set @j=1 set @sql='' while @i<=@count begin set @i=@i+10 set @sql=@sql+'select max(id) from # where sid between '+cast(@j as varchar) +' and '+cast(@i as varchar) +' union all ' set @j=@i end set @sql=stuff(@sql,len(@sql)-9,len(@sql),'') print @sql exec(@sql)
/**每三条取最大,最小**/ CREATE TABLE #A (S_NO INT) INSERT INTO #A SELECT 10 INSERT INTO #A SELECT 20 INSERT INTO #A SELECT 30 INSERT INTO #A SELECT 40 INSERT INTO #A SELECT 50 INSERT INTO #A SELECT 60 INSERT INTO #A SELECT 70 INSERT INTO #A SELECT 80 INSERT INTO #A SELECT 90 INSERT INTO #A SELECT 100 INSERT INTO #A SELECT 110 SELECT IDENTITY(INT,1,1)SORT,* INTO #T FROM #A CREATE TABLE #B (S_NO1 INT,S_NO2 INT )DECLARE @NUM INT DECLARE @NUM1 INT SET @NUM=1 SET @NUM1=0 WHILE @NUM<=(SELECT COUNT(*) FROM #A)/3.0+1 BEGIN INSERT INTO #B SELECT MIN(S_NO)S1,MAX(S_NO)S2 FROM #T WHERE SORT/3.0 >@NUM1 AND SORT/3.0<=@NUMSET @NUM1=@NUM SET @NUM=@NUM+1END DROP TABLE #A,#B,#T /*** SELECT * FROM #B10 30 40 60 70 90 100 110
CREATE TABLE #A (S_NO INT) INSERT INTO #A SELECT 10 INSERT INTO #A SELECT 20 INSERT INTO #A SELECT 30 INSERT INTO #A SELECT 40 INSERT INTO #A SELECT 50 INSERT INTO #A SELECT 60 INSERT INTO #A SELECT 70 INSERT INTO #A SELECT 80 INSERT INTO #A SELECT 90 INSERT INTO #A SELECT 100 INSERT INTO #A SELECT 110 SELECT IDENTITY(INT,1,1)SORT,* INTO #T FROM #A CREATE TABLE #B (S_NO1 INT,S_NO2 INT )DECLARE @NUM INT DECLARE @NUM1 INT SET @NUM=1 SET @NUM1=0 WHILE @NUM<=(SELECT COUNT(*) FROM #A)/3.0+1 BEGIN INSERT INTO #B SELECT MIN(S_NO)S1,MAX(S_NO)S2 FROM #T WHERE SORT/3.0 >@NUM1 AND SORT/3.0<=@NUMSET @NUM1=@NUM SET @NUM=@NUM+1END SELECT * FROM #BDROP TABLE #A,#B,#T
--借用11楼的数据 CREATE TABLE #A (S_NO INT) INSERT INTO #A SELECT 10 INSERT INTO #A SELECT 20 INSERT INTO #A SELECT 30 INSERT INTO #A SELECT 40 INSERT INTO #A SELECT 50 INSERT INTO #A SELECT 60 INSERT INTO #A SELECT 70 INSERT INTO #A SELECT 80 INSERT INTO #A SELECT 90 INSERT INTO #A SELECT 100 INSERT INTO #A SELECT 110 --增加分组字段 alter table #A add groupid int go --分组(注:按10个分组则将@i%3改@i%10) declare @i int,@groupid int select @i=0,@groupid=1 update #A set
@groupid=case when @i%3<>0 then @groupid else @groupid+1 end, @i=@i+1, groupid=@groupid--查询 select max(S_NO) S_SNO from #A group by groupid /*结果 S_SNO ----------- 30 60 90 110 */ drop table #A
假如 #A表还有个列 是name 每一行的name列都是 AA; 最后select * from B的时候 怎么能得到 AA 10 30 ....
if object_id('[A]') is not null drop table [A] go create table [A]([name] varchar(2),[value] int) insert [A] select 'aa',10 union all select 'aa',20 union all select 'aa',30 union all select 'aa',40 union all select 'aa',50 union all select 'aa',60 union all select 'aa',70 union all select 'aa',80 union all select 'aa',90 union all select 'aa',100 union all select 'aa',110select *,id=identity(int,0,1) into #T from [A]select name=min(name),[最小值]=min(value),[最大值]=max(value) from #T group by id/3 --测试结果: /* name 最小值 最大值 ---- ----------- ----------- aa 10 30 aa 40 60 aa 70 90 aa 100 110(4 row(s) affected)*/ drop table #T
select pkid/11,max(id),min(id) from ( select row_number() over(order by id) pkid ,* from t2 ) tbl group by pkid/11 这个有点问题:是每隔11行了select ceiling(pkid*1.0/10),max(id),min(id) from ( select row_number() over(order by id) pkid ,* from t2 ) tbl group by ceiling(pkid*1.0/10)
继续提问 如果A表里是这样的内人 aa 10 aa 20 aa 30 bb 40 bb 50 bb 60 cc 70 cc 80 dd 90 dd 100 dd 110 如何显示 aa 10 30 bb 40 60 cc 70 80 dd 90 110
设有连续id列 group by (id+9)/10
看错了,应该是group by (id+9)/10
无他, 12楼的update #A set
@groupid=case when @i%3<>0 then @groupid else @groupid+1 end, @i=@i+1, groupid=@groupid 很不错的写法
if object_id('[a]') is not null drop table [a] go create table [a]([name] varchar(2),[value] int,date datetime) insert [a] select 'aa',10,'2008-9-8' union all select 'aa',20,'2008-9-9' union all select 'aa',30,'2008-9-12' union all select 'bb',40,'2008-9-1' union all select 'bb',50,'2008-9-2' union all select 'bb',60,'2008-9-3' union all select 'cc',70,'2008-9-1' union all select 'cc',80,'2008-9-2' union all select 'dd',90,'2008-9-1' union all select 'dd',100,'2008-9-2' union all select 'dd',110,'2008-9-3' union all select 'dd',120,'2008-9-4' select *,id=(select count(*) from [a] where name=t.name and date>t.date) into #T from [a] t order by name,date desc--select * from #Tselect name,[最小值]=min(value),[最大值]=max(value) from #T group by name,id/3--测试结果: /* name 最小值 最大值 ---- ----------- ----------- aa 10 30 bb 40 60 cc 70 80 dd 100 120 dd 90 90*/ drop table #T
select name,min_val=min(value),max_val=max(value) from A group by name
where sid<=10
union
select max(id) from #
where sid between 10 and 20?
但局限了,值大小与行序的关系。可以利用临时表插入,或row_number
create table t2
(
id int
)
go
declare @index int
set @index = 1
while (@index < 100)
begin
insert t2 select @index
set @index = @index + 1
end/*按取模排序*/
select pkid%10,max(id),min(id)
from
(
select row_number() over(order by id) pkid ,* from t2
) tbl
group by pkid%10go
/*按ID排序取10条*/
select pkid/11,max(id),min(id)
from
(
select row_number() over(order by id) pkid ,* from t2
) tbl
group by pkid/11
select @count=count(*) from #
set @i=1
set @j=1
set @sql=''
while @i<=@count
begin
set @i=@i+10
set @sql=@sql+'select max(id) from # where sid between '+cast(@j as varchar) +' and '+cast(@i as varchar) +' union all '
set @j=@i
end
set @sql=stuff(@sql,len(@sql)-9,len(@sql),'')
print @sql
exec(@sql)
CREATE TABLE #A (S_NO INT)
INSERT INTO #A SELECT 10
INSERT INTO #A SELECT 20
INSERT INTO #A SELECT 30
INSERT INTO #A SELECT 40
INSERT INTO #A SELECT 50
INSERT INTO #A SELECT 60
INSERT INTO #A SELECT 70
INSERT INTO #A SELECT 80
INSERT INTO #A SELECT 90
INSERT INTO #A SELECT 100
INSERT INTO #A SELECT 110
SELECT IDENTITY(INT,1,1)SORT,* INTO #T FROM #A
CREATE TABLE #B (S_NO1 INT,S_NO2 INT )DECLARE @NUM INT
DECLARE @NUM1 INT
SET @NUM=1
SET @NUM1=0
WHILE @NUM<=(SELECT COUNT(*) FROM #A)/3.0+1
BEGIN
INSERT INTO #B
SELECT MIN(S_NO)S1,MAX(S_NO)S2
FROM #T
WHERE SORT/3.0 >@NUM1 AND SORT/3.0<=@NUMSET @NUM1=@NUM
SET @NUM=@NUM+1END
DROP TABLE #A,#B,#T
/***
SELECT * FROM #B10 30
40 60
70 90
100 110
INSERT INTO #A SELECT 10
INSERT INTO #A SELECT 20
INSERT INTO #A SELECT 30
INSERT INTO #A SELECT 40
INSERT INTO #A SELECT 50
INSERT INTO #A SELECT 60
INSERT INTO #A SELECT 70
INSERT INTO #A SELECT 80
INSERT INTO #A SELECT 90
INSERT INTO #A SELECT 100
INSERT INTO #A SELECT 110
SELECT IDENTITY(INT,1,1)SORT,* INTO #T FROM #A
CREATE TABLE #B (S_NO1 INT,S_NO2 INT )DECLARE @NUM INT
DECLARE @NUM1 INT
SET @NUM=1
SET @NUM1=0
WHILE @NUM<=(SELECT COUNT(*) FROM #A)/3.0+1
BEGIN
INSERT INTO #B
SELECT MIN(S_NO)S1,MAX(S_NO)S2
FROM #T
WHERE SORT/3.0 >@NUM1 AND SORT/3.0<=@NUMSET @NUM1=@NUM
SET @NUM=@NUM+1END SELECT * FROM #BDROP TABLE #A,#B,#T
CREATE TABLE #A (S_NO INT)
INSERT INTO #A SELECT 10
INSERT INTO #A SELECT 20
INSERT INTO #A SELECT 30
INSERT INTO #A SELECT 40
INSERT INTO #A SELECT 50
INSERT INTO #A SELECT 60
INSERT INTO #A SELECT 70
INSERT INTO #A SELECT 80
INSERT INTO #A SELECT 90
INSERT INTO #A SELECT 100
INSERT INTO #A SELECT 110
--增加分组字段
alter table #A add groupid int
go
--分组(注:按10个分组则将@i%3改@i%10)
declare @i int,@groupid int
select @i=0,@groupid=1
update #A set
@groupid=case when @i%3<>0 then @groupid else @groupid+1 end,
@i=@i+1,
groupid=@groupid--查询
select max(S_NO) S_SNO from #A group by groupid
/*结果
S_SNO
-----------
30
60
90
110
*/
drop table #A
最后select * from B的时候
怎么能得到
AA 10 30
....
go
create table [A]([name] varchar(2),[value] int)
insert [A]
select 'aa',10 union all
select 'aa',20 union all
select 'aa',30 union all
select 'aa',40 union all
select 'aa',50 union all
select 'aa',60 union all
select 'aa',70 union all
select 'aa',80 union all
select 'aa',90 union all
select 'aa',100 union all
select 'aa',110select *,id=identity(int,0,1) into #T from [A]select name=min(name),[最小值]=min(value),[最大值]=max(value)
from #T
group by id/3
--测试结果:
/*
name 最小值 最大值
---- ----------- -----------
aa 10 30
aa 40 60
aa 70 90
aa 100 110(4 row(s) affected)*/
drop table #T
from
(
select row_number() over(order by id) pkid ,* from t2
) tbl
group by pkid/11
这个有点问题:是每隔11行了select ceiling(pkid*1.0/10),max(id),min(id)
from
(
select row_number() over(order by id) pkid ,* from t2
) tbl
group by ceiling(pkid*1.0/10)
aa 10
aa 20
aa 30
bb 40
bb 50
bb 60
cc 70
cc 80
dd 90
dd 100
dd 110
如何显示
aa 10 30
bb 40 60
cc 70 80
dd 90 110
group by (id+9)/10
看错了,应该是group by (id+9)/10
12楼的update #A set
@groupid=case when @i%3<>0 then @groupid else @groupid+1 end,
@i=@i+1,
groupid=@groupid
很不错的写法
每10条中最大值和最小值
如果是1000条你的这个10条指的什么?
go
create table [a]([name] varchar(2),[value] int,date datetime)
insert [a]
select 'aa',10,'2008-9-8' union all
select 'aa',20,'2008-9-9' union all
select 'aa',30,'2008-9-12' union all
select 'bb',40,'2008-9-1' union all
select 'bb',50,'2008-9-2' union all
select 'bb',60,'2008-9-3' union all
select 'cc',70,'2008-9-1' union all
select 'cc',80,'2008-9-2' union all
select 'dd',90,'2008-9-1' union all
select 'dd',100,'2008-9-2' union all
select 'dd',110,'2008-9-3' union all
select 'dd',120,'2008-9-4' select *,id=(select count(*) from [a] where name=t.name and date>t.date) into #T from [a] t order by name,date desc--select * from #Tselect name,[最小值]=min(value),[最大值]=max(value)
from #T
group by name,id/3--测试结果:
/*
name 最小值 最大值
---- ----------- -----------
aa 10 30
bb 40 60
cc 70 80
dd 100 120
dd 90 90*/
drop table #T
select name,min_val=min(value),max_val=max(value)
from A
group by name