如何找出缺少的單據編號?能否用一個Select語句實現?
單據編號(varchar(10))規則:前綴P+年+月+日+流水號(3位),
如何找出缺少的單據編號?能否用一個Select語句實現?
比如有以下數據:
P030101001
P030101002
P030101004
P030102001
P030102004
P030102005
則缺少的單據編號是:
P030101003
P030102002
P030102003
下面只是找到卻多少張,但是明細如何找到,請高手指點。
select *,convert(int,right(maxbh,3))-djsl as diff from
(
Select Left(tra_djbh1,7) as ymd,Count(distinct tra_djbh1) as djsl,Max(tra_djbh1) as maxbh
From tra_mstr
Where Left(tra_djbh1,7) Between 'T030101' and 'T030630'
Group by Left(tra_djbh1,7)
) as tmp
Where ymd+Replace(str(djsl,3),' ','0')<>maxbh