如下例所示:
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
现在我要的结果是这样的:
我要把2月份所有的断号查询出来:
ID
0003
0006
0008
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
现在我要的结果是这样的:
我要把2月份所有的断号查询出来:
ID
0003
0006
0008
from tb t
where not exists(select 1 from tb where id=t.id+1)
and id not in(select max(id) from tb)
IF EXISTS (SELECT * FROM sysobjects WHERE NAME ='tb')
DROP TABLE [tb]
CREATE TABLE [tb]
(
[ID] INT NULL ,
[BILL_DATE] datetime NULL ,
[QUAN] int NULL
)
GO--插入测试数据
INSERT INTO [tb] ([ID],[BILL_DATE],[QUAN])
SELECT '0001','2010-02-03','4000' UNION
SELECT '0002','2010-02-03','200' UNION
SELECT '0004','2010-02-04','204' UNION
SELECT '0005','2010-02-07','333' UNION
SELECT '0007','2010-02-09','123' UNION
SELECT '0010','2010-02-27','2034' UNION
SELECT '0009','2010-02-27','2341'
GO
SELECT * FROM [tb]
select right('0000'+ltrim(id+1),4)
from tb t
where not exists(select 1 from tb where id=t.id+1)
and id not in(select max(id) from tb)--result
/*
0003
0006
0008
/*
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([ID] varchar(4),[BILL_DATE] datetime,[QUAN] int)
insert [tb]
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
---查询---
select right('0000'+ltrim(a.id),4) as ID
from
(select top 1000 id=row_number() over(order by getdate()) from sys.objects,sys.columns)a
left join tb b on a.id=b.id
where a.id<(select max(id) from tb)
and b.id is null---结果---
ID
--------
0003
0006
0008(3 行受影响)
DROP TABLE [tb]
CREATE TABLE [tb]
(
[ID] INT NULL ,
[BILL_DATE] datetime NULL ,
[QUAN] int NULL
)
GO--插入测试数据
INSERT INTO [tb] ([ID],[BILL_DATE],[QUAN])
SELECT '0001','2010-02-03','4000' UNION
SELECT '0002','2010-02-03','200' UNION
SELECT '0004','2010-02-04','204' UNION
SELECT '0005','2010-02-07','333' UNION
SELECT '0007','2010-02-09','123' UNION
SELECT '0010','2010-02-27','2034'
GO
SELECT RIGHT(10000+number,4) AS num
FROM (
SELECT s.number
FROM master..spt_values s
WHERE s.number BETWEEN 1 AND (SELECT max(id) FROM tb) AND type='p' ) k
LEFT JOIN tb t ON k.number=t.id
WHERE t.id IS NULL
/*
]num
----
0003
0006
0008
0009*/
WHERE t.type='P' AND T.number NOT IN(SELECT ID FROM TB)