SELECT *, 0 flag
INTO CompareScore
FROM QuestionResult
ORDER BY SurveyID DECLARE @a int, @b int
UPDATE #
SET @a = CASE WHEN @b = SurveyID THEN @a + 1 ELSE 1 END,
@b = SurveyID, flag = @a
SELECT a.AvgScore, b.AvgScore
FROM # a, # b
WHERE a.SurveyID = 1 AND b.SurveyID = 2 AND
a.flag = b.flag go DROP TABLE CompareScore
Select identity(int,1,1) as id,result as result1 into #tmp1 from table where id=1
Select identity(int,1,1) as id,result as result2 into #tmp2 from table where id=2
Select result1,result2 from #tmp1 join #tmp2 on #tmp1.id = #tmp2.id
select a.*,b.result,c.result
from (select distinct num from abc) a left join (select * from abc where id=1) b on a.num=b.num left join (select * from abc where id=2) c on a.num=c.num
--创建数据测试环境
declare @tb table(ID int,Result varchar(4),Num int)
insert into @tb
select 1,'aaa',1
union all select 1,'bbb',2
union all select 1,'fff',3
union all select 2,'ccc',2
union all select 2,'ddd',3
union all select 2,'eee',4
--查询结果
select Num=isnull(a.num,b.num)
,Result1=isnull(a.result,'')
,Result2=isnull(b.result,'')
from(
select result,num from @tb where id=1
) a full join (
select result,num from @tb where id=2
) b on a.num=b.num
order by num
select Num=isnull(a.num,b.num)
,Result1=isnull(a.result,'')
,Result2=isnull(b.result,'')
from(
select result,num from 表 where id=1
) a full join (
select result,num from 表 where id=2
) b on a.num=b.num
order by num