22,207
社区成员
发帖
与我相关
我的任务
分享
--> 建立数据表#test1 --数据表#test1里有'unotext'字段记录(一共有1178009行数据,每一行的数据都是基因信息数据)
if object_id('tempdb.dbo.#test1') is not null drop table #test1
go
create table #test1([personid] int,[unotext] nvarchar(20))
insert #test1
select 100001,'04005013022025028' union all
select 100002,'04005013022025030'
-->查询基因信息第2、5、8、11、14、17位的数是连续的自然数,例如“1,2”或者“1,2,3”或者“4,5”或者“4,5,6”或者“4,5,6,7”或者“1,2,3,4,5,6”以此类推
SELECT personid, unotext
FROM #test1
where 1=1
and Cast(SUBSTRING(unotext,2,1) as int )+Cast(SUBSTRING(unotext,8,1) as int )=2*Cast(SUBSTRING(unotext,5,1) as int )
or Cast(SUBSTRING(unotext,5,1) as int )+Cast(SUBSTRING(unotext,11,1) as int )=2*Cast(SUBSTRING(unotext,8,1) as int )
or Cast(SUBSTRING(unotext,8,1) as int )+Cast(SUBSTRING(unotext,14,1) as int )=2*Cast(SUBSTRING(unotext,11,1) as int )
or Cast(SUBSTRING(unotext,11,1) as int )+Cast(SUBSTRING(unotext,17,1) as int )=2*Cast(SUBSTRING(unotext,14,1) as int )
order by personid
-->执行结果
/*
personid unotext
100001 04005013022025028
*/
-->问题:执行语句错在哪里?执行要求:查询第2、5、8、11、14、17位的数是连续的自然数(2位或2位以上连续)的基因信息
/*想要的结果*/
/*
personid unotext
100001 04005013022025028
100002 04005013022025030
*/
--> 建立数据表#test1 --数据表#test1里有'unotext'字段记录(一共有1178009行数据,每一行的数据都是基因信息数据)
if object_id('tempdb.dbo.#test1') is not null drop table #test1
go
create table #test1([personid] int,[unotext] nvarchar(20))
insert #test1
select 100001,'04005013022025028' union all
select 100002,'04005013022025030' union all
select 100002,'24027029030031032'
-->查询基因信息第2、5、8、11、14、17位的数是连续的自然数,例如“0,1”或者“1,2,3”或者“4,5”或者“4,5,6”或者“4,5,6,7”或者“1,2,3,4,5,6”以此类推
SELECT personid, unotext
FROM #test1
where 1=1
AND cast([unotext] as varchar(20)) like '%01002%'
OR cast([unotext] as varchar(20)) like '%01002003%'
OR cast([unotext] as varchar(20)) like '%01002003004%'
OR cast([unotext] as varchar(20)) like '%01002003004005%'
OR cast([unotext] as varchar(20)) like '%01002003004005006%'
OR cast([unotext] as varchar(20)) like '%02003%'
OR cast([unotext] as varchar(20)) like '%02003004%'
OR cast([unotext] as varchar(20)) like '%02003004005%'
OR cast([unotext] as varchar(20)) like '%02003004005006%'
OR cast([unotext] as varchar(20)) like '%03004%'
OR cast([unotext] as varchar(20)) like '%03004005%'
OR cast([unotext] as varchar(20)) like '%03004005006%'
OR cast([unotext] as varchar(20)) like '%04005%'
OR cast([unotext] as varchar(20)) like '%04005006%'
OR cast([unotext] as varchar(20)) like '%05006%'
OR cast([unotext] as varchar(20)) like '%10011%'
OR cast([unotext] as varchar(20)) like '%10011012%'
OR cast([unotext] as varchar(20)) like '%10011012013%'
OR cast([unotext] as varchar(20)) like '%10011012013014%'
OR cast([unotext] as varchar(20)) like '%10011012013014015%'
OR cast([unotext] as varchar(20)) like '%20021%'
OR cast([unotext] as varchar(20)) like '%20021022%'
OR cast([unotext] as varchar(20)) like '%20021022023%'
OR cast([unotext] as varchar(20)) like '%20021022023 24%'
OR cast([unotext] as varchar(20)) like '%20021022023 24 25%'
OR cast([unotext] as varchar(20)) like '%30031%'
OR cast([unotext] as varchar(20)) like '%30031032%'
OR cast([unotext] as varchar(20)) like '%30031032033%'
/*结果*/
/*
personid unotext
100001 04005013022025028
100002 04005013022025030
100003 24027029030031032
*/
问题:LZ水平菜鸟级,笨拙!!!有更加简短的SQL语句吗?求解,期盼中......
--> 建立数据表#test1 --数据表#test1里有'unotext'字段记录(一共有1178009行数据,每一行的数据都是基因信息数据)
if object_id('tempdb.dbo.#test1') is not null drop table #test1
go
create table #test1([personid] int,[unotext] nvarchar(20))
insert #test1
select 100001,'01005013022027028' union all ---第2、5、8、11、14、17位的数分别是1/5/3/2/7/8,第14、17位分别为7/8,符合查询条件,有结果。
select 100002,'04005013022025030' union all ---第2、5、8、11、14、17位的数分别是4/5/3/2/5/0,第2、5位分别为4/5,符合查询条件,有结果。
select 100002,'06005013022024031' union all ---第2、5、8、11、14、17位的数分别是6/5/3/2/4/1,没有从小到大的自然数,不符合查询条件,没有结果。
select 100002,'24027029030031032' ---第2、5、8、11、14、17位的数分别是4/7/9/0/1/2,第8、11、14、17位分别为9/0/1/2,符合查询条件,有结果。
PS:不是取第2、5、8、11、14、17位的数进行排序,而是查询第2、5、8、11、14、17位的数是否符合0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9从小到大排序。
恳请指教,学习中。。。。。
--> 建立数据表#test1 --数据表#test1里有'unotext'字段记录(一共有1178009行数据,每一行的数据都是基因信息数据)
if object_id('tempdb.dbo.#test1') is not null drop table #test1
go
create table #test1([personid] int,[unotext] nvarchar(20))
insert #test1
select 100001,'04005013022025028' union all
select 100002,'04005013022025030'
-->查询基因信息第2、5、8、11、14、17位的数是连续的自然数,例如“1,2”或者“1,2,3”或者“4,5”或者“4,5,6”或者“4,5,6,7”或者“1,2,3,4,5,6”以此类推
SELECT personid, unotext,b.*
FROM #test1 AS a
CROSS APPLY(
SELECT COUNT(*)-count(DISTINCT Grp)
FROM (
SELECT Num1-ROW_NUMBER()OVER(ORDER BY b1.Num1) AS Grp
FROM
(SELECT SUBSTRING(unotext,2,1)*1 AS Num1
UNION ALL SELECT SUBSTRING(unotext,5,1) AS Num2
UNION ALL SELECT SUBSTRING(unotext,8,1) AS Num3
UNION ALL SELECT SUBSTRING(unotext,11,1) AS Num4
UNION ALL SELECT SUBSTRING(unotext,15,1) AS Num5
UNION ALL SELECT SUBSTRING(unotext,17,1) AS Num6) AS b1
)b2
)b(con)
WHERE b.con>=2--连续自然数>=2
order by personid