【欢度元旦】使用SQL查找指定范围内的缺失单号

wuxiang_123 · 发表于 2013-12-31 09:26

本帖最后由 wuxiang_123 于 2013-12-31 13:28 编辑

要求：
说明.jpg

演示效果：

该贴已经同步到 wuxiang_123的微博

amazeyeli · 发表于 2014-1-2 10:49

SELECT IIF(stars=ends,stars,stars&'-'&ends) AS 结果
FROM
(
SELECT A.单号+1 AS stars,MIN(B.单号)-1 AS ends
FROM
(
SELECT A.单号
FROM [数据$] AS A
INNER JOIN [查询$] AS B
ON A.单号>=B.开始 AND A.单号<=B.结束
) AS A
INNER JOIN
(
SELECT A.单号
FROM [数据$] AS A
INNER JOIN [查询$] AS B
ON A.单号>=B.开始 AND A.单号<=B.结束
) AS B
ON A.单号 < B.单号
GROUP BY A.单号
HAVING MIN(B.单号) - A.单号 > 1
UNION ALL
SELECT 开始 AS stars,
(SELECT MIN(单号)-1 FROM [数据$]) AS ends
FROM [查询$]
WHERE 开始<(SELECT MIN(单号) FROM [数据$])
UNION ALL
SELECT (SELECT MAX(单号)+1 FROM [数据$]) AS stars,
结束 AS ends
FROM [查询$]
WHERE 结束>(SELECT MAX(单号) FROM [数据$])
)

复制代码

有点慢

amazeyeli · 发表于 2014-1-2 10:51

还是用排名函数快

USE tempdb
GO
IF OBJECT_ID('TEST') IS NOT NULL
DROP TABLE TEST;
GO
CREATE TABLE TEST
(
num INT
);
GO
----------------------------------------
DECLARE @star INT = 1,
@send INT = 1000
;WITH t AS
(SELECT num,id=ROW_NUMBER()OVER(ORDER BY num) FROM TEST
WHERE num>=@star AND num<=@send)
SELECT CASE WHEN star = send
THEN CAST(star AS varchar(10))
ELSE CAST(star AS varchar(10))+ '-' + CAST(send AS varchar(10))
END AS lasts
FROM
(
SELECT @star AS star,(SELECT MIN(num)-1 FROM t) AS send
WHERE @star<(SELECT MIN(num) FROM t)
UNION ALL
SELECT a.num + 1 AS star,b.num - 1 AS send
FROM t AS a
JOIN t AS b
ON a.id = b.id - 1
WHERE b.num - a.num > 1
UNION ALL
SELECT (SELECT MAX(num)+1 FROM t) AS star,@send AS send
WHERE @send>(SELECT MAX(num) FROM t)
) AS A
----------------------------------------
/*
lasts
---------------------
1
8-9
12
16-19
21
24-25
27-28
32-33
36
40-41
44
47
53
55
59-63
66-68
74-75
79-80
82
87
91-93
97
99
101
103
108
113
116
118
125-126
130
133-135
142
144-146
151
153
158
165
168-169
172
176
180
184
188
190
194
196-197
206
208-209
211-212
217-219
222
225-226
228-229
235
237
240-241
243
246
248
250-251
259
262
265
271
274-275
277
284-285
288
293-297
299
301
304-305
308
313-314
316
319
321
323
325
327-328
330-331
333-334
337
339-341
351-352
354-355
363-364
368-370
374
378-379
385-386
392
394
405
408-409
420-421
423-424
427-428
434
436-437
440
443-444
448
451-452
454-458
460
465
467
476-477
479-480
488-489
494
496
498
500
502
504-505
508-509
512
514
519
521-522
533-534
536
541-542
544-545
549-552
557
559-560
562
564
569-570
572-574
576
580-582
585
587
592-593
596
600
602
607
611
613
617
620
622
629-630
632
635
640
642
644
647
649-654
656-658
669
674
676
678-680
682
686-690
692
694
699
701
703-705
707-709
711
713
719
725-726
734
736
743
745
748
755
760
763
766-767
769-770
772
775
777
782
784
788
792
799
801-802
806-809
814
816
820-821
825-827
829-831
844
851
855
859
861-862
866
868
870
877
879
881
883
885
891
901
903
908
912
914-915
919-922
926
928
930
932
934-935
944
948-950
952
958
962-964
966
968
970-971
973
975
977
980
987-991
993-996
999
(238 行受影响)
*/
----------------------------------------

复制代码

wuxiang_123 · 发表于 2014-1-2 12:36

amazeyeli 发表于 2014-1-2 10:49
有点慢

我的语句：

游客，如果您要查看本帖隐藏内容请回复

wuxiang_123 · 发表于 2014-1-2 13:06

amazeyeli 发表于 2014-1-2 10:49
有点慢

游客，如果您要查看本帖隐藏内容请回复

amazeyeli · 发表于 2014-1-2 16:07

慢在这里:B.单号<C.单号
数据保守有1000+9999+9998+...

HNYSDKZ · 发表于 2014-1-3 15:14

谢谢，香帅。

335081548 · 发表于 2014-1-6 21:56

回复
开贴

觳鳥鴝鼜 · 发表于 2014-1-16 12:27

本帖最后由觳鳥鴝鼜于 2014-1-16 12:30 编辑

非常有意思，试了一下，还是2楼的速度最快

流云帅帅 · 发表于 2014-1-16 22:05

牛人还是很多的，大神啊

		自动登录	找回密码
密码			免费注册

[原创] 【欢度元旦】使用SQL查找指定范围内的缺失单号

评分

点评

评分

点评