该问题有点难度,请进
有两表,其中A表里有该字段制造番号
ZH3061211338-ZH3061211367
B表里有数据为
ZH3061211338
ZH3061211458
ZH3061212553
ZH3061212575
ZH3061212850
ZH3061212862
ZH3061212863
ZH3061212864
ZH3061212873
..........
现在我想从A表的数据ZH3061211338到ZH3061211367(请注意可能是从338到367,也可能是0-10)的数据在B表有多少存在,不存在的为哪些?


------解决方案--------------------
create table A(begNo varchar(20), endNo varchar(20))
insert A select 'ZH3061211338 ', 'ZH3061211367 '
go
create table B(No varchar(20))
insert B select 'ZH3061211338 '
union all select 'ZH3061211458 '
union all select 'ZH3061212553 '
union all select 'ZH3061212575 '
union all select 'ZH3061212850 '
union all select 'ZH3061212862 '
union all select 'ZH3061212863 '
union all select 'ZH3061212864 '
union all select 'ZH3061212873 '
go
declare @begNo varchar(20), @endNo varchar(20)
declare @begInt int, @endInt int
declare @T table(No varchar(20))

declare cur cursor local
for
select * from A
open cur
fetch next from cur into @begNo, @endNo

while @@fetch_status=0
begin
select @begInt=right(@begNo, 3), @endInt=right(@endNo, 3)
while @begInt <=@endInt
begin
insert @T select left(@begNo, 9)+rtrim(@begInt)
set @begInt=@begInt+1
end


fetch next from cur into @begNo, @endNo
end

close cur
deallocate cur

--存在
select * from @T as A
left join B on A.No=B.No
where B.No is not null

--不存在
select * from @T as A
left join B on A.No=B.No
where B.No is null
------解决方案--------------------
--A表中左侧数字
CREATE FUNCTION FUNL(@paraA VARCHAR(50))
RETURNS DECIMAL
AS
BEGIN
DECLARE @STR VARCHAR(50)
DECLARE @A DECIMAL
SET @STR =LEFT(@paraA,CHARINDEX( '- ',@paraA)-1)
SET @A=CAST(REVERSE(LEFT(REVERSE(@STR),PATINDEX( '%[^0-9]% ',REVERSE(@STR))-1)) AS DECIMAL)
RETURN (@A)
END


--A表中右侧数字
CREATE FUNCTION FUNR(@paraA VARCHAR(50))
RETURNS DECIMAL
AS
BEGIN
DECLARE @STR VARCHAR(50)
DECLARE @B DECIMAL
SET @STR =RIGHT(@paraA,LEN(@paraA)-CHARINDEX( '- ',@paraA))
SET @B=CAST(REVERSE(LEFT(REVERSE(@STR),PATINDEX( '%[^0-9]% ',REVERSE(@STR))-1)) AS DECIMAL)
RETURN (@B)
END
--字符串中数字
CREATE FUNCTION FUNSTR(@paraA VARCHAR(50))
RETURNS DECIMAL
AS
BEGIN
DECLARE @C DECIMAL
SELECT @C=CAST(REVERSE(LEFT(REVERSE(@paraA),PATINDEX( '%[^0-9]% ',REVERSE(@paraA))-1)) AS DECIMAL)
RETURN (@C)
END
CREATE TABLE A
(
COLA VARCHAR(50)
)
INSERT INTO A
SELECT 'ZH3061211338-ZH3061211367 '
CREATE TABLE B
(
COLB VARCHAR(50)
)
INSERT INTO B
SELECT 'ZH3061211338 ' UNION ALL
SELECT 'ZH3061211458 ' UNION ALL
SELECT 'ZH3061212553 ' UNION ALL
SELECT 'ZH3061212575 ' UNION ALL
SELECT 'ZH3061212850 ' UNION ALL
SELECT 'ZH3061212862 ' UNION ALL
SELECT 'ZH3061212863 ' UNION ALL
SELECT 'ZH3061212864 ' UNION ALL
SELECT 'ZH3061212873 '
--符合
SELECT B.* FROM A,B WHERE dbo.FUNSTR(B.COLB) BETWEEN dbo.FUNL(A.COLA) AND dbo.FUNR(A.COLA)
--结果
/*
COLB
--------------------------------------------------
ZH3061211338

(1 行受影响)

*/
-