测试数据是没有问题的, 我放到ERP上就有问题了,查询某个ITEM_CODE时,有些显数据能正确显示,有些数据显示错误,这个报表我发了好几个帖子了,请大侠指点。
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'
insert into tb select 'PO216-3199','10000','82-00020','塑胶B','TR2037850','2006-10-23',2700,'N'
insert into tb select 'PO216-3199','10000','82-00020','塑胶B','TR2037887','2006-10-18',2140,'N'
goSELECT
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
原始数据图片:
查询后图片:
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'
insert into tb select 'PO216-3199','10000','82-00020','塑胶B','TR2037850','2006-10-23',2700,'N'
insert into tb select 'PO216-3199','10000','82-00020','塑胶B','TR2037887','2006-10-18',2140,'N'
goSELECT
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
原始数据图片:
查询后图片:
insert into tb select 'PO116-0017','500','21-00001-00020','塑胶A','GN20-3727','2006-4-21',100,'N'
insert into tb select 'PO116-0017','500','21-00001-00020','塑胶A','GN20-3722','2006-4-18',300,'N'
insert into tb select 'PO116-0017','500','21-00001-00020','塑胶A','GN20-3753','2006-4-22',100,'N'
insert into tb select 'PO116-0060','250','21-00001-00020','塑胶A','GN20-3957','2006-8-23',75,'N'
insert into tb select 'PO116-0060','250','21-00001-00020','塑胶A','GN20-3958','2006-8-22',75,'N'
insert into tb select 'PO116-0060','250','21-00001-00020','塑胶A','GN20-3962','2006-8-25',25,'N'
insert into tb select 'PO116-0060','250','21-00001-00020','塑胶A','GN20-3965','2006-8-12',75,'N'
insert into tb select 'PO116-0060','250','21-00001-00020','塑胶A','GN20-3952','2006-8-27',25,'N'
insert into tb select 'PO116-0084','750','21-00001-00020','塑胶B','GN20-4108','2006-10-23',750,'N'
insert into tb select 'PO117-0033','1000','21-00001-00020','塑胶B','GN20-4432','2006-10-18',1000,'N'
go
SELECT
REF_CODE = CASE WHEN b.REF_CODE IS NULL THEN a.REF_CODE ELSE '' END,
QTY = ISNULL((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.ITEM_CODE='21-00001-00020'
GROUP BY a.REF_CODE, b.REF_CODE, a.QTY, a.ITEM_CODE, a.DES, a.TRX_CODE, a.INDATE, a.QUANTITY, a.FREEGOODS
go
drop table tb
/*
REF_CODE QTY ITEM_CODE DES TRX_CODE INDATE QUANTITY FREEGOODS
-------------------- ---------- --------------- ---------- ---------- ----------------------- ----------- ----------
PO116-0017 500 21-00001-00020 塑胶A GN20-3722 2006-04-18 00:00:00.000 300 N
GN20-3727 2006-04-21 00:00:00.000 100 N
GN20-3753 2006-04-22 00:00:00.000 100 N
PO116-0060 250 21-00001-00020 塑胶A GN20-3965 2006-08-12 00:00:00.000 75 N
GN20-3952 2006-08-27 00:00:00.000 25 N
GN20-3957 2006-08-23 00:00:00.000 75 N
GN20-3958 2006-08-22 00:00:00.000 75 N
GN20-3962 2006-08-25 00:00:00.000 25 N
PO116-0084 750 21-00001-00020 塑胶B GN20-4108 2006-10-23 00:00:00.000 750 N
PO117-0033 1000 21-00001-00020 塑胶B GN20-4432 2006-10-18 00:00:00.000 1000 N(10 行受影响)*/
么有问题啊!