22,210
社区成员
发帖
与我相关
我的任务
分享
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
PO216-3588 5500 82-00010 塑胶A TR2038418 2006-11-02 2700 N
PO216-3588 5500 82-00010 塑胶A TR2038494 2006-11-09 2140 N
......
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
82-00010 塑胶A TR2038418 2006-11-02 2700 N
82-00010 塑胶A TR2038494 2006-11-09 2140 N
......
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 行受影响)
/*
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 行受影响)
*/
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
*/
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
0 TR2038418 2006-11-02 00:00:00.000 2700 N
0 TR2038494 2006-11-09 00:00:00.000 2140 N
WITH T AS(SELECT *,ID=ROW_NUMBER() OVER(PARTITION BY REF_CODE ORDER BY QTY) FROM A)
SELECT REF_CODE=CASE WHEN ID=1 THEN REF_CODE ELSE ''END,
QTY=CASE WHEN ID=1 THEN QTY ELSE '' END,
ITEM_CODE=CASE WHEN ID=1 THEN ITEM_CODE ELSE '' END,
DES=CASE WHEN ID=1 THEN DES ELSE '' END,TRX_CODE,INDATE,QUANTITY,FREEGOODS FROM T
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
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
0 82-00010 塑胶A TR2038418 2006-11-02 00:00:00.000 2700 N
0 82-00010 塑胶A TR2038494 2006-11-09 00:00:00.000 2140 N
CREATE TABLE A(REF_CODE VARCHAR(10),QTY INT,ITEM_CODE VARCHAR(10),
DES NVARCHAR(10),TRX_CODE VARCHAR(10),INDATE DATETIME,QUANTITY INT,FREEGOODS VARCHAR(2))
INSERT INTO A
SELECT 'PO216-3077' , 5300 , '82-00010' , N'塑胶A' , 'TR2037147' , '2006-9-22' , 5300 , 'N' UNION ALL
SELECT 'PO216-3185' , 10200 , '82-00010' , N'塑胶A' , 'TR2037750' , '2006-10-13' , 10200 , 'N' UNION ALL
SELECT 'PO216-3588' , 5500 , '82-00010' , N'塑胶A' , 'TR2038495' , '2006-11-04' , 660 , 'N' UNION ALL
SELECT 'PO216-3588' , 5500 , '82-00010' , N'塑胶A' , 'TR2038418' , '2006-11-02' , 2700 , 'N' UNION ALL
SELECT 'PO216-3588' , 5500 , '82-00010' , N'塑胶A', 'TR2038494' , '2006-11-09' , 2140 , 'N'
GO
WITH T AS(SELECT *,ID=ROW_NUMBER() OVER(PARTITION BY REF_CODE ORDER BY QTY) FROM A)
SELECT REF_CODE=CASE WHEN ID=1 THEN REF_CODE ELSE ''END,
QTY=CASE WHEN ID=1 THEN QTY ELSE '' END,ITEM_CODE,DES,TRX_CODE,INDATE,QUANTITY,FREEGOODS FROM T