如何在SQL中的组中按顺序查找缺失值?
问题描述:
我有ID的表格和定位如何在SQL中的组中按顺序查找缺失值?
CREATE TABLE #MissingSequence (ID INT NOT NULL, Position INT NOT NULL)
INSERT INTO #MissingSequence (ID,Position)
SELECT 36,1
UNION ALL SELECT 36,2
UNION ALL SELECT 36,3
UNION ALL SELECT 36,4
UNION ALL SELECT 36,5
UNION ALL SELECT 36,6
UNION ALL SELECT 44,1
UNION ALL SELECT 44,3
UNION ALL SELECT 44,4
UNION ALL SELECT 44,5
UNION ALL SELECT 44,6
我试图找出是,如果有是ID位置序列在这种情况下,任何突破44.1和44.3之间
突破我已经成功地一起解析:
SELECT l.ID
,Start_Position = MIN(l.Position) + 1
,Stop_Position = MIN(fr.Position) - 1
FROM #MissingSequence l
LEFT JOIN #MissingSequence r
ON l.Position = r.Position - 1
LEFT JOIN #MissingSequence fr
ON l.Position < fr.Position
WHERE r.Position IS NULL
AND fr.Position IS NOT NULL
GROUP BY l.ID
,但如果有多个ID值,这是行不通的。如果只有一个ID 44存在,它确实有效。
想法,意见,建议?
谢谢!
答
左自联接是一个很好的本能,但我不认为聚集体削减它,当然你需要在自连接中包含匹配-ID子句。
下面是使用(ANSI标准)版的空左加盟的想法,选择顶行和底行和检查两者其实没什么:
SELECT
above.ID AS ID, below.Position+1 AS Start_Position, above.Position-1 AS End_Position
FROM MissingSequence AS above
JOIN MissingSequence AS below
ON below.ID=above.ID AND below.Position<above.Position-1
LEFT JOIN MissingSequence AS inbetween
ON inbetween.ID=below.ID AND inbetween.Position BETWEEN below.Position+1 AND above.Position-1
WHERE inbetween.ID IS NULL;
+----+----------------+--------------+
| ID | Start_Position | End_Position |
+----+----------------+--------------+
| 44 | 2 | 2 |
+----+----------------+--------------+
答
这个查询找到了简单的单词,希望有用;如果你是在SQL 2005,您可以使用一个CTE
SELECT ID, Position + 1
FROM #MissingSequence t1
WHERE (Position + 1) NOT IN (SELECT Position FROM #MissingSequence t2 WHERE t1.ID = t2.ID)
AND Position <> (SELECT MAX(Position) FROM #MissingSequence t2 WHERE t1.ID = t2.ID)
答
create database testing
use testing;
create table sequence (
id int not null primary key
);
insert into sequence(id) values
(1), (2), (3), (4), (6), (7), (8), (9),
(10), (15), (16), (17), (18), (19), (20);
select * from sequence
Create PROCEDURE test_proce(@mode varchar(50))
AS
BEGIN
declare @se int;
set @se=0;
set @se=(
select top 1 t.id + 1
from sequence t
left join sequence x on x.id = t.id + 1
where x.id is null
order by t.id
);
select * from sequence where id<@se;
END
exec test_proce 'mode'
你喜欢什么输出查看?有最大允许位置值吗? – tpdi 2009-04-07 20:19:58
没有职位的最大数目...我正在处理的全表包含大约12k ID记录,职位从3到x – 2009-04-07 20:30:38
@Christopher,我的解决方案有什么问题吗? – 2009-04-07 20:34:47