22,298
社区成员
发帖
与我相关
我的任务
分享疑难问题
如下例所示：
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 都要一份
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 都要一份
...全文
请发表友善的回复…
发表回复
abuying 2010-10-25
- 打赏
- 举报
2#，4#正解！
dawugui 2010-10-25
- 打赏
- 举报
--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)
go
select 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 行）
*/
SQLCenter 2010-10-25
- 打赏
- 举报
#2
查缺号或孤岛 要不要搞那么复杂
查缺号或孤岛 要不要搞那么复杂
SQLCenter 2010-10-25
- 打赏
- 举报
--> 测试数据：#
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
*/
「已注销」 2010-10-25
- 打赏
- 举报
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
--*/
