英超

如何查漏编号字段_0

2019-09-13 19:49:53来源:励志吧0次阅读

如何查漏编号字段

SQL code

我有一张表A,有字段A(编号,插入一行编号+1),字段B(金额),字段C(人次)

现在想统计一段数据:select count(1) from 表A where 字段A>=200 and 字段A<=400

结果是199张,漏了一个编号,想用一个查询语句查出漏的编号是那个号?或者漏的编号的上一个编号

--参考

SQL code SELECT Col1+1 AS 缺号起点,

(SELECT MIN(Col1) FROM tb

WHERE Col1>t.Col1) -1 AS 缺号终点

FROM tb AS t

WHERE NOT EXISTS

(SELECT * FROM tb WHERE Col1=t.Col1+1)

假设只有三位数字。

那么可以用如下代码:

SQL codeSELECT 字段1

from LI T

where not exists (select 1 From LI

where (Cast(right(字段1,3) as int) - cast(right(T.字段1,3) as int)) = 1)

SQL code--生成已用编号分布字符串的函数

CREATE FUNCTION f_GetStrSeries(@col1 varchar(10))

RETURNS varchar(8000)

AS

BEGIN

DECLARE @re varchar(8000),@pid int

SELECT @re='',@pid=-1

SELECT @re=CASE

WHEN col2=@pid+1 THEN @re

ELSE @re

+CASE

WHEN RIGHT(@re,CHARINDEX(',',REVERSE(@re)+',')-1)=@pid THEN ''

ELSE CAST(-@pid as varchar)

END

+','+CAST(col2 as varchar)

END,

@pid=col2

FROM tb

WHERE col1=@col1

ORDER BY col2

RETURN(STUFF(@re,1,2,'')

+CASE

WHEN RIGHT(@re,CHARINDEX(',',REVERSE(@re)+',')-1)=@pid THEN ''

ELSE CAST(-@pid as varchar)

END)

END

GO

--生成缺号分布字符串的函数

CREATE FUNCTION f_GetStrNSeries(@col1 varchar(10))

RETURNS VARCHAR(8000)

AS

BEGIN

DECLARE @re varchar(8000),@pid int

SELECT @re='',@pid=0

SELECT @re=CASE

WHEN col2=@pid+1 THEN @re

ELSE @re+','+CAST(@pid+1 as varchar)

+CASE

WHEN @pid+1=col2-1 THEN ''

ELSE CAST(1-col2 as varchar)

END

END,

@pid=col2

FROM tb

WHERE col1=@col1

ORDER BY col2

RETURN(STUFF(@re,1,1,''))

END

GO

--调用测试

--测试数据

CREATE TABLE tb(col1 varchar(10),col2 int)

INSERT tb SELECT 'a',2

UNION ALL SELECT 'a',3

UNION ALL SELECT 'a',5

UNION ALL SELECT 'a',8

UNION ALL SELECT 'a',9

UNION ALL SELECT 'b',1

UNION ALL SELECT 'b',5

UNION ALL SELECT 'b',6

UNION ALL SELECT 'b',7

--查询

SELECT col1,

col2_Series=dbo.f_GetStrSeries(col1),

col2_Series=dbo.f_GetStrNSeries(col1)

FROM tb

GROUP BY col1

/*--结果

col1 col2_Series col2_Series

-------------- ------------------------ --------------

a 2-3,5,8-9 1,4,6-7

b 1,5-7 2-4

--*/

--生成已用编号分布字符串的函数

CREATE FUNCTION f_GetStrSeries(@col1 varchar(10))

RETURNS varchar(8000)

AS

BEGIN

DECLARE @re varchar(8000),@pid int

SELECT @re='',@pid=-1

SELECT @re=CASE

WHEN col2=@pid+1 THEN @re

ELSE @re

+CASE

WHEN RIGHT(@re,CHARINDEX(',',REVERSE(@re)+',')-1)=@pid THEN ''

ELSE CAST(-@pid as varchar)

END

+','+CAST(col2 as varchar)

END,

@pid=col2

FROM tb

WHERE col1=@col1

ORDER BY col2

RETURN(STUFF(@re,1,2,'')

+CASE

WHEN RIGHT(@re,CHARINDEX(',',REVERSE(@re)+',')-1)=@pid THEN ''

ELSE CAST(-@pid as varchar)

END)

END

GO

--生成缺号分布字符串的函数

CREATE FUNCTION f_GetStrNSeries(@col1 varchar(10))

RETURNS VARCHAR(8000)

AS

BEGIN

DECLARE @re varchar(8000),@pid int

SELECT @re='',@pid=0

SELECT @re=CASE

WHEN col2=@pid+1 THEN @re

ELSE @re+','+CAST(@pid+1 as varchar)

+CASE

WHEN @pid+1=col2-1 THEN ''

ELSE CAST(1-col2 as varchar)

END

END,

@pid=col2

FROM tb

WHERE col1=@col1

ORDER BY col2

RETURN(STUFF(@re,1,1,''))

END

GO

--调用测试

--测试数据

CREATE TABLE tb(col1 varchar(10),col2 int)

INSERT tb SELECT 'a',2

UNION ALL SELECT 'a',3

UNION ALL SELECT 'a',5

UNION ALL SELECT 'a',8

UNION ALL SELECT 'a',9

UNION ALL SELECT 'b',1

UNION ALL SELECT 'b',5

UNION ALL SELECT 'b',6

UNION ALL SELECT 'b',7

--查询

SELECT col1,

col2_Series=dbo.f_GetStrSeries(col1),

col2_Series=dbo.f_GetStrNSeries(col1)

FROM tb

GROUP BY col1

/*--结果

col1 col2_Series col2_Series

-------------- ------------------------ --------------

a 2-3,5,8-9 1,4,6-7

b 1,5-7 2-4

--*/

小孩脾胃虚弱用药
宝宝脾虚怎么办
小孩经常流鼻血是怎么回事
小孩发烧
分享到: