一个较难的SQL查询语句

Question

------------------------------------------------------------------------
-- Author:  happyflystone  
-- Date  :  2009-05-07 16:58:29
-- Ver:     Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86) 
--       Apr 14 2006 01:12:25 
--       Copyright (c) 1988-2005 Microsoft Corporation
--       Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 4)
--      
-------------------------------------------------------------------------- Test Data: T1
IF OBJECT_ID('T1') IS NOT NULL 
    DROP TABLE T1
Go
CREATE TABLE T1(a NVARCHAR(11))
Go
INSERT INTO T1
 SELECT 'NDE09050001' UNION ALL
 SELECT 'NDE09050002' UNION ALL
 SELECT 'NDE09050005' UNION ALL
 SELECT 'NDE09050006' 
GO
--Start
SELECT 
'nde0905'+right('0000'+ltrim(number),4)
FROM
T1 b
right join master..spt_values a
on number = cast(right(a,4) as int)
where a.type = 'p' and number <= (select max(right(a,4)) from t1) and number >0
      and a is null
 
--Result:
/*
---------------
nde09050003
nde09050004
*/
--End 

Answer 1

我得建议是，先生成一个临时表 B ，数据是全的，
NDE09050001
NDE09050002
NDE09050003 
NDE09050004 
NDE09050005
NDE09050006然后2个表连接就可以了，或者select B.field from B where B.field not in (select A.field from A) 就是临时表中有， 你要查询的表中没有的。在临时表中生成连续的应该简单些。使用字符串连接 和 整数转字符串就可以。

Answer 2

--> Test Data: T
create  table tb1([col] varchar(11))
insert into tb1
select 'NDE09050001' union all
select 'NDE09050002' union all
select 'NDE09050005' union all
select 'NDE09050006' --select * from @T
--Code
declare @max int
select @max= max(cast(right(col,4) as int)) from tb1
exec ('select top '+@max+' id=identity(int,1,1) into # from syscolumns'
+'
select col= ''NDE0905''+right(ltrim(100000+b.id),4) from tb1 a right join # b on 
right(col,4)=right(ltrim(100000+b.id),4)
where col is null
drop table #')
--Drop
drop  table tb1
--Result
/*
col             
--------------- 
NDE09050003
NDE09050004
*/

Answer 3

我敢肯定，楼上的 MVP一定是数学牛人！堪比华多庚！

Answer 4

DECLARE @t TABLE (id VARCHAR(30))
INSERT INTO @t
SELECT 
'NDE09050001'  UNION ALL SELECT 
'NDE09050002' UNION ALL SELECT 
'NDE09050005' UNION ALL SELECT 
'NDE09050007' UNION ALL SELECT 
'NDE09050012' UNION ALL SELECT 
'NDE09050013' 
;
WITH tmp
AS
(
SELECT STUFF(id,1,3,'') as col1
FROM @t
)
,CTE1
AS
(
 SELECT  col1 + 1 AS start_range,
 (
SELECT MIN(col1) 
FROM tmp AS B
 WHERE B.col1 > A.col1
) - 1 AS end_range
FROM tmp AS A
WHERE NOT EXISTS
  (
SELECT * 
FROM tmp AS B
 WHERE B.col1 = A.col1 + 1)
  AND col1 < (SELECT MAX(col1) FROM tmp)
),
CTE2
AS
(
SELECT start_range,end_range FROM CTE1
UNION ALL
SELECT a.start_range+1,end_range
FROM  CTE2 a
WHERE a.start_range+1<=a.end_range
)
SELECT 'NDE'+RIGHT(REPLICATE('0',8)+start_range,8) as RefNo
FROM CTE2 
ORDER BY start_range

Answer 5

--帖结果.
DECLARE @t TABLE (id VARCHAR(30))
INSERT INTO @t
SELECT 
'NDE09050001'  UNION ALL SELECT 
'NDE09050002' UNION ALL SELECT 
'NDE09050005' UNION ALL SELECT 
'NDE09050007' UNION ALL SELECT 
'NDE09050012' UNION ALL SELECT 
'NDE09050013' 
;
WITH tmp
AS
(
SELECT STUFF(id,1,3,'') as col1
FROM @t
)
,CTE1
AS
(
 SELECT  col1 + 1 AS start_range,
 (
SELECT MIN(col1) 
FROM tmp AS B
 WHERE B.col1 > A.col1
) - 1 AS end_range
FROM tmp AS A
WHERE NOT EXISTS
  (
SELECT * 
FROM tmp AS B
 WHERE B.col1 = A.col1 + 1)
  AND col1 < (SELECT MAX(col1) FROM tmp)
),
CTE2
AS
(
SELECT start_range,end_range FROM CTE1
UNION ALL
SELECT a.start_range+1,end_range
FROM  CTE2 a
WHERE a.start_range+1<=a.end_range
)
SELECT 'NDE'+RIGHT(REPLICATE('0',8)+cast(start_range as varchar(30)),8) as RefNo
FROM CTE2 
ORDER BY start_range
/*
NDE09050003
NDE09050004
NDE09050006
NDE09050008
NDE09050009
NDE09050010
NDE09050011*/

Answer 6

DECLARE @t TABLE (id VARCHAR(30))
INSERT INTO @t
SELECT 
'NDE09050001'  UNION ALL SELECT 
'NDE09050002' UNION ALL SELECT 
'NDE09050005' UNION ALL SELECT 
'NDE09050006'  
select 'NDE'+cast(start_point as varchar),'NDE'+cast(end_point as varchar) from ( select cur+1 start_point,nxt-1  end_point from (select stuff(id,1,3,'') as cur,(select min(stuff(id,1,3,'')) from @t as b where stuff(id,1,3,'')>stuff(a.id,1,3,'')) as nxt from @t as a) as d
where cast(nxt as int)-cast(cur as int)>1 )e
--------------------------------- ---------------------------------
NDE9050003                        NDE9050004(1 行受影响)