34,575
社区成员
发帖
与我相关
我的任务
分享
/******************************************************************************/
/*回复:20080610001总:00064 */
/*主题:数据显示 */
/*作者:二等草 */
/******************************************************************************/
set nocount on
--数据--------------------------------------------------------------------------
declare @sp table([商品号] varchar(10),[商品名] varchar(15),[尺码] varchar(8),[价格] varchar(6))
insert into @sp select '001','XX鞋','ss','$1'
insert into @sp select '','','s','$1'
insert into @sp select '','','M','$1.1'
insert into @sp select '','','L','$1.1'
insert into @sp select '','','LL','$1.2'
insert into @sp select '002','YY鞋','ss','$1'
insert into @sp select '','','s','$1'
insert into @sp select '','','M','$1.1'
insert into @sp select '','','L','$1.1'
insert into @sp select '','','LL','$1.2'
insert into @sp select '007','Y衬衫','SS','$2'
insert into @sp select '','','s','$2'
insert into @sp select '','','M','$2.1'
insert into @sp select '','','L','$2.1'
insert into @sp select '','','LL','$2.5'
insert into @sp select '011','商品1','','$2'
insert into @sp select '037','帽子2','','$2'
insert into @sp select '091','领带','','$2'
insert into @sp select '037','XX2','','$2'
insert into @sp select '099','XXX','','$2'
insert into @sp select '199','XX3','','$3'
--代码--------------------------------------------------------------------------
declare @t table(id int identity,bh varchar(10),mc varchar(20),cm varchar(8),jg varchar(8))
declare @i int
insert @t(bh,mc,cm,jg) select * from @sp
set @i = @@rowcount
update a set bh=b.bh,mc = b.mc from @t a,
(select bh,mc from @t where id = (select max(id) from @t where id<=(@i+1)/2 and bh<>'')) b
where a.id = (@i+1)/2+1 and a.bh=''
select a.bh 商品号,a.mc 商品名,a.cm 尺码,a.jg 价格,b.bh 商品号,b.mc 商品名,b.cm 尺码,b.jg 价格 from @t a left join @t b on a.id = b.id-(@i+1)/2
where a.id <=(@i+1)/2
go
/*结果--------------------------------------------------------------------------
品号 商品名 尺码 价格 商品号 商品名 尺码 价格
---------- -------------------- -------- -------- ---------- -------------------- -------- --------
001 XX鞋 ss $1 007 Y衬衫 s $2
s $1 M $2.1
M $1.1 L $2.1
L $1.1 LL $2.5
LL $1.2 011 商品1 $2
002 YY鞋 ss $1 037 帽子2 $2
s $1 091 领带 $2
M $1.1 037 XX2 $2
L $1.1 099 XXX $2
LL $1.2 199 XX3 $3
007 Y衬衫 SS $2 NULL NULL NULL NULL
--清除------------------------------------------------------------------------*/
/******************************************************************************/
/*回复:20080610001总:00064 */
/*主题:数据显示 */
/*作者:二等草 */
/******************************************************************************/
set nocount on
--数据--------------------------------------------------------------------------
declare @sp table([商品号] varchar(10),[商品名] varchar(15),[尺码] varchar(8),[价格] varchar(6))
insert into @sp select '001','XX鞋','ss','$1'
insert into @sp select '','','s','$1'
insert into @sp select '','','M','$1.1'
insert into @sp select '','','L','$1.1'
insert into @sp select '','','LL','$1.2'
insert into @sp select '002','YY鞋','ss','$1'
insert into @sp select '','','s','$1'
insert into @sp select '','','M','$1.1'
insert into @sp select '','','L','$1.1'
insert into @sp select '','','LL','$1.2'
insert into @sp select '007','Y衬衫','SS','$2'
insert into @sp select '','','s','$2'
insert into @sp select '','','M','$2.1'
insert into @sp select '','','L','$2.1'
insert into @sp select '','','LL','$2.5'
insert into @sp select '011','商品1','','$2'
insert into @sp select '037','帽子2','','$2'
insert into @sp select '091','领带','','$2'
insert into @sp select '037','XX2','','$2'
insert into @sp select '099','XXX','','$2'
--代码--------------------------------------------------------------------------
declare @t table(id int identity,bh varchar(10),mc varchar(20),cm varchar(8),jg varchar(8))
insert @t(bh,mc,cm,jg) select * from @sp
select a.bh 商品号,a.mc 商品名,a.cm 尺码,a.jg 价格,b.bh 商品号,b.mc 商品名,b.cm 尺码,b.jg 价格 from @t a left join @t b on a.id = b.id-(@@rowcount+1)/2
where a.id <=(@@rowcount+1)/2
go
/*结果--------------------------------------------------------------------------
商品号 商品名 尺码 价格 商品号 商品名 尺码 价格
---------- -------------------- -------- -------- ---------- -------------------- -------- --------
001 XX鞋 ss $1 007 Y衬衫 SS $2
s $1 s $2
M $1.1 M $2.1
L $1.1 L $2.1
LL $1.2 LL $2.5
002 YY鞋 ss $1 011 商品1 $2
s $1 037 帽子2 $2
M $1.1 091 领带 $2
L $1.1 037 XX2 $2
LL $1.2 099 XXX $2
--清除------------------------------------------------------------------------*/