22,210
社区成员
发帖
与我相关
我的任务
分享
-- =========================================
-- -----------t_mac 小编-------------------
--------------------希望有天成为大虾----
-- =========================================
IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb( ISBN varchar(10),DWMC varchar(10) ,LB varchar(10) ,YS varchar(10),YW varchar(10))
go
insert tb SELECT
'123456' ,'杨昆' ,'A' ,'2M2', '789' UNION ALL SELECT
'123456', '杨昆' ,'B' ,'A4' ,'45' UNION ALL SELECT
'123456','杨昆' ,'C' ,'A4' ,'34' UNION ALL SELECT
'123456' ,'杨昆', 'D' ,'2M2', '98' UNION ALL SELECT
'123456', '杨昆', 'E' ,'2M2', '98'UNION ALL SELECT
'343' , '张三' ,'dd' ,'12', '58' UNION ALL SELECT
'343' , '张三', 'ee' ,'12' ,'58' UNION ALL SELECT
'343' , '张三', 'ff' ,'12', '58'
go
select *
from(
select k.ISBN,k.DWMC,LB=k.LB, YS=k.YS, YW=k.YW, LB1=p.LB, YS1=p.YS,YW11=p.YW
from (select Id=(select count(*) from tb where r.ISBN=ISBN and r.LB>=LB ),* from tb r) k
join (select ID=(select count(*) from tb where r.ISBN=ISBN and r.LB>=LB),* from tb r ) p
on k.ISBN=p.ISBN and k.id=p.id-1 and k.id%2=1
union all
select k.ISBN,k.DWMC,LB=k.LB,YS=k.YS, YW=k.YW, LB1='',YS1='', YW11=' '
from tb k
where not exists(select * from tb where k.ISBN=ISBN and LB>k.LB)) k
order by ISBN ,lb
go
/*
ISBN DWMC LB YS YW LB1 YS1 YW11
---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
123456 杨昆 A 2M2 789 B A4 45
123456 杨昆 C A4 34 D 2M2 98
123456 杨昆 E 2M2 98
343 张三 dd 12 58 ee 12 58
343 张三 ff 12 58
*/
-- =========================================
-- -----------t_mac 小编-------------------
--------------------希望有天成为大虾----
-- =========================================
IF OBJECT_ID('tb') IS NOT NULL
DROP TABLE tb
GO
CREATE TABLE tb( ISBN varchar(10),DWMC varchar(10) ,LB varchar(10) ,YS varchar(10),YW int)
go
insert tb SELECT
'123456' ,'杨昆' ,'A' ,'2M2', 789 UNION ALL SELECT
'123456', '杨昆' ,'B' ,'A4' ,45 UNION ALL SELECT
'123456','杨昆' ,'C' ,'A4' ,34 UNION ALL SELECT
'123456' ,'杨昆', 'D' ,'2M2', 98 UNION ALL SELECT
'123456', '杨昆', 'E' ,'2M2', 98UNION ALL SELECT
'343' , '张三' ,'dd' ,'12', 58 UNION ALL SELECT
'343' , '张三', 'ee' ,'12' ,58 UNION ALL SELECT
'343' , '张三', 'ff' ,'12', 58
go
select * from(
select k.ISBN,
k.DWMC,
LB=k.LB,
YS=k.YS,
YW=k.YW,
LB1=p.LB,
YS1=p.YS,
YW11=p.YW
from (select Id=(select count(*) from tb where r.ISBN=ISBN and r.LB>=LB ),* from tb r) k
join (select ID=(select count(*) from tb where r.ISBN=ISBN and r.LB>=LB),* from tb r ) p
on k.ISBN=p.ISBN and k.id=p.id-1 and k.id%2=1
union all
select k.ISBN,
k.DWMC,
LB=k.LB,
YS=k.YS,
YW=k.YW,
LB1='',
YS1='',
YW11=' '
from tb k
where not exists(select * from tb where k.ISBN=ISBN and LB>k.LB)) k
order by ISBN ,lb
go
/*
(8 行受影响)
ISBN DWMC LB YS YW LB1 YS1 YW11
---------- ---------- ---------- ---------- ----------- ---------- ---------- -----------
123456 杨昆 A 2M2 789 B A4 45
123456 杨昆 C A4 34 D 2M2 98
123456 杨昆 E 2M2 98 0
343 张三 dd 12 58 ee 12 58
343 张三 ff 12 58 0
*/