疑难问题

Question

如下例所示：
ID BILL_DATE QUAN
0001 2010-02-03 4000
0002 2010-02-03 200
0004 2010-02-04 204
0005 2010-02-07 333
0007 2010-02-09 123
0010 2010-02-27 2034
0009 2010-02-27 2341   
0013 2010-02-21 222
现在我要的结果是这样的：
我要把2月份所有的断号查询出来：
ID
0003
0006
0008 
0011
0012
sql server 2000   和sql server2005 都要一份
 
 

Answer 1

declare @minValue int 
declare @maxValue int
declare @uncount varchar(2000)
set @minValue = 1--查询的最小值
set @maxValue = 10--查询的最大值
set @uncount = ''
--drop table #t
create table #t(a int,b varchar(20),c float)
insert into #t
select 1,'1',1.2 union all
select 2,'2',1.3 union all
select 4,'4',1.4 union all
select 5,'5',1.5 union all
select 7,'7',1.7 --循环查找，如果不存在，则记录编号while (@minValue <= @maxValue)
begin
    if not exists(select * from #t where a = @minValue)
    begin
        set @uncount = @uncount + cast(@minValue as varchar) +','
    end
    set @minValue = @minValue + 1
end
if(len(@uncount)>0)
begin
    set @uncount = substring(@uncount,1,len(@uncount)-1)
end
select @uncount as uncount
drop table #t-------运行结果--------
uncount
3,6,8,9,10
--生成已用编号分布字符串的函数    
CREATE FUNCTION f_GetStrSeries(@col1 varchar(10))   
RETURNS varchar(8000)   
AS   
BEGIN   
    DECLARE @re varchar(8000),@pid int   
    SELECT @re='',@pid=-1   
    SELECT @re=CASE    
            WHEN col2=@pid+1 THEN @re   
            ELSE @re   
                +CASE    
                    WHEN RIGHT(@re,CHARINDEX(',',REVERSE(@re)+',')-1)=@pid THEN ''   
                    ELSE CAST(-@pid as varchar)   
                END   
                +','+CAST(col2 as varchar)    
            END,   
        @pid=col2   
    FROM tb   
    WHERE col1=@col1   
    ORDER BY col2   
    RETURN(STUFF(@re,1,2,'')   
        +CASE    
            WHEN RIGHT(@re,CHARINDEX(',',REVERSE(@re)+',')-1)=@pid THEN ''   
            ELSE CAST(-@pid as varchar)   
        END)   
END   
GO   
   
--生成缺号分布字符串的函数    
CREATE FUNCTION f_GetStrNSeries(@col1 varchar(10))   
RETURNS VARCHAR(8000)   
AS   
BEGIN   
    DECLARE @re varchar(8000),@pid int   
    SELECT @re='',@pid=0   
    SELECT @re=CASE    
            WHEN col2=@pid+1 THEN @re   
            ELSE @re+','+CAST(@pid+1 as varchar)   
                +CASE    
                    WHEN @pid+1=col2-1 THEN ''   
                    ELSE CAST(1-col2 as varchar)   
                END   
            END,   
        @pid=col2   
    FROM tb   
    WHERE col1=@col1   
    ORDER BY col2   
    RETURN(STUFF(@re,1,1,''))   
END   
GO   
   
--调用测试    
--测试数据    
CREATE TABLE tb(col1 varchar(10),col2 int)   
INSERT tb SELECT 'a',2   
UNION ALL SELECT 'a',3   
UNION ALL SELECT 'a',5   
UNION ALL SELECT 'a',8   
UNION ALL SELECT 'a',9   
UNION ALL SELECT 'b',1   
UNION ALL SELECT 'b',5   
UNION ALL SELECT 'b',6   
UNION ALL SELECT 'b',7   
   
--查询    
SELECT col1,   
    col2_Series=dbo.f_GetStrSeries(col1),   
    col2_Series=dbo.f_GetStrNSeries(col1)   
FROM tb   
GROUP BY col1   
/*--结果    
col1       col2_Series       col2_Series    
-------------- ------------------------ --------------    
a          2-3,5,8-9        1,4,6-7   
b          1,5-7           2-4   
--*/   --生成已用编号分布字符串的函数 
CREATE FUNCTION f_GetStrSeries(@col1 varchar(10)) 
RETURNS varchar(8000) 
AS 
BEGIN 
    DECLARE @re varchar(8000),@pid int 
    SELECT @re='',@pid=-1 
    SELECT @re=CASE  
            WHEN col2=@pid+1 THEN @re 
            ELSE @re 
                +CASE  
                    WHEN RIGHT(@re,CHARINDEX(',',REVERSE(@re)+',')-1)=@pid THEN '' 
                    ELSE CAST(-@pid as varchar) 
                END 
                +','+CAST(col2 as varchar)  
            END, 
        @pid=col2 
    FROM tb 
    WHERE col1=@col1 
    ORDER BY col2 
    RETURN(STUFF(@re,1,2,'') 
        +CASE  
            WHEN RIGHT(@re,CHARINDEX(',',REVERSE(@re)+',')-1)=@pid THEN '' 
            ELSE CAST(-@pid as varchar) 
        END) 
END 
GO 
 
--生成缺号分布字符串的函数 
CREATE FUNCTION f_GetStrNSeries(@col1 varchar(10)) 
RETURNS VARCHAR(8000) 
AS 
BEGIN 
    DECLARE @re varchar(8000),@pid int 
    SELECT @re='',@pid=0 
    SELECT @re=CASE  
            WHEN col2=@pid+1 THEN @re 
            ELSE @re+','+CAST(@pid+1 as varchar) 
                +CASE  
                    WHEN @pid+1=col2-1 THEN '' 
                    ELSE CAST(1-col2 as varchar) 
                END 
            END, 
        @pid=col2 
    FROM tb 
    WHERE col1=@col1 
    ORDER BY col2 
    RETURN(STUFF(@re,1,1,'')) 
END 
GO 
 
--调用测试 
--测试数据 
CREATE TABLE tb(col1 varchar(10),col2 int) 
INSERT tb SELECT 'a',2 
UNION ALL SELECT 'a',3 
UNION ALL SELECT 'a',5 
UNION ALL SELECT 'a',8 
UNION ALL SELECT 'a',9 
UNION ALL SELECT 'b',1 
UNION ALL SELECT 'b',5 
UNION ALL SELECT 'b',6 
UNION ALL SELECT 'b',7 
 
--查询 
SELECT col1, 
    col2_Series=dbo.f_GetStrSeries(col1), 
    col2_Series=dbo.f_GetStrNSeries(col1) 
FROM tb 
GROUP BY col1 
/*--结果 
col1       col2_Series       col2_Series  
-------------- ------------------------ -------------- 
a          2-3,5,8-9        1,4,6-7 
b          1,5-7           2-4 
--*/

Answer 2

--> 测试数据：#
if object_id('tempdb.dbo.#') is not null drop table #
create table #(ID varchar(8), BILL_DATE datetime, QUAN int)
insert into #
select '0001', '2010-02-03', 4000 union all
select '0002', '2010-02-03', 200 union all
select '0004', '2010-02-04', 204 union all
select '0005', '2010-02-07', 333 union all
select '0007', '2010-02-09', 123 union all
select '0010', '2010-02-27', 2034 union all
select '0009', '2010-02-27', 2341 union all
select '0013', '2010-02-21', 222-- 只有这一份，2000/2005都要关联数字表
select right(10000+a.number,4)ID from master..spt_values a left join # b on a.number=b.ID and month(b.BILL_DATE)=2
where a.type='P' and a.number between 1 and (select max(ID) from # where month(BILL_DATE)=2) and b.ID is null
/*
ID
--------
0003
0006
0008
0011
0012
*/

Answer 3

--sql 2000
create table tb(ID varchar(10),BILL_DATE datetime,QUAN int)
insert into tb values('0001', '2010-02-03', 4000)
insert into tb values('0002', '2010-02-03', 200)
insert into tb values('0004', '2010-02-04', 204)
insert into tb values('0005', '2010-02-07', 333)
insert into tb values('0007', '2010-02-09', 123)
insert into tb values('0010', '2010-02-27', 2034)
insert into tb values('0009', '2010-02-27', 2341 )  
insert into tb values('0013', '2010-02-21', 222)
goselect t1.* from 
(
select right('000'+cast(m.min_id + n.number as varchar),4) id from
(select min(ID) min_id, max(ID) max_id from tb where datepart(mm,bill_date) = 2) m,
master..spt_values  n 
where n.type='p' and cast(m.min_id as int) + n.number <= cast(m.max_id as int)
) t1 where id not in (select id from tb where datepart(mm,bill_date) = 2)drop table tb/*
id       
-------- 
0003
0006
0008
0011
0012（所影响的行数为 5 行）
*/