A分组中最大的是5,B分组中最的的是3啊. no type 3 A 4 B 这是你提供的预期结果吗? 怎么感觉我不在地球? 断号?怎么理解?
按楼主的描述,结果应该是 NO TYPE 3 A 3 B 干, 耗了半天终于搞出结果来了 declare @tb table([no] int,[type] varchar(1)) insert @tb select 1,'A' union all select 2,'A' union all select 4,'A' union all select 5,'A' union all select 2,'B' union all select 3,'B'SELECT type, [no]+1 AS [no] FROM @tb t WHERE [no]+1 not in (select [no] from @tb where t.type=type) and [no] <(select max([no]) from @tb where t.type = type) UNION ALL SELECT b.type,b.no FROM @TB B LEFT JOIN ( SELECT type, [no]+1 AS [no] FROM @tb t WHERE [no]+1 not in (select [no] from @tb where t.type=type) and [no] <(select max([no]) from @tb where t.type = type) ) C ON B.TYPE = C.TYPE WHERE (NOT EXISTS(SELECT 1 FROM @TB WHERE B.TYPE = TYPE AND NO>B.NO)) AND (C.TYPE IS NULL) --测试结果: /* type no ---- ----------- A 3 B 3(所影响的行数为 2 行) */
早说嘛, 那我下面这样的应该是对的了, declare @tb table([no] int,[type] varchar(1)) insert @tb select 1,'A' union all select 2,'A' union all select 4,'A' union all select 5,'A' union all select 2,'B' union all select 3,'B'SELECT type, [no]+1 AS [no] FROM @tb t WHERE [no]+1 not in (select [no] from @tb where t.type=type) and [no] <(select max([no]) from @tb where t.type = type) UNION ALL SELECT b.type,b.no+1 FROM @TB B LEFT JOIN ( SELECT type, [no]+1 AS [no] FROM @tb t WHERE [no]+1 not in (select [no] from @tb where t.type=type) and [no] <(select max([no]) from @tb where t.type = type) ) C ON B.TYPE = C.TYPE WHERE (NOT EXISTS(SELECT 1 FROM @TB WHERE B.TYPE = TYPE AND NO>B.NO)) AND (C.TYPE IS NULL) --测试结果: /* type no ---- ----------- A 3 B 4(所影响的行数为 2 行)*/
no type 1 A 2 A 4 A 5 A 7 A 8 A 取哪个?
no type 1 A 2 A 4 A 5 A 8 A 9 A 这又取哪个?
select 'type no ---- ----------- A 3 B 3'
no type 1 A 2 A 4 A 5 A 8 A 9 A 第一次取出3,然后会插入3 第二次取出6,再插入6 第三次取出7,再插入7 第四次则取10,接下去就顺了
SELECT A.TYPE, [NO] = MIN(A.NO) + 1 FROM @TB A LEFT JOIN @TB B ON A.TYPE = B.TYPE AND A.NO +1 = B.NO WHERE B.TYPE IS NULL GROUP BY A.TYPE
--> 生成测试数据: @tb DECLARE @tb TABLE (no int,type varchar(1)) INSERT INTO @tb SELECT 1,'A' UNION ALL SELECT 2,'A' UNION ALL SELECT 4,'A' UNION ALL SELECT 5,'A' UNION ALL SELECT 9,'A' UNION ALL SELECT 10,'A' UNION ALL SELECT 2,'B' UNION ALL SELECT 3,'B'--SQL查询如下:SELECT A.type,B.number AS no FROM (SELECT type,MIN(no) AS minNo,MAX(no) AS maxNo FROM @tb GROUP BY type) AS A JOIN master.dbo.spt_values AS B ON B.type = 'P' AND B.number BETWEEN A.minNo AND A.maxNo+1 LEFT JOIN @tb AS C ON A.type = C.type AND B.number = C.no WHERE C.type IS NULL;/* type no ---- ----------- A 3 A 6 A 7 A 8 A 11 B 4(6 行受影响)*/
no type
3 A
4 B
这是你提供的预期结果吗?
怎么感觉我不在地球?
断号?怎么理解?
NO TYPE
3 A
3 B
干, 耗了半天终于搞出结果来了
declare @tb table([no] int,[type] varchar(1))
insert @tb
select 1,'A' union all
select 2,'A' union all
select 4,'A' union all
select 5,'A' union all
select 2,'B' union all
select 3,'B'SELECT type, [no]+1 AS [no]
FROM @tb t
WHERE [no]+1 not in (select [no] from @tb where t.type=type) and [no] <(select max([no]) from @tb where t.type = type)
UNION ALL
SELECT b.type,b.no
FROM @TB B LEFT JOIN (
SELECT type, [no]+1 AS [no]
FROM @tb t
WHERE [no]+1 not in (select [no] from @tb where t.type=type) and [no] <(select max([no]) from @tb where t.type = type)
) C ON B.TYPE = C.TYPE
WHERE (NOT EXISTS(SELECT 1 FROM @TB WHERE B.TYPE = TYPE AND NO>B.NO)) AND (C.TYPE IS NULL)
--测试结果:
/*
type no
---- -----------
A 3
B 3(所影响的行数为 2 行)
*/
早说嘛, 那我下面这样的应该是对的了, declare @tb table([no] int,[type] varchar(1))
insert @tb
select 1,'A' union all
select 2,'A' union all
select 4,'A' union all
select 5,'A' union all
select 2,'B' union all
select 3,'B'SELECT type, [no]+1 AS [no]
FROM @tb t
WHERE [no]+1 not in (select [no] from @tb where t.type=type) and [no] <(select max([no]) from @tb where t.type = type)
UNION ALL
SELECT b.type,b.no+1
FROM @TB B LEFT JOIN (
SELECT type, [no]+1 AS [no]
FROM @tb t
WHERE [no]+1 not in (select [no] from @tb where t.type=type) and [no] <(select max([no]) from @tb where t.type = type)
) C ON B.TYPE = C.TYPE
WHERE (NOT EXISTS(SELECT 1 FROM @TB WHERE B.TYPE = TYPE AND NO>B.NO)) AND (C.TYPE IS NULL)
--测试结果:
/*
type no
---- -----------
A 3
B 4(所影响的行数为 2 行)*/
1 A
2 A
4 A
5 A
7 A
8 A 取哪个?
1 A
2 A
4 A
5 A
8 A
9 A 这又取哪个?
---- -----------
A 3
B 3'
1 A
2 A
4 A
5 A
8 A
9 A 第一次取出3,然后会插入3
第二次取出6,再插入6
第三次取出7,再插入7
第四次则取10,接下去就顺了
SELECT A.TYPE, [NO] = MIN(A.NO) + 1
FROM @TB A
LEFT JOIN @TB B
ON A.TYPE = B.TYPE AND A.NO +1 = B.NO
WHERE B.TYPE IS NULL
GROUP BY A.TYPE
-- Author : liangCK 梁爱兰
-- Comment: 小梁 爱 兰儿
-- Date : 2009-11-25 09:14:59
-------------------------------------
--> 生成测试数据: @tb
DECLARE @tb TABLE (no int,type varchar(1))
INSERT INTO @tb
SELECT 1,'A' UNION ALL
SELECT 2,'A' UNION ALL
SELECT 4,'A' UNION ALL
SELECT 5,'A' UNION ALL
SELECT 9,'A' UNION ALL
SELECT 10,'A' UNION ALL
SELECT 2,'B' UNION ALL
SELECT 3,'B'--SQL查询如下:SELECT A.type,B.number AS no
FROM (SELECT type,MIN(no) AS minNo,MAX(no) AS maxNo
FROM @tb GROUP BY type) AS A
JOIN master.dbo.spt_values AS B
ON B.type = 'P' AND B.number BETWEEN A.minNo AND A.maxNo+1
LEFT JOIN @tb AS C
ON A.type = C.type AND B.number = C.no
WHERE C.type IS NULL;/*
type no
---- -----------
A 3
A 6
A 7
A 8
A 11
B 4(6 行受影响)*/