sql中一个小纠结，望高人会诊一下。

[风之影子]

需求是这样的，我声明了六个变量(存第一行的某个值列变量，一个时间变量，依次第二行值，时间，第三行值，时间)

通过物理表生成一个内存表如#temp_Table（表里有时间列,并在生成时已按时间顺序排序）

我可以通过select top 3 * from #temp_table where time>第一行的时间值

需求的要求是第二次提取的三行数据是基于第一次三行的第二行开始，例如：

123(第一次的三行)
234(第二次)
345(三)
456(四)


现在我的实现是通过三条查询语句，来实现。最终想通过一次查询内存表，赋值六个变量。看看高手们的语句写法。

[风之影子]

set @basicnumber=1
					select @onenumber=avgstress,@onetime=monitortime from #temptable where tempid=@basicnumber
					select @twonumber=avgstress,@twotime=monitortime from #temptable where tempid=@basicnumber+1
					select @threenumber=avgstress,@threetime=monitortime from #temptable where tempid=@basicnumber+2
set @basicnumber=@basicnumber+1

[风之影子]

declare @onenumber float,@twonumber float,@threenumber float
declare @onetime datetime,@twotime datetime,@threetime datetime

上面是声明的变量
下面循环内部的赋值

set @basicnumber=1
select @onenumber=avgstress,@onetime=monitortime from #temptable where tempid=@basicnumber
select @twonumber=avgstress,@twotime=monitortime from #temptable where tempid=@basicnumber+1
select @threenumber=avgstress,@threetime=monitortime from #temptable where tempid=@basicnumber+2
set @basicnumber=@basicnumber+1

[天-笑]

declare @t TABLE(Row INT )

INSERT INTO @t
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 

;with Cet as
(
SELECT ((ROW-1)/3)+1 as Gid,
		convert(varchar(8),Row) as Row
 FROM @T
)

select Gid, 
	[RowS]=stuff((select ','+Row from Cet  where Gid=T.Gid for xml path('')), 1, 1, '')
from Cet T
group by Gid
(7 行受影响)
Gid         RowS
----------- --------------------------------------------------------------------------------------------------------------------------------------------------------------1           1,2,3
2           4,5,6
3           7

(3 行受影响)

[天-笑]

Gid         RowS
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1           1,2,3
2           4,5,6
3           7

(3 行受影响)

[快溜]

生成#temp_Table的时候加个row_number

select row_number() over(order by 时间) as no,* into #temp_Table from tb
select * from #temp_Table where no between 1 and 3
select * from #temp_Table where no between 2 and 4
select * from #temp_Table where no between 3 and 5
select * from #temp_Table where no between 4 and 6

[--小F--]

合并列值 
--*******************************************************************************************
表结构，数据如下： 
id    value 
----- ------ 
1    aa 
1    bb 
2    aaa 
2    bbb 
2    ccc 

需要得到结果： 
id    values 
------ ----------- 
1      aa,bb 
2      aaa,bbb,ccc 
即：group by id, 求 value 的和（字符串相加） 

1. 旧的解决方法(在sql server 2000中只能用函数解决。) 
--=============================================================================
create table tb(id int, value varchar(10)) 
insert into tb values(1, 'aa') 
insert into tb values(1, 'bb') 
insert into tb values(2, 'aaa') 
insert into tb values(2, 'bbb') 
insert into tb values(2, 'ccc') 
go 
--1. 创建处理函数 
CREATE FUNCTION dbo.f_strUnite(@id int) 
RETURNS varchar(8000) 
AS 
BEGIN 
    DECLARE @str varchar(8000) 
    SET @str = '' 
    SELECT @str = @str + ',' + value FROM tb WHERE id=@id 
    RETURN STUFF(@str, 1, 1, '') 
END 
GO 
-- 调用函数 
SELECt id, value = dbo.f_strUnite(id) FROM tb GROUP BY id 
drop table tb 
drop function dbo.f_strUnite 
go
/* 
id          value      
----------- ----------- 
1          aa,bb 
2          aaa,bbb,ccc 
（所影响的行数为 2 行） 
*/ 
--===================================================================================
2. 新的解决方法(在sql server 2005中用OUTER APPLY等解决。) 
create table tb(id int, value varchar(10)) 
insert into tb values(1, 'aa') 
insert into tb values(1, 'bb') 
insert into tb values(2, 'aaa') 
insert into tb values(2, 'bbb') 
insert into tb values(2, 'ccc') 
go 
-- 查询处理 
SELECT * FROM(SELECT DISTINCT id FROM tb)A OUTER APPLY( 
        SELECT [values]= STUFF(REPLACE(REPLACE( 
            ( 
                SELECT value FROM tb N 
                WHERE id = A.id 
                FOR XML AUTO 
            ), ' <N value="', ','), '"/>', ''), 1, 1, '') 
)N 
drop table tb 

/* 
id          values 
----------- ----------- 
1          aa,bb 
2          aaa,bbb,ccc 

(2 行受影响) 
*/ 

--SQL2005中的方法2 
create table tb(id int, value varchar(10)) 
insert into tb values(1, 'aa') 
insert into tb values(1, 'bb') 
insert into tb values(2, 'aaa') 
insert into tb values(2, 'bbb') 
insert into tb values(2, 'ccc') 
go 

select id, [values]=stuff((select ','+[value] from tb t where id=tb.id for xml path('')), 1, 1, '') 
from tb 
group by id 

/* 
id          values 
----------- -------------------- 
1          aa,bb 
2          aaa,bbb,ccc 

(2 row(s) affected) 

*/ 

drop table tb 

/*

[风之影子]

高手：能否再用白话简述一下，你的实现过程。

[快溜]

[Quote=引用 5 楼 libinguest 的回复:]
高手：能否再用白话简述一下，你的实现过程。
[/Quote]多行合并在一起？

合并列值 
--*******************************************************************************************
表结构，数据如下： 
id    value 
----- ------ 
1    aa 
1    bb 
2    aaa 
2    bbb 
2    ccc 

需要得到结果： 
id    values 
------ ----------- 
1      aa,bb 
2      aaa,bbb,ccc 
即：group by id, 求 value 的和（字符串相加） 

1. 旧的解决方法(在sql server 2000中只能用函数解决。) 
--=============================================================================
create table tb(id int, value varchar(10)) 
insert into tb values(1, 'aa') 
insert into tb values(1, 'bb') 
insert into tb values(2, 'aaa') 
insert into tb values(2, 'bbb') 
insert into tb values(2, 'ccc') 
go 
--1. 创建处理函数 
CREATE FUNCTION dbo.f_strUnite(@id int) 
RETURNS varchar(8000) 
AS 
BEGIN 
    DECLARE @str varchar(8000) 
    SET @str = '' 
    SELECT @str = @str + ',' + value FROM tb WHERE id=@id 
    RETURN STUFF(@str, 1, 1, '') 
END 
GO 
-- 调用函数 
SELECt id, value = dbo.f_strUnite(id) FROM tb GROUP BY id 
drop table tb 
drop function dbo.f_strUnite 
go
/* 
id          value      
----------- ----------- 
1          aa,bb 
2          aaa,bbb,ccc 
（所影响的行数为 2 行） 
*/ 
--===================================================================================
2. 新的解决方法(在sql server 2005中用OUTER APPLY等解决。) 
create table tb(id int, value varchar(10)) 
insert into tb values(1, 'aa') 
insert into tb values(1, 'bb') 
insert into tb values(2, 'aaa') 
insert into tb values(2, 'bbb') 
insert into tb values(2, 'ccc') 
go 
-- 查询处理 
SELECT * FROM(SELECT DISTINCT id FROM tb)A OUTER APPLY( 
        SELECT [values]= STUFF(REPLACE(REPLACE( 
            ( 
                SELECT value FROM tb N 
                WHERE id = A.id 
                FOR XML AUTO 
            ), ' <N value="', ','), '"/>', ''), 1, 1, '') 
)N 
drop table tb 

/* 
id          values 
----------- ----------- 
1          aa,bb 
2          aaa,bbb,ccc 

(2 行受影响) 
*/ 

--SQL2005中的方法2 
create table tb(id int, value varchar(10)) 
insert into tb values(1, 'aa') 
insert into tb values(1, 'bb') 
insert into tb values(2, 'aaa') 
insert into tb values(2, 'bbb') 
insert into tb values(2, 'ccc') 
go 

select id, [values]=stuff((select ','+[value] from tb t where id=tb.id for xml path('')), 1, 1, '') 
from tb 
group by id 

/* 
id          values 
----------- -------------------- 
1          aa,bb 
2          aaa,bbb,ccc 

(2 row(s) affected) 

*/ 

[风之影子]

[Quote=引用 3 楼 ssp2009 的回复:]
生成#temp_Table的时候加个row_number

SQL code
select row_number() over(order by 时间) as no,* into #temp_Table from tb
select * from #temp_Table where no between 1 and 3
select * from #temp_Table where no……
[/Quote]


如何用查出的三行初始化声明的六个变量，高手再看一下，可否实现。

[快溜]

[Quote=引用 7 楼 libinguest 的回复:]
引用 3 楼 ssp2009 的回复:
生成#temp_Table的时候加个row_number

SQL code
select row_number() over(order by 时间) as no,* into #temp_Table from tb
select * from #temp_Table where no between 1 and 3
select * from……
[/Quote]

declare @date datetime
select @date=时间 from #temp_Table

[天-笑]

declare @t TABLE(Row INT )

INSERT INTO @t
SELECT 1 UNION ALL
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5 UNION ALL
SELECT 6 UNION ALL
SELECT 7 

;with Cet as
(
SELECT a.Row as Gid,convert(varchar(8),b.Row) as Row
 FROM @T a  left join @T  b on b.Row >= a.Row and b.Row<a.Row +3
)


select Gid, 
	[RowS]=stuff((select ','+Row from Cet  where Gid=T.Gid for xml path('')), 1, 1, '')
from Cet T
group by Gid

-----------------------------
(7 行受影响)
Gid         RowS
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1           1,2,3
2           2,3,4
3           3,4,5
4           4,5,6
5           5,6,7
6           6,7
7           7

(7 行受影响)

[风之影子]

谢谢各位，我一先看用到的函数，再深入研究一下。

[fcuandy]

select 
@onenumber = max(case when tempid=@basicnumber then avgstress else null end),
@twonumber = max(case when tempid=@basicnumber+1 then avg.....),
@thrnumber = max(case when tempid=@basicnumber+2 then avg.....),
@onetime = max(case when tempid=@basicnumber then monitortime else null end),
@twotime = max(case when tempid=@basicnumber+1 then monitortime else null end),
@thrtime =max(.....)
from #temptable where tempid between @basicnumber and @basicnumber+2

前提假定tempid连续且唯一

[风之影子]

[Quote=引用 8 楼 ssp2009 的回复:]
引用 7 楼 libinguest 的回复:
引用 3 楼 ssp2009 的回复:
生成#temp_Table的时候加个row_number

SQL code
select row_number() over(order by 时间) as no,* into #temp_Table from tb
select * from #temp_Table where no betwee……
[/Quote]


哥们：这个还是用了三条语句来赋值。

[fcuandy]

把你自己的代码贴出来， 再指明 你的哪三条语句 要改作一条。

[xrongzhen]

[Quote=引用 15 楼 fcuandy 的回复:]
SQL code
select
@onenumber = max(case when tempid=@basicnumber then avgstress else null end),
@twonumber = max(case when tempid=@basicnumber+1 then avg.....),
@thrnumber = max(case when tempid=@b……
[/Quote]
不唯一的话 自己用row_number()生成一个唯一列

;with cte as
(
    select *,rn = row_number() over(order by tempid) from #temptable
)
select 
@onenumber = max(case when rn=@basicnumber then avgstress else null end),
@twonumber = max(case when rn=@basicnumber+1 then avg.....),
@thrnumber = max(case when rn=@basicnumber+2 then avg.....),
@onetime = max(case when rn=@basicnumber then monitortime else null end),
@twotime = max(case when rn=@basicnumber+1 then monitortime else null end),
@thrtime =max(.....)
from cte where tempid between @basicnumber and @basicnumber+2

[xrongzhen]

;with cte as
(
    select *,rn = row_number() over(order by tempid) from #temptable
)
select 
@onenumber = max(case when rn=@basicnumber then avgstress else null end),
@twonumber = max(case when rn=@basicnumber+1 then avg.....),
@thrnumber = max(case when rn=@basicnumber+2 then avg.....),
@onetime = max(case when rn=@basicnumber then monitortime else null end),
@twotime = max(case when rn=@basicnumber+1 then monitortime else null end),
@thrtime =max(.....)
from cte where rn between @basicnumber and @basicnumber+2

[flztfly]

Mark Twin slip here.