更改一下,结果要求如下:REF_CODE QTY ITEM_CODE DES TRX_CODE INDATE QUANTITY FREEGOODS PO216-3077 5300 82-00010 塑胶A TR2037147 2006-9-22 5300 N PO216-3185 10200 82-00010 塑胶A TR2037750 2006-10-13 10200 N PO216-3588 5500 82-00010 塑胶A TR2038495 2006-11-04 660 N TR2038418 2006-11-02 2700 N TR2038494 2006-11-09 2140 N
if not object_id('tb') is null drop table tb Go Create table tb([REF_CODE] nvarchar(10),[QTY] int,[ITEM_CODE] nvarchar(8),[DES] nvarchar(3),[TRX_CODE] nvarchar(9),[INDATE] Datetime,[QUANTITY] int,[FREEGOODS] nvarchar(1)) Insert tb select N'PO216-3077',5300,N'82-00010',N'塑胶A',N'TR2037147','2006-9-22',5300,N'N' union all select N'PO216-3185',10200,N'82-00010',N'塑胶A',N'TR2037750','2006-10-13',10200,N'N' union all select N'PO216-3588',5500,N'82-00010',N'塑胶A',N'TR2038495','2006-11-04',660,N'N' union all select N'PO216-3588',5500,N'82-00010',N'塑胶A',N'TR2038418','2006-11-02',2700,N'N' union all select N'PO216-3588',5500,N'82-00010',N'塑胶A',N'TR2038494','2006-11-09',2140,N'N' Go --2000加个自增列 alter table tb add id int identity go select [REF_CODE]=case when exists(select 1 from tb where [REF_CODE]=t.[REF_CODE] and id>t.id) then '' else [REF_CODE] end, [QTY]=case when exists(select 1 from tb where [REF_CODE]=t.[REF_CODE] and id>t.id) then null else [QTY] end, [ITEM_CODE], [DES], [TRX_CODE], [INDATE] , [QUANTITY], [FREEGOODS] from tb t go alter table tb drop column ID go ---2005 ;with tt as (select ID=row_number()over(order by getdate()), * from tb) select [REF_CODE]=case when exists(select 1 from tt where [REF_CODE]=t.[REF_CODE] and id>t.id) then '' else [REF_CODE] end, [QTY]=case when exists(select 1 from tt where [REF_CODE]=t.[REF_CODE] and id>t.id) then null else [QTY] end, [ITEM_CODE], [DES], [TRX_CODE], [INDATE] , [QUANTITY], [FREEGOODS] from tt t go /* REF_CODE QTY ITEM_CODE DES TRX_CODE INDATE QUANTITY FREEGOODS ---------- ----------- --------- ---- --------- ----------------------- ----------- --------- PO216-3077 5300 82-00010 塑胶A TR2037147 2006-09-22 00:00:00.000 5300 N PO216-3185 10200 82-00010 塑胶A TR2037750 2006-10-13 00:00:00.000 10200 N NULL 82-00010 塑胶A TR2038495 2006-11-04 00:00:00.000 660 N NULL 82-00010 塑胶A TR2038418 2006-11-02 00:00:00.000 2700 N PO216-3588 5500 82-00010 塑胶A TR2038494 2006-11-09 00:00:00.000 2140 N */
ITEM_CODE DES 自己照写吧
这样行不: create table tb(REF_CODE nvarchar(20),QTY nvarchar(10),ITEM_CODE nvarchar(10),[DES] nvarchar(10),TRX_CODE nvarchar(10),INDATE datetime,QUANTITY int,FREEGOODS nvarchar(10)) insert into tb select 'PO216-3077','5300','82-00010','塑胶A','TR2037147','2006-9-22',5300,'N' insert into tb select 'PO216-3185','10200','82-00010','塑胶A','TR2037750','2006-10-13',10200,'N' insert into tb select 'PO216-3588','5500','82-00010','塑胶A','TR2038495','2006-11-04',660,'N' insert into tb select 'PO216-3588','5500','82-00010','塑胶A','TR2038418','2006-11-02',2700,'N' insert into tb select 'PO216-3588','5500','82-00010','塑胶A','TR2038494','2006-11-09',2140,'N' go select (case when ranknum=1 then REF_CODE else '' end) as REF_CODE, (case when ranknum=1 then QTY else '' end) as QTY, (case when ranknum=1 then ITEM_CODE else '' end) as ITEM_CODE, [DES],TRX_CODE,INDATE,QUANTITY,FREEGOODS FROM( select rank() over(partition by REF_CODE order by QUANTITY)as ranknum,* from tb )T go drop table tb /* REF_CODE QTY ITEM_CODE DES TRX_CODE INDATE QUANTITY FREEGOODS -------------------- ---------- ---------- ---------- ---------- ----------------------- ----------- ---------- PO216-3077 5300 82-00010 塑胶A TR2037147 2006-09-22 00:00:00.000 5300 N PO216-3185 10200 82-00010 塑胶A TR2037750 2006-10-13 00:00:00.000 10200 N PO216-3588 5500 82-00010 塑胶A TR2038495 2006-11-04 00:00:00.000 660 N 塑胶A TR2038494 2006-11-09 00:00:00.000 2140 N 塑胶A TR2038418 2006-11-02 00:00:00.000 2700 N(5 行受影响)*/
create table tb(REF_CODE nvarchar(20),QTY nvarchar(10),ITEM_CODE nvarchar(10),[DES] nvarchar(10),TRX_CODE nvarchar(10),INDATE datetime,QUANTITY int,FREEGOODS nvarchar(10)) insert into tb select 'PO216-3077','5300','82-00010','塑胶A','TR2037147','2006-9-22',5300,'N' insert into tb select 'PO216-3185','10200','82-00010','塑胶A','TR2037750','2006-10-13',10200,'N' insert into tb select 'PO216-3588','5500','82-00010','塑胶A','TR2038495','2006-11-04',660,'N' insert into tb select 'PO216-3588','5500','82-00010','塑胶A','TR2038418','2006-11-02',2700,'N' insert into tb select 'PO216-3588','5500','82-00010','塑胶A','TR2038494','2006-11-09',2140,'N' go select (case when ranknum=1 then REF_CODE else '' end) as REF_CODE, (case when ranknum=1 then QTY else '' end) as QTY, (case when ranknum=1 then ITEM_CODE else '' end) as ITEM_CODE, (case when ranknum=1 then [DES] else '' end) as [DES], TRX_CODE,INDATE,QUANTITY,FREEGOODS FROM( select rank() over(partition by REF_CODE order by QUANTITY)as ranknum,* from tb )T go drop table tb /* REF_CODE QTY ITEM_CODE DES TRX_CODE INDATE QUANTITY FREEGOODS -------------------- ---------- ---------- ---------- ---------- ----------------------- ----------- ---------- PO216-3077 5300 82-00010 塑胶A TR2037147 2006-09-22 00:00:00.000 5300 N PO216-3185 10200 82-00010 塑胶A TR2037750 2006-10-13 00:00:00.000 10200 N PO216-3588 5500 82-00010 塑胶A TR2038495 2006-11-04 00:00:00.000 660 N TR2038494 2006-11-09 00:00:00.000 2140 N TR2038418 2006-11-02 00:00:00.000 2700 N(5 行受影响) /*
--借用#6演示数据 SELECT REF_CODE = CASE WHEN b.REF_CODE IS NULL THEN a.REF_CODE ELSE '' END, QTY = CASE WHEN b.REF_CODE IS NULL THEN a.QTY ELSE NULL END, ITEM_CODE = CASE WHEN b.REF_CODE IS NULL THEN a.ITEM_CODE ELSE '' END, DES = CASE WHEN b.REF_CODE IS NULL THEN a.DES ELSE '' END, a.TRX_CODE, a.INDATE, a.QUANTITY, a.FREEGOODS FROM tb a LEFT JOIN tb b ON a.REF_CODE = b.REF_CODE and a.QTY = b.QTY and a.INDATE > b.INDATE --WHERE a......bla bla bla GROUP BY a.REF_CODE, b.REF_CODE, a.QTY, a.ITEM_CODE, a.DES, a.TRX_CODE, a.INDATE, a.QUANTITY, a.FREEGOODS ORDER BY a.REF_CODE, a.QTY, a.INDATE/* REF_CODE QTY ITEM_CODE DES TRX_CODE INDATE QUANTITY FREEGOODS -------------------- ---------- ---------- ---------- ---------- ----------------------- ----------- ---------- PO216-3077 5300 82-00010 塑胶A TR2037147 2006-09-22 00:00:00.000 5300 N PO216-3185 10200 82-00010 塑胶A TR2037750 2006-10-13 00:00:00.000 10200 N PO216-3588 5500 82-00010 塑胶A TR2038418 2006-11-02 00:00:00.000 2700 N NULL TR2038495 2006-11-04 00:00:00.000 660 N NULL TR2038494 2006-11-09 00:00:00.000 2140 N(5 行受影响)*/楼主,记得结贴哦
PO216-3077 5300 82-00010 塑胶A TR2037147 2006-9-22 5300 N
PO216-3185 10200 82-00010 塑胶A TR2037750 2006-10-13 10200 N
PO216-3588 5500 82-00010 塑胶A TR2038495 2006-11-04 660 N
TR2038418 2006-11-02 2700 N
TR2038494 2006-11-09 2140 N
drop table tb
Go
Create table tb([REF_CODE] nvarchar(10),[QTY] int,[ITEM_CODE] nvarchar(8),[DES] nvarchar(3),[TRX_CODE] nvarchar(9),[INDATE] Datetime,[QUANTITY] int,[FREEGOODS] nvarchar(1))
Insert tb
select N'PO216-3077',5300,N'82-00010',N'塑胶A',N'TR2037147','2006-9-22',5300,N'N' union all
select N'PO216-3185',10200,N'82-00010',N'塑胶A',N'TR2037750','2006-10-13',10200,N'N' union all
select N'PO216-3588',5500,N'82-00010',N'塑胶A',N'TR2038495','2006-11-04',660,N'N' union all
select N'PO216-3588',5500,N'82-00010',N'塑胶A',N'TR2038418','2006-11-02',2700,N'N' union all
select N'PO216-3588',5500,N'82-00010',N'塑胶A',N'TR2038494','2006-11-09',2140,N'N'
Go
--2000加个自增列
alter table tb
add id int identity
go
select [REF_CODE]=case when exists(select 1
from tb
where [REF_CODE]=t.[REF_CODE] and id>t.id)
then '' else [REF_CODE] end,
[QTY]=case when exists(select 1
from tb
where [REF_CODE]=t.[REF_CODE] and id>t.id)
then null else [QTY] end,
[ITEM_CODE],
[DES],
[TRX_CODE],
[INDATE] ,
[QUANTITY],
[FREEGOODS]
from tb t
go
alter table tb
drop column ID
go
---2005
;with tt
as
(select ID=row_number()over(order by getdate()),
* from tb)
select [REF_CODE]=case when exists(select 1
from tt
where [REF_CODE]=t.[REF_CODE] and id>t.id)
then '' else [REF_CODE] end,
[QTY]=case when exists(select 1
from tt
where [REF_CODE]=t.[REF_CODE] and id>t.id)
then null else [QTY] end,
[ITEM_CODE],
[DES],
[TRX_CODE],
[INDATE] ,
[QUANTITY],
[FREEGOODS]
from tt t
go
/*
REF_CODE QTY ITEM_CODE DES TRX_CODE INDATE QUANTITY FREEGOODS
---------- ----------- --------- ---- --------- ----------------------- ----------- ---------
PO216-3077 5300 82-00010 塑胶A TR2037147 2006-09-22 00:00:00.000 5300 N
PO216-3185 10200 82-00010 塑胶A TR2037750 2006-10-13 00:00:00.000 10200 N
NULL 82-00010 塑胶A TR2038495 2006-11-04 00:00:00.000 660 N
NULL 82-00010 塑胶A TR2038418 2006-11-02 00:00:00.000 2700 N
PO216-3588 5500 82-00010 塑胶A TR2038494 2006-11-09 00:00:00.000 2140 N
*/
DES
自己照写吧
create table tb(REF_CODE nvarchar(20),QTY nvarchar(10),ITEM_CODE nvarchar(10),[DES] nvarchar(10),TRX_CODE nvarchar(10),INDATE datetime,QUANTITY int,FREEGOODS nvarchar(10))
insert into tb select 'PO216-3077','5300','82-00010','塑胶A','TR2037147','2006-9-22',5300,'N'
insert into tb select 'PO216-3185','10200','82-00010','塑胶A','TR2037750','2006-10-13',10200,'N'
insert into tb select 'PO216-3588','5500','82-00010','塑胶A','TR2038495','2006-11-04',660,'N'
insert into tb select 'PO216-3588','5500','82-00010','塑胶A','TR2038418','2006-11-02',2700,'N'
insert into tb select 'PO216-3588','5500','82-00010','塑胶A','TR2038494','2006-11-09',2140,'N'
go
select (case when ranknum=1 then REF_CODE else '' end) as REF_CODE,
(case when ranknum=1 then QTY else '' end) as QTY,
(case when ranknum=1 then ITEM_CODE else '' end) as ITEM_CODE,
[DES],TRX_CODE,INDATE,QUANTITY,FREEGOODS
FROM(
select rank() over(partition by REF_CODE order by QUANTITY)as ranknum,* from tb
)T
go
drop table tb
/*
REF_CODE QTY ITEM_CODE DES TRX_CODE INDATE QUANTITY FREEGOODS
-------------------- ---------- ---------- ---------- ---------- ----------------------- ----------- ----------
PO216-3077 5300 82-00010 塑胶A TR2037147 2006-09-22 00:00:00.000 5300 N
PO216-3185 10200 82-00010 塑胶A TR2037750 2006-10-13 00:00:00.000 10200 N
PO216-3588 5500 82-00010 塑胶A TR2038495 2006-11-04 00:00:00.000 660 N
塑胶A TR2038494 2006-11-09 00:00:00.000 2140 N
塑胶A TR2038418 2006-11-02 00:00:00.000 2700 N(5 行受影响)*/
insert into tb select 'PO216-3077','5300','82-00010','塑胶A','TR2037147','2006-9-22',5300,'N'
insert into tb select 'PO216-3185','10200','82-00010','塑胶A','TR2037750','2006-10-13',10200,'N'
insert into tb select 'PO216-3588','5500','82-00010','塑胶A','TR2038495','2006-11-04',660,'N'
insert into tb select 'PO216-3588','5500','82-00010','塑胶A','TR2038418','2006-11-02',2700,'N'
insert into tb select 'PO216-3588','5500','82-00010','塑胶A','TR2038494','2006-11-09',2140,'N'
go
select (case when ranknum=1 then REF_CODE else '' end) as REF_CODE,
(case when ranknum=1 then QTY else '' end) as QTY,
(case when ranknum=1 then ITEM_CODE else '' end) as ITEM_CODE,
(case when ranknum=1 then [DES] else '' end) as [DES],
TRX_CODE,INDATE,QUANTITY,FREEGOODS
FROM(
select rank() over(partition by REF_CODE order by QUANTITY)as ranknum,* from tb
)T
go
drop table tb
/*
REF_CODE QTY ITEM_CODE DES TRX_CODE INDATE QUANTITY FREEGOODS
-------------------- ---------- ---------- ---------- ---------- ----------------------- ----------- ----------
PO216-3077 5300 82-00010 塑胶A TR2037147 2006-09-22 00:00:00.000 5300 N
PO216-3185 10200 82-00010 塑胶A TR2037750 2006-10-13 00:00:00.000 10200 N
PO216-3588 5500 82-00010 塑胶A TR2038495 2006-11-04 00:00:00.000 660 N
TR2038494 2006-11-09 00:00:00.000 2140 N
TR2038418 2006-11-02 00:00:00.000 2700 N(5 行受影响)
/*
--借用#6演示数据
SELECT
REF_CODE = CASE WHEN b.REF_CODE IS NULL THEN a.REF_CODE ELSE '' END,
QTY = CASE WHEN b.REF_CODE IS NULL THEN a.QTY ELSE NULL END,
ITEM_CODE = CASE WHEN b.REF_CODE IS NULL THEN a.ITEM_CODE ELSE '' END,
DES = CASE WHEN b.REF_CODE IS NULL THEN a.DES ELSE '' END,
a.TRX_CODE, a.INDATE, a.QUANTITY, a.FREEGOODS
FROM tb a
LEFT JOIN tb b ON a.REF_CODE = b.REF_CODE and a.QTY = b.QTY and a.INDATE > b.INDATE
--WHERE a......bla bla bla
GROUP BY a.REF_CODE, b.REF_CODE, a.QTY, a.ITEM_CODE, a.DES, a.TRX_CODE, a.INDATE, a.QUANTITY, a.FREEGOODS
ORDER BY a.REF_CODE, a.QTY, a.INDATE/*
REF_CODE QTY ITEM_CODE DES TRX_CODE INDATE QUANTITY FREEGOODS
-------------------- ---------- ---------- ---------- ---------- ----------------------- ----------- ----------
PO216-3077 5300 82-00010 塑胶A TR2037147 2006-09-22 00:00:00.000 5300 N
PO216-3185 10200 82-00010 塑胶A TR2037750 2006-10-13 00:00:00.000 10200 N
PO216-3588 5500 82-00010 塑胶A TR2038418 2006-11-02 00:00:00.000 2700 N
NULL TR2038495 2006-11-04 00:00:00.000 660 N
NULL TR2038494 2006-11-09 00:00:00.000 2140 N(5 行受影响)*/楼主,记得结贴哦