34,838
社区成员




--> 临时表
if object_id('tempdb.dbo.#') is not null drop table #
select num=identity(int,1,1),* into # from datadict where tablename='T_Product_List' and colprk !='yes' order by serialid
select a.num,a.colname,colname1=isnull(b.colname,N'无') from # as a left join # as b on a.num=b.num-1 where a.num%2=1
--> 直接
select a.num,a.colname,colname1=isnull(b.colname,N'无')
from
(select num=(select count(1) from datadict where tablename='T_Product_List' and colprk<>'yes' and serialid<=a.serialid),* from datadict a where tablename='T_Product_List' and colprk<>'yes') a
left join
(select num=(select count(1) from datadict where tablename='T_Product_List' and colprk<>'yes' and serialid<=a.serialid),* from datadict a where tablename='T_Product_List' and colprk<>'yes') b
on a.num=b.num-1 where a.num%2=1
-->放临时表去吧,结果集要重复引用的(left join),用子查询肯定慢
select num=identity(int,1,1),lower(colname) as colname,lower(coltype) as coltype into # from datadict h where tablename='T_Product_List' and colprk !='yes'
select a.num,a.colname,colname1=isnull(b.colname,N'无') from # as a left join # as b on a.num=b.num-1 where b.num%2=1
select id=identity(int,1,1),* into # from ...
--> 困了,具体就不写了,先写个简单的例子:
if object_id('tempdb.dbo.#') is not null drop table #
create table #(id int identity, data nchar(1))
insert # select 'a'
insert # select 'b'
insert # select 'c'
insert # select 'd'
insert # select 'e'
select a.id, a.data, data1=isnull(b.data, N'无') from # a left join # b on a.id=b.id-1 where a.id%2=1
/*
id data data1
----------- ---- -----
1 a b
3 c d
5 e 无
*/