用了一个笨办法 select identity(int,1,1) as xh,c.id as a,c.deep as b,c.name as d,i.name as e,i.intime as f,i.auhor as g into #a from cms_column_tbl c,cms_info_tbl i where c.id=i.cid and c.deep=1 order by c.orderid select a.a as a,a.b as b,a.c as c,a.d as d,b.a as e,b.b as f,b.c as g,b.d as h from (select * from #a where xh%2=1) a, (select * from #a where xh%2<>1) b where a.a=b.a
--比上面的笨方法少写些代码 select identity(int,1,1) as xh,c.id as a,c.deep as b,c.name as d,i.name as e,i.intime as f,i.auhor as g into #a from cms_column_tbl c,cms_info_tbl i where c.id=i.cid and c.deep=1 order by c.orderidselect a.*,h=b.e,i=b.f,j=b.g from #a a join #a b on a.xh<>b.xh and a.a=b.a and a.b=b.b and a.d=b.d --这里的条件根据你实际的条件取舍看你的意思是要两行记录的a/b/d都相等
写代码太麻烦,眼下又没有条件测试,说说方法吧: 大概就是select a表的所有纪录,b表出去key字段的所有纪录 from (select 所有字段 from 原始表 where 满足条件1)as a join from (select 所有字段 from 原始表 where 满足条件1)as b on a表的key = b表的key
sorry,写错了,应该是:select a表的所有纪录,b表出去key字段的所有纪录 from (select 所有字段 from 原始表 where 满足条件1)as a join from (select 所有字段 from 原始表 where 满足条件2)as b on a表的key = b表的key
--更正一下我上面的错误select a.*,h=b.e,i=b.f,j=b.g from #a a join #a b on a.xh<b.xh --这里的条件改了 and a.a=b.a and a.b=b.b and a.d=b.d
select gid=0 ,c.id as a,c.deep as b ,c.name as d,i.name as e,i.intime as f,i.auhor as g into #t from cms_column_tbl c,cms_info_tbl i where c.id=i.cid and c.deep=1 order by c.orderiddeclare @a int,@b int,@i int,@s varchar(8000) update #t set @i=case when @a=a and @b=b then @i+1 else 1 end ,gid=@i,@a=a,@b=b select @s='',@i=max(i) from #t while @i>0 select @s=',d=max(case gid when '+cast(@i as varchar) +' then d else '''' end),e=max(case gid when ' +cast(@i as varchar) +' then e else '''' end),f=max(case gid when ' +cast(@i as varchar) +' then f else '''' end),g=max(case gid when ' +cast(@i as varchar) +' then g else '''' end)'+@s,@i=@i-1 exec('select a,b'+@s+' from #t group by a,b')
select identity(int,1,1) as xh,c.id as a,c.deep as b,c.name as d,i.name as e,i.intime as f,i.auhor as g into #a
from cms_column_tbl c,cms_info_tbl i where c.id=i.cid and c.deep=1 order by c.orderid
select a.a as a,a.b as b,a.c as c,a.d as d,b.a as e,b.b as f,b.c as g,b.d as h
from
(select * from #a where xh%2=1) a,
(select * from #a where xh%2<>1) b
where a.a=b.a
select identity(int,1,1) as xh,c.id as a,c.deep as b,c.name as d,i.name as e,i.intime as f,i.auhor as g into #a
from cms_column_tbl c,cms_info_tbl i where c.id=i.cid and c.deep=1 order by c.orderidselect a.*,h=b.e,i=b.f,j=b.g
from #a a join #a b
on a.xh<>b.xh
and a.a=b.a and a.b=b.b and a.d=b.d --这里的条件根据你实际的条件取舍看你的意思是要两行记录的a/b/d都相等
大概就是select a表的所有纪录,b表出去key字段的所有纪录
from (select 所有字段 from 原始表 where 满足条件1)as a
join
from (select 所有字段 from 原始表 where 满足条件1)as b
on a表的key = b表的key
from (select 所有字段 from 原始表 where 满足条件1)as a
join
from (select 所有字段 from 原始表 where 满足条件2)as b
on a表的key = b表的key
from #a a join #a b
on a.xh<b.xh --这里的条件改了
and a.a=b.a and a.b=b.b and a.d=b.d
,c.id as a,c.deep as b
,c.name as d,i.name as e,i.intime as f,i.auhor as g
into #t
from cms_column_tbl c,cms_info_tbl i
where c.id=i.cid and c.deep=1
order by c.orderiddeclare @a int,@b int,@i int,@s varchar(8000)
update #t set @i=case when @a=a and @b=b then @i+1 else 1 end
,gid=@i,@a=a,@b=b
select @s='',@i=max(i) from #t
while @i>0
select @s=',d=max(case gid when '+cast(@i as varchar)
+' then d else '''' end),e=max(case gid when ' +cast(@i as varchar)
+' then e else '''' end),f=max(case gid when ' +cast(@i as varchar)
+' then f else '''' end),g=max(case gid when ' +cast(@i as varchar)
+' then g else '''' end)'+@s,@i=@i-1
exec('select a,b'+@s+' from #t group by a,b')