--> 测试数据:[tbl] if object_id('[tbl]') is not null drop table [tbl] create table [tbl]([cardnumber] bigint) insert [tbl] select 6226688888880001 union all select 6226688888880002 union all select 6226688888880005 union all select 6226688888880008 union all select 6226688888880009 union all select 6226688888880010go declare @card bigint select @card=MAX([cardnumber]) from tbl ;with t as( select * from tbl union all select a.[cardnumber]+1 from t a where not exists(select * from tbl b where b.[cardnumber]=a.[cardnumber]+1 ) and a.[cardnumber]<@card ) select *from t where not exists(select 1 from tbl a where a.cardnumber=t.cardnumber) order by [cardnumber] /* cardnumber 6226688888880003 6226688888880004 6226688888880006 6226688888880007 */
补充下有重复日期情况下,我的方法,呵呵CREATE TABLE A ( ID INT NOT NULL, Name VARCHAR(100) NOT NULL )GOINSERT INTO A SELECT 1,'haha' UNION SELECT 2,'hoho' UNION SELECT 3,'hehe' UNION SELECT 4,'hihi' CREATE TABLE B ( ID INT NOT NULL, ShoppingDate VARCHAR(100) )GOINSERT INTO B SELECT 1,'2012-04-01 14:00:00' UNION SELECT 1,'2012-04-01 13:00:00' UNION SELECT 1,'2012-04-03 12:00:00' UNION SELECT 1,'2012-04-04 12:00:00' UNION SELECT 1,'2012-04-05 12:00:00' UNION SELECT 1,'2012-04-06 12:00:00' UNION SELECT 1,'2012-04-07 12:00:00' UNION SELECT 1,'2012-04-08 12:00:00' UNION SELECT 1,'2012-04-10 12:00:00' UNION SELECT 1,'2012-04-11 12:00:00' UNION SELECT 1,'2012-04-13 12:00:00' UNION SELECT 1,'2012-04-14 12:00:00' UNION SELECT 1,'2012-04-15 12:00:00' UNION SELECT 1,'2012-04-16 12:00:00' UNION SELECT 1,'2012-04-17 12:00:00' UNION SELECT 1,'2012-04-18 12:00:00' UNION SELECT 1,'2012-04-19 12:00:00' UNION SELECT 1,'2012-04-20 12:00:00' UNION SELECT 1,'2012-04-21 12:00:00' UNION SELECT 1,'2012-04-22 12:00:00' UNION SELECT 1,'2012-04-23 12:00:00' UNION SELECT 1,'2012-04-24 12:00:00' UNION SELECT 1,'2012-04-25 12:00:00' UNION SELECT 1,'2012-04-26 12:00:00' UNION SELECT 1,'2012-04-27 12:00:00' UNION SELECT 3,'2012-04-01 12:00:00' UNION SELECT 3,'2012-04-02 12:00:00' UNION SELECT 3,'2012-04-03 12:00:00' UNION SELECT 3,'2012-04-04 12:00:00' UNION SELECT 3,'2012-04-05 12:00:00' UNION SELECT 3,'2012-04-06 12:00:00' UNION SELECT 3,'2012-04-07 12:00:00' UNION SELECT 3,'2012-04-08 12:00:00' UNION SELECT 2,'2012-04-10 12:00:00' UNION SELECT 2,'2012-04-11 12:00:00' UNION SELECT 4,'2012-04-13 12:00:00' UNION SELECT 4,'2012-04-14 12:00:00' UNION SELECT 4,'2012-04-15 12:00:00' UNION SELECT 4,'2012-04-16 12:00:00' UNION SELECT 4,'2012-04-17 12:00:00' UNION SELECT 4,'2012-04-18 12:00:00' UNION SELECT 4,'2012-04-19 12:00:00' UNION SELECT 4,'2012-04-20 12:00:00' UNION SELECT 4,'2012-04-21 12:00:00' UNION SELECT 4,'2012-04-22 12:00:00' UNION SELECT 4,'2012-04-23 12:00:00' UNION SELECT 4,'2012-04-24 12:00:00' UNION SELECT 4,'2012-04-25 12:00:00' UNION SELECT 4,'2012-04-26 12:00:00' UNION SELECT 4,'2012-04-27 12:00:00' SELECT C.ID,A.Name,MIN(shoppingdate),MAX(shoppingdate),COUNT(C.ID) FROM (SELECT ID,shoppingdate,rn=ROW_NUMBER()OVER (ORDER BY Id,shoppingdate) FROM (SELECT DISTINCT ID,CONVERT(VARCHAR(10),shoppingdate,23) AS shoppingdate FROM B) AS D) AS C,A WHERE C.ID = A.ID GROUP BY C.ID,A.Name,DATEADD(DAY,0-rn,shoppingdate) ORDER BY C.ID 这是magician547写的,我转发
数据只有一个字段,叫做 card_no
值如下:
6226688888880001
6226688888880002
6226688888880005
6226688888880008
6226688888880009
6226688888880010请找出卡号范围6226688888880001-6226688888880010的断号
并列出来
--> 测试数据:[tbl]
if object_id('[tbl]') is not null drop table [tbl]
create table [tbl]([cardnumber] bigint)
insert [tbl]
select 6226688888880001 union all
select 6226688888880002 union all
select 6226688888880005 union all
select 6226688888880008 union all
select 6226688888880009 union all
select 6226688888880010go
declare @card bigint
select @card=MAX([cardnumber]) from tbl
;with t
as(
select * from tbl
union all
select a.[cardnumber]+1 from t a
where not exists(select * from tbl b
where b.[cardnumber]=a.[cardnumber]+1
)
and a.[cardnumber]<@card
)
select *from t
where not exists(select 1 from tbl a where a.cardnumber=t.cardnumber)
order by [cardnumber]
/*
cardnumber
6226688888880003
6226688888880004
6226688888880006
6226688888880007
*/
(
ID INT NOT NULL,
Name VARCHAR(100) NOT NULL
)GOINSERT INTO A
SELECT 1,'haha' UNION
SELECT 2,'hoho' UNION
SELECT 3,'hehe' UNION
SELECT 4,'hihi'
CREATE TABLE B
(
ID INT NOT NULL,
ShoppingDate VARCHAR(100)
)GOINSERT INTO B
SELECT 1,'2012-04-01 14:00:00' UNION
SELECT 1,'2012-04-01 13:00:00' UNION
SELECT 1,'2012-04-03 12:00:00' UNION
SELECT 1,'2012-04-04 12:00:00' UNION
SELECT 1,'2012-04-05 12:00:00' UNION
SELECT 1,'2012-04-06 12:00:00' UNION
SELECT 1,'2012-04-07 12:00:00' UNION
SELECT 1,'2012-04-08 12:00:00' UNION
SELECT 1,'2012-04-10 12:00:00' UNION
SELECT 1,'2012-04-11 12:00:00' UNION
SELECT 1,'2012-04-13 12:00:00' UNION
SELECT 1,'2012-04-14 12:00:00' UNION
SELECT 1,'2012-04-15 12:00:00' UNION
SELECT 1,'2012-04-16 12:00:00' UNION
SELECT 1,'2012-04-17 12:00:00' UNION
SELECT 1,'2012-04-18 12:00:00' UNION
SELECT 1,'2012-04-19 12:00:00' UNION
SELECT 1,'2012-04-20 12:00:00' UNION
SELECT 1,'2012-04-21 12:00:00' UNION
SELECT 1,'2012-04-22 12:00:00' UNION
SELECT 1,'2012-04-23 12:00:00' UNION
SELECT 1,'2012-04-24 12:00:00' UNION
SELECT 1,'2012-04-25 12:00:00' UNION
SELECT 1,'2012-04-26 12:00:00' UNION
SELECT 1,'2012-04-27 12:00:00' UNION
SELECT 3,'2012-04-01 12:00:00' UNION
SELECT 3,'2012-04-02 12:00:00' UNION
SELECT 3,'2012-04-03 12:00:00' UNION
SELECT 3,'2012-04-04 12:00:00' UNION
SELECT 3,'2012-04-05 12:00:00' UNION
SELECT 3,'2012-04-06 12:00:00' UNION
SELECT 3,'2012-04-07 12:00:00' UNION
SELECT 3,'2012-04-08 12:00:00' UNION
SELECT 2,'2012-04-10 12:00:00' UNION
SELECT 2,'2012-04-11 12:00:00' UNION
SELECT 4,'2012-04-13 12:00:00' UNION
SELECT 4,'2012-04-14 12:00:00' UNION
SELECT 4,'2012-04-15 12:00:00' UNION
SELECT 4,'2012-04-16 12:00:00' UNION
SELECT 4,'2012-04-17 12:00:00' UNION
SELECT 4,'2012-04-18 12:00:00' UNION
SELECT 4,'2012-04-19 12:00:00' UNION
SELECT 4,'2012-04-20 12:00:00' UNION
SELECT 4,'2012-04-21 12:00:00' UNION
SELECT 4,'2012-04-22 12:00:00' UNION
SELECT 4,'2012-04-23 12:00:00' UNION
SELECT 4,'2012-04-24 12:00:00' UNION
SELECT 4,'2012-04-25 12:00:00' UNION
SELECT 4,'2012-04-26 12:00:00' UNION
SELECT 4,'2012-04-27 12:00:00' SELECT C.ID,A.Name,MIN(shoppingdate),MAX(shoppingdate),COUNT(C.ID)
FROM (SELECT ID,shoppingdate,rn=ROW_NUMBER()OVER (ORDER BY Id,shoppingdate) FROM (SELECT DISTINCT ID,CONVERT(VARCHAR(10),shoppingdate,23) AS shoppingdate FROM B) AS D) AS C,A
WHERE C.ID = A.ID
GROUP BY C.ID,A.Name,DATEADD(DAY,0-rn,shoppingdate)
ORDER BY C.ID
这是magician547写的,我转发