假设有这么个表:ID 内容
1 a
2 b
3 c
4 d
5 e
6 f
7 g
8 h
…………一直延续
我想通过一个存储过程,输入一个起始行号,比如输入2,每列3个结果,那么就输出查询结果为:b e h
c f i
d g j这个该怎么写呢?我是为了在报表里调用 所以必须得一次输出3列
1 a
2 b
3 c
4 d
5 e
6 f
7 g
8 h
…………一直延续
我想通过一个存储过程,输入一个起始行号,比如输入2,每列3个结果,那么就输出查询结果为:b e h
c f i
d g j这个该怎么写呢?我是为了在报表里调用 所以必须得一次输出3列
解决方案 »
- SQL2005如何备份内存数据
- 有人现在电脑上有SQL2005吗?帮我把2005的转一下2000的!急!
- 如何把列字段中的横线去掉?
- 为什么将一个表导出到另外一个数据库的时候,主键要重新设定?
- SQL嵌套判断语句怎么实现?
- 通过sql语句或存储过程怎样获取这样的结果?(在线等)
- 求一SQL语句!急!麻烦大家帮忙看看!
- 关于WHERE后面能不能加IF判断的?
- 什么样的SQL语句可以获取数据库中最后10条数据
- select * from table1与exec('select * from table1)的区别及其他(在线等)
- 高人帮忙看下sql文
- sql server 2008中有求百分位的函数吗?
MAX(CASE WHEN NUM/3=1 THEN 内容 ELSE '' END) AS 内容1,
MAX(CASE WHEN NUM/3=2 THEN 内容 ELSE '' END) AS 内容2,
MAX(CASE WHEN NUM/3=3 THEN 内容 ELSE '' END) AS 内容3FROM (SELECT NUM=(SELECT COUNT(1) FROM TB WHERE ID<=T.ID),内容 FROM TB T WHERE ID>=@ID ) TGROUP BY (NUM-1)/3
http://blog.csdn.net/roy_88/archive/2007/11/29/1906435.aspxSQL2000時最好生成臨時表再做連接
IF OBJECT_ID('tb') IS NOT NULL DROP TABLE tb
GO
CREATE TABLE tb
(
id int identity,
name varchar(10)
CONSTRAINT PK_TB PRIMARY KEY (id)
)
GO
INSERT TB
select 'a' union all
select 'b' union all
select 'c' union all
select 'd' union all
select 'e' union all
select 'f' union all
select 'g' union all
select 'h'
goIF OBJECT_ID('p_test') IS NOT NULL DROP proc p_test
go
create proc p_test @count int
as
begin
declare @i int
declare @sql varchar(8000)
set @i=1
set @sql='select (id-1)/@count as rn'
while @i<=@count
begin
set @sql=@sql+',max(case when (id-1)%@count='+ltrim(@i-1)+' then name else null end) as col'+ltrim(@i)
set @i=@i+1
end
set @sql=@sql + ' from tb group by (id-1)/@count '
set @sql=replace(@sql,'@count',@count)
exec( @sql)
end
go
--查询
exec p_test 2
--结果
/*
rn col1 col2
----------- ---------- ----------
0 a b
1 c d
2 e f
3 g h
警告: 聚合或其他 SET 操作消除了空值。(4 行受影响)
*/--查询
exec p_test 3
--结果
/*
rn col1 col2 col3
----------- ---------- ---------- ----------
0 a b c
1 d e f
2 g h NULL
警告: 聚合或其他 SET 操作消除了空值。(3 行受影响)
*/
declare @T table([ID] int,[内容] nvarchar(1))
Insert @T
select 1,N'a' union all
select 2,N'b' union all
select 3,N'c' union all
select 4,N'd' union all
select 5,N'e' union all
select 6,N'f' union all
select 7,N'g' union all
select 8,N'h'
Select a.内容,b.内容,c.内容
from @T a
LEFT JOIN @T b ON b.ID=a.ID+3
LEFT JOIN @T c ON c.ID=b.ID+3
WHERE a.ID>1 AND a.ID<5
GO
CREATE TABLE TB(ID INT,COMMENT VARCHAR(10))
INSERT INTO TB
SELECT 1 ,'a' UNION ALL
SELECT 2 ,'b' UNION ALL
SELECT 3 ,'c' UNION ALL
SELECT 4 ,'d' UNION ALL
SELECT 5 ,'e' UNION ALL
SELECT 6 ,'f' UNION ALL
SELECT 7 ,'g' UNION ALL
SELECT 8 ,'h' UNION ALL
SELECT 9 ,'i' UNION ALL
SELECT 10 ,'j' UNION ALL
SELECT 11 ,'k' UNION ALL
SELECT 12 ,'l'DECLARE @ID INT
SELECT @ID=2
SELECT T1.COMMENT ,T2.COMMENT,T3.COMMENT
FROM TB T1
LEFT JOIN TB T2 ON T1.ID=T2.ID-3
LEFT JOIN TB T3 ON T1.ID=T3.ID-6
WHERE T1.ID>=@ID
--如果只取3行,SELECT改成SELECT TOP 3
/*
COMMENT COMMENT COMMENT
---------- ---------- ----------
b e h
c f i
d g j
e h k
f i l
g j NULL
h k NULL
i l NULL
j NULL NULL
k NULL NULL
l NULL NULL
*/
-- Author : htl258(Tony)
-- Date : 2010-06-29 11:59:36
-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)
-- Blog : http://blog.csdn.net/htl258
------------------------------------------------------------------------------------> 生成测试数据表: [tb]
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([ID] [int],[col] [nvarchar](10))
INSERT INTO [tb]
SELECT '1','a' UNION ALL
SELECT '2','b' UNION ALL
SELECT '3','c' UNION ALL
SELECT '4','d' UNION ALL
SELECT '5','e' UNION ALL
SELECT '6','f' UNION ALL
SELECT '7','g' UNION ALL
SELECT '8','h' UNION ALL
SELECT '8','i' UNION ALL
SELECT '8','j'--SELECT * FROM [tb]-->SQL查询如下:
IF OBJECT_ID('p_test')>0
DROP PROC p_test
GO
CREATE PROC p_test
@a INT, --几行起
@b INT --每行几列
AS
DECLARE @collist NVARCHAR(max),@s NVARCHAR(MAX);
WITH t AS
(
SELECT rn=ROW_NUMBER()OVER(ORDER BY id),*
FROM tb
)
,t1 AS
(
SELECT (rn-1)%@b x,col,r2=NTILE(@b)OVER(ORDER BY id)
FROM t
WHERE rn>=@a
)
SELECT @collist=ISNULL(@collist+',','')+QUOTENAME(r2)
FROM T1
GROUP BY r2;
SET @s='
WITH t AS
(
SELECT rn=ROW_NUMBER()OVER(ORDER BY id),*
FROM tb
)
,t1 AS
(
SELECT (rn-1)%'+ltrim(@b)+' x,col,r2=NTILE('+ltrim(@b)+')OVER(ORDER BY id)
FROM t
WHERE rn>='+ltrim(@a)+'
)
SELECT '+@collist+'
FROM T1
PIVOT(MAX(col) FOR r2 IN ('+@collist+')) b'
EXEC(@s)
GOEXEC p_test 2,3
/*
1 2 3
---------- ---------- ----------
d g j
b e h
c f i(3 行受影响)
*/
虽然能写出来……
……为什么不在输出的时候处理?
ps:没有测试但是确定思路可行,同时思路可行但是一定很慢,declare @start int
select @start = 2
select a1.a,a2.a,a3,a
form table a1 left join table a2
on (select count (*) from table ax where ax.ID <a1.id and ax.id >=@start )
= (select count (*) from table ax where ax.ID <a2.id and ax.id >=@start )
- CEILING((select count(*) from table ax ) /3.0)
left join table a3
(select count (*) from table ax where ax.ID <a1.id and ax.id >=@start )
= (select count (*) from table ax where ax.ID <a3.id and ax.id >=@start )
- CEILING((select count(*) from table ax ) /3.0) *2
where
a1.id >=@start
(select count (*) from table ax where ax.ID <a1.id and ax.id >=@start )
<=CEILING((select count(*) from table ax ) /3.0)
select @start = 2
select a1.a,a2.a,a3,a
form table a1 left join table a2
on (select count (*) from table ax where ax.ID <a1.id and ax.id >=@start )
= (select count (*) from table ax where ax.ID <a2.id and ax.id >=@start )
- CEILING((select count(*) from table ax ) /3.0)
left join table a3
(select count (*) from table ax where ax.ID <a1.id and ax.id >=@start )
= (select count (*) from table ax where ax.ID <a3.id and ax.id >=@start )
- CEILING((select count(*) from table ax ) /3.0) *2
where
a1.id >=@start
and (select count (*) from table ax where ax.ID <a1.id and ax.id >=@start )
<=CEILING((select count(*) from table ax ) /3.0) ps:还可以有更扯淡的join写法,貌似。。
IF OBJECT_ID('tb') IS NOT NULL DROP TABLE tb
GO
CREATE TABLE tb
(
id int identity,
name varchar(10)
CONSTRAINT PK_TB PRIMARY KEY (id)
)
GO
INSERT TB
select 'a' union all
select 'b' union all
select 'c' union all
select 'd' union all
select 'e' union all
select 'f' union all
select 'g' union all
select 'h'
goIF OBJECT_ID('p_test') IS NOT NULL DROP proc p_test
go
create proc p_test @id int
as
begin
declare @count int
select name,id=identity(int,1,1) into #t from tb where id>=@id
select @count = count(1) from #t
select
case when id between @count/3.*0 and ceiling(@count/3.)*1 then id
when id between ceiling(@count/3.)*1+1 and ceiling(@count/3.)*2 then id-ceiling(@count/3.)*1
when id between ceiling(@count/3.)*2+1 and ceiling(@count/3.)*3 then id-ceiling(@count/3.)*2
end as rowid,
case when id between @count/3.*0 and ceiling(@count/3.)*1 then name else null end as [n1],
case when id between ceiling(@count/3.)*1+1 and ceiling(@count/3.)*2 then name else null end as [n2],
case when id between ceiling(@count/3.)*2+1 and ceiling(@count/3.)*3 then name else null end as [n3]
into #t1
from #t
select max([n1])n1,max([n2])n2,max([n3])n3 from #t1 group by rowid
end
go
--查询
exec p_test 2
--结果
/*
n1 n2 n3
---------- ---------- ----------
b e h
c f NULL
d g NULL
警告: 聚合或其他 SET 操作消除了空值。(3 行受影响)
*/--查询
exec p_test 3
--结果
/*
n1 n2 n3
---------- ---------- ----------
c e g
d f h
警告: 聚合或其他 SET 操作消除了空值。(2 行受影响)*/
IF OBJECT_ID('[tb]') IS NOT NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb] ([ID] [int],[col] [nvarchar](10))
INSERT INTO [tb]
SELECT '1','a'
UNION ALL
SELECT '2','b' UNION ALL
SELECT '3','c' UNION ALL
SELECT '4','d' UNION ALL
SELECT '5','e' UNION ALL
SELECT '6','f' UNION ALL
SELECT '7','g' UNION ALL
SELECT '8','h' UNION ALL
SELECT '9','i' UNION ALL
SELECT '10','j'godeclare @start int
select @start = 2
select a1.col,a2.col,a3.col
from tb a1 left join tb a2
on (select count (*) from tb ax where ax.ID <a1.id and ax.id >=@start )
= (select count (*) from tb ax where ax.ID <a2.id and ax.id >=@start )
- CEILING((select count(*) from tb ax where id >=@start) /3.0)
left join tb a3 on
(select count (*) from tb ax where ax.ID <a2.id and ax.id >=@start )
= (select count (*) from tb ax where ax.ID <a3.id and ax.id >=@start )
- CEILING((select count(*) from tb ax where id >=@start ) /3.0)
where
a1.id >=@start
and (select count (*) from tb ax where ax.ID <=a1.id and ax.id >=@start )
<=CEILING((select count(*) from tb ax where id >=@start ) /3.0)
我也想后期处理呢 但是不行啊 用的rdlc报表 不会处理