select identity(int,1,1)xh,* into #t from 表
select a.col2 a,b.co2 as b,c.col2 as c
from #t a left join #t as b on a.xh=b.xh-1
left join #t c on a.xh=c.xh-2
select a.col2 a,b.co2 as b,c.col2 as c
from #t a left join #t as b on a.xh=b.xh-1
left join #t c on a.xh=c.xh-2
select a.col2 a,b.co2 as b,c.col2 as c
from #t a left join #t as b on a.xh=b.xh-1
left join #t c on a.xh=c.xh-2
where (a.xh-1)%3=0
,min(case a when 'b' then b end) as b
,min(case a when 'c' then b end) as c
from 你的表
union
select max(case a when 'a' then b end) as a
,max(case a when 'b' then b end) as b
,max(case a when 'c' then b end) as c
from 你的表
from tablename1 where filed1='a'select *,identity(int,1,1) user_id into #1
from tablename1 where filed1='b'select *,identity(int,1,1) user_id into #2
from tablename1 where filed1='c'select #.filed1 as a,#1.filed1 as b,#2.filed1 as c
from # full join #1 on #.id=#1.id
full join COALESCE(#.id,#1.id)=#2.id
select #.filed2 as a,#1.filed2 as b,#2.filed2 as c
from # full join #1 on #.user_id =#1.user_id
full join COALESCE(#.user_id ,#1.user_id )=#2.user_id
create table #( a varchar(10),b int)
insert into # values('a',1)
insert into # values('b',2)
insert into # values('c',3)
insert into # values('a',4)
insert into # values('b',5)
insert into # values('c',6)
insert into # values('d',7)
insert into # values('a',7)
insert into # values('g',7)
select * ,1 as id into #1 from # order by a
declare @id int,@a varchar(10),@sql varchar(8000)
set @id=0
update #1 set @id=id=(case when @a=a then @id+1 else id end),@a=a
set @sql=''
select @sql=@sql +',sum(case when a='''+a+''' then b end) as '+ a from (select distinct a from #1) bb
set @sql=right(@sql,len(@sql)-1)
exec ('select ' + @sql + ' from #1 group by id')
drop table #,#1a b c d g
----------- ----------- ----------- ----------- -----------
4 2 6 7 7
1 5 3 NULL NULL
7 NULL NULL NULL NULL
testid testname testvalue
----------- -------------------------------------------------- -----------
1 a 1
2 b 2
3 c 3
4 a 4
5 b 5
6 c 6SQL语句如下:SELECT SUM(a) AS a,SUM(b) AS b,SUM(c)AS c
FROM
(SELECT CASE testname WHEN 'a' THEN testvalue ELSE 0 END AS a,
CASE testname WHEN 'b' THEN testvalue ELSE 0 END AS b,
CASE testname WHEN 'c' THEN testvalue ELSE 0 END c,
((SELECT COUNT(*) FROM test WHERE testid<=a.testid)+2)/3 AS newtestid
FROM test a) b
GROUP BY newtestid结果:
a b c
----------- ----------- -----------
1 2 3
4 5 6(所影响的行数为 2 行)
a 1
b 2
c 3
a 4
b 5
c 6
A
B
C
一句SQL可以解决
( CrazyFor(上苍保佑吃完了饭的人民)
FROM
(SELECT CASE testname WHEN 'a' THEN testvalue ELSE 0 END AS a,
CASE testname WHEN 'b' THEN testvalue ELSE 0 END AS b,
CASE testname WHEN 'c' THEN testvalue ELSE 0 END c,
((SELECT COUNT(*) FROM test WHERE testid<=a.testid)+2)/3 AS newtestid
FROM test a) b
GROUP BY newtestid
上苍的方法倒是可以解决,还是用了2个语句,虽然比我原来的存储过程提高效率不少.我是谁用1个语句解决问题了,但有个潜在的问题是你中间有这么一句[testid<=a.testid)+2)/3],对于testid一般是自动+1的,如果我删除某一天记录,这个id就空了,下一条记录是在他的基础上+1的,这样这个id就不连续了,(因为不可能保证用户不删除记录,这也是不现实的),这样看来,好像只能像上苍的,先建临时表了??
select a.col2 a,b.co2 as b,c.col2 as c
from 表 a left join 表 as b on a.xh=(select top 1 xh from 表 where 表.xh>a.xh order by 表.xh)-1
left join 表 c on a.xh=(select top 1 xh from 表 where 表.xh>b.xh order by 表.xh)-2
where (a.xh-1)%3=0