BranchCode ItemCode Cipher PDA_NO
3 4571136740161 1116 236
3 4571136740598 1116 236
3 4582247360490 1214 233
3 4582247360506 1214 236
3 4901550210016 0 236
3 4901550262480 0 236
3 4901577133992 0 236
3 4901620139834 0 236
3 4902130882661 214 232
3 4902206104895 0 232
3 4902206105618 0 232
3 4902402534090 0 236
3 4902402534120 0 236
3 4902410425281 1114 236
根据BranchCode,PDA_NO排序,把BranchCode,PDA_NO相等,且Cipher=0的上一条数据开始的数据抽出来,最后想要的数据是这几条数据
BranchCode ItemCode Cipher PDA_NO
3 4582247360506 1214 236
3 4901550210016 0 236
3 4901550262480 0 236
3 4901577133992 0 236
3 4901620139834 0 236
3 4902130882661 214 232
3 4902206104895 0 232
3 4902206105618 0 232谢谢
3 4571136740161 1116 236
3 4571136740598 1116 236
3 4582247360490 1214 233
3 4582247360506 1214 236
3 4901550210016 0 236
3 4901550262480 0 236
3 4901577133992 0 236
3 4901620139834 0 236
3 4902130882661 214 232
3 4902206104895 0 232
3 4902206105618 0 232
3 4902402534090 0 236
3 4902402534120 0 236
3 4902410425281 1114 236
根据BranchCode,PDA_NO排序,把BranchCode,PDA_NO相等,且Cipher=0的上一条数据开始的数据抽出来,最后想要的数据是这几条数据
BranchCode ItemCode Cipher PDA_NO
3 4582247360506 1214 236
3 4901550210016 0 236
3 4901550262480 0 236
3 4901577133992 0 236
3 4901620139834 0 236
3 4902130882661 214 232
3 4902206104895 0 232
3 4902206105618 0 232谢谢
from tablename a left join tablename b
on b.ItemCode=(select min(ItemCode) from tablename where ItemCode>a.ItemCode)
left join tablename c
on b.ItemCode=(select max(ItemCode) from tablename where ItemCode<a.ItemCode)
where a.Cipher=0 and c.Cipher<>0 and a.PDA_NO=c.PDA_NO and a.BranchCode=c.BranchCode
or a.Cipher<>0 and b.Cipher=0 and a.PDA_NO=b.PDA_NO and a.BranchCode=b.BranchCode
or a.Cipher=0 and c.Cipher=0 and a.PDA_NO=c.PDA_NO and a.BranchCode=c.BranchCode and not exists (select 1 from tablename d where ItemCode>(select max(ItemCode) from tablename PDA_NO=a.PDA_NO and BranchCode=a.BranchCode and ItemCode<a.ItemCode and Cipher<>0) and ItemCode<a.ItemCode and (PDA_NO<>a.PDA_NO or BranchCode<>a.BranchCode
)
)
--未测试,不知道对不对
BranchCode int,
ItemCode varchar(20),
Cipher int,
PDA_NO int
)
insert @t select
3, '4571136740161', 1116, 236
union all select
3, '4571136740598', 1116, 236
union all select
3, '4582247360490', 1214, 233
union all select
3, '4582247360506', 1214, 236
union all select
3, '4901550210016', 0, 236
union all select
3, '4901550262480', 0, 236
union all select
3, '4901577133992', 0, 236
union all select
3, '4901620139834', 0, 236
union all select
3, '4902130882661', 214, 232
union all select
3, '4902206104895', 0, 232
union all select
3, '4902206105618', 0, 232
union all select
3, '4902402534090', 0, 236
union all select
3, '4902402534120', 0, 236
union all select
3, '4902410425281', 1114, 236select a.*
from @t a left join @t b
on b.ItemCode=(select min(ItemCode) from @t where ItemCode>a.ItemCode)
left join @t c
on c.ItemCode=(select max(ItemCode) from @t where ItemCode<a.ItemCode)
where a.Cipher=0 and c.Cipher<>0 and a.PDA_NO=c.PDA_NO and a.BranchCode=c.BranchCode
or a.Cipher<>0 and b.Cipher=0 and a.PDA_NO=b.PDA_NO and a.BranchCode=b.BranchCode
or a.Cipher=0 and c.Cipher=0 and a.PDA_NO=c.PDA_NO and a.BranchCode=c.BranchCode
and not exists (
select 1 from @t d
where ItemCode>(
select max(ItemCode)
from @t
where PDA_NO=a.PDA_NO and BranchCode=a.BranchCode and ItemCode<a.ItemCode
and Cipher<>0
) and ItemCode<a.ItemCode and (PDA_NO<>a.PDA_NO or BranchCode<>a.BranchCode
)
)--结果
BranchCode ItemCode Cipher PDA_NO
----------- -------------------- ----------- -----------
3 4582247360506 1214 236
3 4901550210016 0 236
3 4901550262480 0 236
3 4901577133992 0 236
3 4901620139834 0 236
3 4902130882661 214 232
3 4902206104895 0 232
3 4902206105618 0 232(所影响的行数为 8 行)
CREATE TABLE tb
(
BranchCode INT ,
ItemCode CHAR(13),
Cipher INT,
PDA_NO INT
)
INSERT INTO tb
SELECT 3,'4571136740161',1116,236 UNION ALL
SELECT 3,'4571136740598',1116,236 UNION ALL
SELECT 3,'4582247360490',1214,233 UNION ALL
SELECT 3,'4582247360506',1214,236 UNION ALL
SELECT 3,'4901550210016',0,236 UNION ALL
SELECT 3,'4901550262480',0,236 UNION ALL
SELECT 3,'4901577133992',0,236 UNION ALL
SELECT 3,'4901620139834',0,236 UNION ALL
SELECT 3,'4902130882661',214,232 UNION ALL
SELECT 3,'4902206104895',0,232 UNION ALL
SELECT 3,'4902206105618',0,232 UNION ALL
SELECT 3,'4902402534090',0,236 UNION ALL
SELECT 3,'4902402534120',0,236 UNION ALL
SELECT 3,'4902410425281',1114,236
CREATE TABLE #
(
BranchCode INT ,
ItemCode CHAR(13),
Cipher INT,
PDA_NO INT
)
GO
DECLARE @BranchCode INT
DECLARE @ItemCode CHAR(13)
DECLARE @Cipher INT
DECLARE @PDA_NO INT
DECLARE @OLDBranchCode INT
DECLARE @OLDItemCode CHAR(13)
DECLARE @OLDCipher INT
DECLARE @OLDPDA_NO INT
--游标开始
DECLARE CURTEST CURSOR FOR
SELECT BranchCode,ItemCode,Cipher,PDA_NO FROM tb
OPEN CURTEST
FETCH CURTEST INTO @BranchCode,@ItemCode,@Cipher,@PDA_NO
WHILE @@FETCH_STATUS = 0
BEGIN
IF @BranchCode=@OLDBranchCode AND @PDA_NO=@OLDPDA_NO AND @Cipher=0
BEGIN
INSERT INTO # SELECT @OLDBranchCode,@OLDItemCode,@OLDCipher,@OLDPDA_NO
END
SELECT @OLDBranchCode=@BranchCode,@OLDItemCode=@ItemCode,@OLDCipher=@Cipher,@OLDPDA_NO=@PDA_NO
FETCH CURTEST INTO @BranchCode,@ItemCode,@Cipher,@PDA_NO
ENDCLOSE CURTEST
DEALLOCATE CURTEST
SELECT * FROM #
DROP TABLE #
--结果
BranchCode ItemCode Cipher PDA_NO
----------- ------------- ----------- -----------
3 4582247360506 1214 236
3 4901550210016 0 236
3 4901550262480 0 236
3 4901577133992 0 236
3 4902130882661 214 232
3 4902206104895 0 232
3 4902402534090 0 236(7 行受影响)
BranchCode ItemCode Cipher PDA_NO OrderDate
3 4522646193084 1314 233 2007/5/14
3 4522646193091 1314 233 2007/5/14
3 4528141500099 1114 233 2007/5/14
3 4528141500105 1114 233 2007/5/14
3 4560275500729 1116 236 2007/5/12
3 4571136740161 1116 236 2007/5/12
3 4571136740161 1116 236 2007/5/12
3 4571136740598 1116 236 2007/5/12
3 4582247360490 1214 233 2007/5/14
3 4582247360506 1214 236 2007/5/14
3 4901550210016 0 236 2007/5/12
3 4901550262480 0 236 2007/5/12
3 4901577133992 0 236 2007/5/15
3 4901620139834 0 236 2007/5/15
3 4902130882661 214 232 2007/5/12
3 4902206104895 0 232 2007/5/12
3 4902206105618 0 232 2007/5/12
3 4902402534090 0 232 2007/5/15
3 4902402534120 0 236 2007/5/15
就是OrderDate必须相等,谢谢