巨难的作业！！！如何通过这些特征（或特征条件）筛选出相对应的基因信息？

RICHEER COCA 2016-04-04 02:43:20





--> 建立数据表#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,'29011008032003030' union all

select 100002,'19010027025013018' union all

select 100003,'26028011013010012' union all

select 100004,'09004026021019020' union all

select 100005,'23007028010001032' union all

select 100006,'13004007025006010' union all

select 100007,'04030017015031006' union all

select 100008,'01010027026021003' union all

select 100009,'23019009026001021' union all

select 100010,'09008017023014005' union all

select 100011,'30018009022005020' union all

select 100012,'01017024008002013'



--> 建立数据表#test2 --数据表#test2里有'jytzum'字段记录（一共有1177行数据，每一行的数据都是基因特征）

 

if object_id('tempdb.dbo.#test2') is not null drop table #test2

go 

create table #test2([SSID] nvarchar(20),[jytzum] nvarchar(200))

insert #test2

select 'D004','Cast(SUBSTRING(unotext,2,1)as int)=2*Cast(SUBSTRING(unotext,5,1)as int)' union all

select 'D017','Cast(SUBSTRING(unotext,1,2)as int)%3=0' union all

select 'D028','Cast(SUBSTRING(unotext,11,1)as int)+Cast(SUBSTRING(unotext,17,1)as int)=2*Cast(SUBSTRING(unotext,14,1)as int)' union all

select 'D030','Cast(SUBSTRING(unotext,11,1)as int)=2*Cast(SUBSTRING(unotext,17,1)as int)' union all

select 'D031','Cast(SUBSTRING(unotext,11,1)as int)=2*Cast(SUBSTRING(unotext,2,1)as int)' union all

select 'D032','Cast(SUBSTRING(unotext,11,1)as int)=2*Cast(SUBSTRING(unotext,5,1)as int)' union all

select 'D038','Cast(SUBSTRING(unotext,13,2)as int)-Cast(SUBSTRING(unotext,10,2)as int)=1' union all

select 'D039','Cast(SUBSTRING(unotext,14,1)as int)+Cast(SUBSTRING(unotext,2,1)as int)=2*Cast(SUBSTRING(unotext,17,1)as int)' union all

select 'D042','Cast(SUBSTRING(unotext,14,1)as int)=2*Cast(SUBSTRING(unotext,2,1)as int)' union all

select 'D051','Cast(SUBSTRING(unotext,17,1)as int)=2*Cast(SUBSTRING(unotext,14,1)as int)' union all

select 'D053','Cast(SUBSTRING(unotext,17,1)as int)=2*Cast(SUBSTRING(unotext,5,1)as int)' union all

select 'D056','Cast(SUBSTRING(unotext,2,1)as int)+Cast(SUBSTRING(unotext,8,1)as int)=2*Cast(SUBSTRING(unotext,5,1)as int)' union all

select 'D057','Cast(SUBSTRING(unotext,2,1)as int)=Cast(SUBSTRING(unotext,17,1)as int)' union all

select 'D059','Cast(SUBSTRING(unotext,4,1)as int)=Cast(SUBSTRING(unotext,17,1)as int)' union all

select 'D070','Cast(SUBSTRING(unotext,7,2)as int)%5=0' union all

select 'D074','Cast(SUBSTRING(unotext,8,1)as int)=Cast(SUBSTRING(unotext,13,1)as int)'



--> 建立被测试者名单数据表#test3 --数据表#test3里有'SID'字段记录（每一行的数据都是每一位被测试者基因特征）

 

if object_id('tempdb.dbo.#test3') is not null drop table #test3

go 

create table #test3([persoNM]  nvarchar(20),[SID] nvarchar(200))

insert #test3

select 'A被测试者','D028 D039 D051' union all      

select 'B被测试者','D042 D059' union all       

select 'C被测试者','D032' union all        

select 'D被测试者','D004 D017 D030 D031 D038 D053 D056 D057 D070 D074'

在基因检测过程中，有的被测试者患有2中或两种以上的基因特征，例如：
A被测试者的基因特征是 D028 D039 D051
B被测试者的基因特征是 D042 D059
C被测试者的基因特征是 D032
D被测试者的基因特征是 D004 D017 D030 D031 D038 D053 D056 D057 D070 D074

问题：如何通过这些特征（或特征条件）快速一次性筛选出A、B、C、D这四位被测试者相对应的基因信息？
A被测试者的基因特征是 D028 D039 D051，对应的基因信息？
B被测试者的基因特征是 D042 D059，对应的基因信息？
C被测试者的基因特征是 D032，对应的基因信息？
D被测试者的基因特征是 D004 D017 D030 D031 D038 D053 D056 D057 D070 D074，对应的基因信息？
盼解，谢谢。

...全文

384 20 打赏收藏转发到动态举报

写回复

用AI写文章

20 条回复

切换为时间正序

请发表友善的回复…

发表回复

道素 2016-04-05

打赏
举报

我也大致写了一种方法，但是我没测试：思路是先将SID中的每个代码退换为公式，如果有多个条件中间用and连接然后遍历每个测试用例，生成动态代码执行，其实思路很简单。


if object_id('tempdb.dbo.#test4') is not null drop table #test4
go 
create table #test4 ([persoNM]  nvarchar(20),[unotext] nvarchar(20))


DECLARE @persoNM NVARCHAR(20),@formula VARCHAR(2000)
DECLARE @SQL VARCHAR(MAX)
DECLARE cur_test CURSOR FOR
select tt3.persoNM,ttt.formula from #test3 as tt3
cross apply (
		select stuff((select ' and ('+t2.jytzum +')' from (
		       select *,convert(xml,'<r><n>'+replace(SID,' ','</n><n>')+'</n></r>') as xssid from #test3 
		) t3
		cross apply (select b.value('.','varchar(100)') as ssid  from t3.xssid.nodes('/r/n') as s(b)) tt
		left join #test2 as t2 on t2.SSID=tt.ssid
		where t3.persoNM=t3.persoNM for xml path('')),1,1,'') as formula
) ttt

open cur_test
FETCH NEXT FROM cur_test INTO @persoNM,@formula
WHILE @@FETCH_STATUS = 0
BEGIN
    set @SQL='insert into #test4 select N'''+@persoNM+''' as persoNM, [personid]  from #test1 as t1 where 1=1  '+ @formula 
	print @sql
	exec(@SQL)
FETCH NEXT FROM cur_test INTO @persoNM,@formula
end

CLOSE cur_test
DEALLOCATE cur_test


SELECT * FROM #test4

举个生成的动态代码例子，如果觉得不对，你可以修改下：


insert into #test4 select N'A被测试者' as persoNM, [personid]  from #test1 as t1 where 1=1  and (Cast(SUBSTRING(unotext,11,1)as int)+Cast(SUBSTRING(unotext,17,1)as int)=2*Cast(SUBSTRING(unotext,14,1)as int)) and (Cast(SUBSTRING(unotext,14,1)as int)+Cast(SUBSTRING(unotext,2,1)as int)=2*Cast(SUBSTRING(unotext,17,1)as int)) and (Cast(SUBSTRING(unotext,17,1)as int)=2*Cast(SUBSTRING(unotext,14,1)as int)) and (Cast(SUBSTRING(unotext,14,1)as int)=2*Cast(SUBSTRING(unotext,2,1)as int)) and (Cast(SUBSTRING(unotext,4,1)as int)=Cast(SUBSTRING(unotext,17,1)as int)) and (Cast(SUBSTRING(unotext,11,1)as int)=2*Cast(SUBSTRING(unotext,5,1)as int)) and (Cast(SUBSTRING(unotext,2,1)as int)=2*Cast(SUBSTRING(unotext,5,1)as int)) and (Cast(SUBSTRING(unotext,1,2)as int)%3=0) and (Cast(SUBSTRING(unotext,11,1)as int)=2*Cast(SUBSTRING(unotext,17,1)as int)) and (Cast(SUBSTRING(unotext,11,1)as int)=2*Cast(SUBSTRING(unotext,2,1)as int)) and (Cast(SUBSTRING(unotext,13,2)as int)-Cast(SUBSTRING(unotext,10,2)as int)=1) and (Cast(SUBSTRING(unotext,17,1)as int)=2*Cast(SUBSTRING(unotext,5,1)as int)) and (Cast(SUBSTRING(unotext,2,1)as int)+Cast(SUBSTRING(unotext,8,1)as int)=2*Cast(SUBSTRING(unotext,5,1)as int)) and (Cast(SUBSTRING(unotext,2,1)as int)=Cast(SUBSTRING(unotext,17,1)as int)) and (Cast(SUBSTRING(unotext,7,2)as int)%5=0) and (Cast(SUBSTRING(unotext,8,1)as int)=Cast(SUBSTRING(unotext,13,1)as int))


insert into #test4 select N'B被测试者' as persoNM, [personid]  from #test1 as t1 where 1=1  and (Cast(SUBSTRING(unotext,11,1)as int)+Cast(SUBSTRING(unotext,17,1)as int)=2*Cast(SUBSTRING(unotext,14,1)as int)) and (Cast(SUBSTRING(unotext,14,1)as int)+Cast(SUBSTRING(unotext,2,1)as int)=2*Cast(SUBSTRING(unotext,17,1)as int)) and (Cast(SUBSTRING(unotext,17,1)as int)=2*Cast(SUBSTRING(unotext,14,1)as int)) and (Cast(SUBSTRING(unotext,14,1)as int)=2*Cast(SUBSTRING(unotext,2,1)as int)) and (Cast(SUBSTRING(unotext,4,1)as int)=Cast(SUBSTRING(unotext,17,1)as int)) and (Cast(SUBSTRING(unotext,11,1)as int)=2*Cast(SUBSTRING(unotext,5,1)as int)) and (Cast(SUBSTRING(unotext,2,1)as int)=2*Cast(SUBSTRING(unotext,5,1)as int)) and (Cast(SUBSTRING(unotext,1,2)as int)%3=0) and (Cast(SUBSTRING(unotext,11,1)as int)=2*Cast(SUBSTRING(unotext,17,1)as int)) and (Cast(SUBSTRING(unotext,11,1)as int)=2*Cast(SUBSTRING(unotext,2,1)as int)) and (Cast(SUBSTRING(unotext,13,2)as int)-Cast(SUBSTRING(unotext,10,2)as int)=1) and (Cast(SUBSTRING(unotext,17,1)as int)=2*Cast(SUBSTRING(unotext,5,1)as int)) and (Cast(SUBSTRING(unotext,2,1)as int)+Cast(SUBSTRING(unotext,8,1)as int)=2*Cast(SUBSTRING(unotext,5,1)as int)) and (Cast(SUBSTRING(unotext,2,1)as int)=Cast(SUBSTRING(unotext,17,1)as int)) and (Cast(SUBSTRING(unotext,7,2)as int)%5=0) and (Cast(SUBSTRING(unotext,8,1)as int)=Cast(SUBSTRING(unotext,13,1)as int))


insert into #test4 select N'C被测试者' as persoNM, [personid]  from #test1 as t1 where 1=1  and (Cast(SUBSTRING(unotext,11,1)as int)+Cast(SUBSTRING(unotext,17,1)as int)=2*Cast(SUBSTRING(unotext,14,1)as int)) and (Cast(SUBSTRING(unotext,14,1)as int)+Cast(SUBSTRING(unotext,2,1)as int)=2*Cast(SUBSTRING(unotext,17,1)as int)) and (Cast(SUBSTRING(unotext,17,1)as int)=2*Cast(SUBSTRING(unotext,14,1)as int)) and (Cast(SUBSTRING(unotext,14,1)as int)=2*Cast(SUBSTRING(unotext,2,1)as int)) and (Cast(SUBSTRING(unotext,4,1)as int)=Cast(SUBSTRING(unotext,17,1)as int)) and (Cast(SUBSTRING(unotext,11,1)as int)=2*Cast(SUBSTRING(unotext,5,1)as int)) and (Cast(SUBSTRING(unotext,2,1)as int)=2*Cast(SUBSTRING(unotext,5,1)as int)) and (Cast(SUBSTRING(unotext,1,2)as int)%3=0) and (Cast(SUBSTRING(unotext,11,1)as int)=2*Cast(SUBSTRING(unotext,17,1)as int)) and (Cast(SUBSTRING(unotext,11,1)as int)=2*Cast(SUBSTRING(unotext,2,1)as int)) and (Cast(SUBSTRING(unotext,13,2)as int)-Cast(SUBSTRING(unotext,10,2)as int)=1) and (Cast(SUBSTRING(unotext,17,1)as int)=2*Cast(SUBSTRING(unotext,5,1)as int)) and (Cast(SUBSTRING(unotext,2,1)as int)+Cast(SUBSTRING(unotext,8,1)as int)=2*Cast(SUBSTRING(unotext,5,1)as int)) and (Cast(SUBSTRING(unotext,2,1)as int)=Cast(SUBSTRING(unotext,17,1)as int)) and (Cast(SUBSTRING(unotext,7,2)as int)%5=0) and (Cast(SUBSTRING(unotext,8,1)as int)=Cast(SUBSTRING(unotext,13,1)as int))


insert into #test4 select N'D被测试者' as persoNM, [personid]  from #test1 as t1 where 1=1  and (Cast(SUBSTRING(unotext,11,1)as int)+Cast(SUBSTRING(unotext,17,1)as int)=2*Cast(SUBSTRING(unotext,14,1)as int)) and (Cast(SUBSTRING(unotext,14,1)as int)+Cast(SUBSTRING(unotext,2,1)as int)=2*Cast(SUBSTRING(unotext,17,1)as int)) and (Cast(SUBSTRING(unotext,17,1)as int)=2*Cast(SUBSTRING(unotext,14,1)as int)) and (Cast(SUBSTRING(unotext,14,1)as int)=2*Cast(SUBSTRING(unotext,2,1)as int)) and (Cast(SUBSTRING(unotext,4,1)as int)=Cast(SUBSTRING(unotext,17,1)as int)) and (Cast(SUBSTRING(unotext,11,1)as int)=2*Cast(SUBSTRING(unotext,5,1)as int)) and (Cast(SUBSTRING(unotext,2,1)as int)=2*Cast(SUBSTRING(unotext,5,1)as int)) and (Cast(SUBSTRING(unotext,1,2)as int)%3=0) and (Cast(SUBSTRING(unotext,11,1)as int)=2*Cast(SUBSTRING(unotext,17,1)as int)) and (Cast(SUBSTRING(unotext,11,1)as int)=2*Cast(SUBSTRING(unotext,2,1)as int)) and (Cast(SUBSTRING(unotext,13,2)as int)-Cast(SUBSTRING(unotext,10,2)as int)=1) and (Cast(SUBSTRING(unotext,17,1)as int)=2*Cast(SUBSTRING(unotext,5,1)as int)) and (Cast(SUBSTRING(unotext,2,1)as int)+Cast(SUBSTRING(unotext,8,1)as int)=2*Cast(SUBSTRING(unotext,5,1)as int)) and (Cast(SUBSTRING(unotext,2,1)as int)=Cast(SUBSTRING(unotext,17,1)as int)) and (Cast(SUBSTRING(unotext,7,2)as int)%5=0) and (Cast(SUBSTRING(unotext,8,1)as int)=Cast(SUBSTRING(unotext,13,1)as int))

CZP98168 2016-04-05

打赏
举报

因为要用到动态SQL，所有临时表都改成了实体表



if object_id('test3') is not null drop table test3
go 
create table test3([persoNM]  nvarchar(20),[SID] nvarchar(200))
insert test3
select 'A被测试者','D028 D039 D051' union all      
select 'B被测试者','D042 D059' union all       
select 'C被测试者','D032' union all        
select 'D被测试者','D004 D017 D030 D031 D038 D053 D056 D057 D070 D074'
GO

if object_id('test1') is not null drop table test1
go 
create table test1([personid] int,[unotext] nvarchar(20))
insert test1
select 100001,'29011008032003030' union all
select 100002,'19010027025013018' union all
select 100003,'26028011013010012' union all
select 100004,'09004026021019020' union all
select 100005,'23007028010001032' union all
select 100006,'13004007025006010' union all
select 100007,'04030017015031006' union all
select 100008,'01010027026021003' union all
select 100009,'23019009026001021' union all
select 100010,'09008017023014005' union all
select 100011,'30018009022005020' union all
select 100012,'01017024008002013'
GO

if object_id('test2') is not null drop table test2
go 
create table test2([SSID] nvarchar(20),[jytzum] nvarchar(200))
insert test2
select 'D004','Cast(SUBSTRING(unotext,2,1)as int)=2*Cast(SUBSTRING(unotext,5,1)as int)' union all
select 'D017','Cast(SUBSTRING(unotext,1,2)as int)%3=0' union all
select 'D028','Cast(SUBSTRING(unotext,11,1)as int)+Cast(SUBSTRING(unotext,17,1)as int)=2*Cast(SUBSTRING(unotext,14,1)as int)' union all
select 'D030','Cast(SUBSTRING(unotext,11,1)as int)=2*Cast(SUBSTRING(unotext,17,1)as int)' union all
select 'D031','Cast(SUBSTRING(unotext,11,1)as int)=2*Cast(SUBSTRING(unotext,2,1)as int)' union all
select 'D032','Cast(SUBSTRING(unotext,11,1)as int)=2*Cast(SUBSTRING(unotext,5,1)as int)' union all
select 'D038','Cast(SUBSTRING(unotext,13,2)as int)-Cast(SUBSTRING(unotext,10,2)as int)=1' union all
select 'D039','Cast(SUBSTRING(unotext,14,1)as int)+Cast(SUBSTRING(unotext,2,1)as int)=2*Cast(SUBSTRING(unotext,17,1)as int)' union all
select 'D042','Cast(SUBSTRING(unotext,14,1)as int)=2*Cast(SUBSTRING(unotext,2,1)as int)' union all
select 'D051','Cast(SUBSTRING(unotext,17,1)as int)=2*Cast(SUBSTRING(unotext,14,1)as int)' union all
select 'D053','Cast(SUBSTRING(unotext,17,1)as int)=2*Cast(SUBSTRING(unotext,5,1)as int)' union all
select 'D056','Cast(SUBSTRING(unotext,2,1)as int)+Cast(SUBSTRING(unotext,8,1)as int)=2*Cast(SUBSTRING(unotext,5,1)as int)' union all
select 'D057','Cast(SUBSTRING(unotext,2,1)as int)=Cast(SUBSTRING(unotext,17,1)as int)' union all
select 'D059','Cast(SUBSTRING(unotext,4,1)as int)=Cast(SUBSTRING(unotext,17,1)as int)' union all
select 'D070','Cast(SUBSTRING(unotext,7,2)as int)%5=0' union all
select 'D074','Cast(SUBSTRING(unotext,8,1)as int)=Cast(SUBSTRING(unotext,13,1)as int)'
GO

--创建结果表
if object_id('result') is not null drop table result
go 
CREATE TABLE result([persoNM]  nvarchar(20),[personid] int,[unotext] nvarchar(20))
GO
DECLARE @persoNM NVARCHAR(20),@SID NVARCHAR(200)
DECLARE @SQL NVARCHAR(MAX),@WHERE NVARCHAR(MAX)
DECLARE @personid INT,@unotext NVARCHAR(20)

--游标表
DECLARE Test4 CURSOR FOR
SELECT *
FROM test3

OPEN Test4
FETCH NEXT FROM Test4 INTO @persoNM,@SID
WHILE @@FETCH_STATUS = 0
BEGIN
  SET @WHERE = ''
  SET @personid = NULL
  SET @unotext = NULL
  SELECT @WHERE = @WHERE + CASE WHEN @WHERE = '' THEN '(' + ISNULL(jytzum,'') + ')' ELSE 'AND (' + ISNULL(jytzum,'') + ')' END
  FROM test2
  WHERE CHARINDEX(SSID,@SID) > 0
  
  IF @WHERE = ''
    SET @WHERE = '1 < 0'
    
  SET @SQL = 'SELECT @P1 = personid,@P2 = unotext FROM test1 where ' + @WHERE
  EXEC Sp_executesql @SQL,N'@P1 int output,@P2 NVARCHAR(20) output',@P1 = @personid OUTPUT,@P2 = @unotext OUTPUT
  
  INSERT INTO result
          ( persoNM, personid, unotext )
  VALUES  ( @persoNM, -- persoNM - nvarchar(20)
            @personid, -- personid - int
            @unotext  -- unotext - nvarchar(20)
            )
  FETCH NEXT FROM Test4 INTO @persoNM,@SID
END
CLOSE Test4
DEALLOCATE Test4

SELECT * FROM result

DROP TABLE test1
DROP TABLE test2
DROP TABLE test3
DROP TABLE result

/* --结果 persoNM personid unotext -------------------- ----------- -------------------- A被测试者 100005 23007028010001032 B被测试者 100006 13004007025006010 C被测试者 100001 29011008032003030 D被测试者 NULL NULL */

唐诗三百首 2016-04-05

打赏
举报

请问希望结果"对应的基因信息"是指什么? 请提供明确的表结构和样例数据.

CZP98168 2016-04-05

打赏
举报

因此实现的步骤是： 1、从#test3中分解出测试者的基因特征 2、根据分解出的基因特征从#test2得到查询条件 3、根据查询条件用动态SQL语句从#test1中得到符合条件的基因信息

CZP98168 2016-04-05

打赏
举报

按我的理解，你要的结果是这样的，以C被测试者为例：


SELECT * FROM #test1 
WHERE Cast(SUBSTRING(unotext,11,1)as int)=2*Cast(SUBSTRING(unotext,5,1)as int)

/*
--结果
personid    unotext
----------- --------------------
100001      29011008032003030
*/

RICHEER COCA 2016-04-05

打赏
举报

期待ing......

结贴也是必须滴

RICHEER COCA 2016-04-05

打赏
举报

[quote=引用 17 楼 ap0405140 的回复:[/quote] 非常感谢 17 楼 ap0405140 的回复，正解，大神太强大了，学习受益啊！继续请教测试：在基因信息数据库里取 (100000 行受影响)到#test1 取测试者的基因特征(1810 行受影响)到#test3 取基因特征库里的D001-D077(77 行受影响)到#test2 执行结果(2185679 行受影响)时间2:44 如果在基因信息数据库里取1178009行数据到#test1，，每一行的数据都是基因信息数据，其他条件不变，(1810 行受影响)到#test3，(77 行受影响)到#test2 据此推算，跑完可能需要20分钟左右的时间，如果所有的数据翻倍的情况，时间会更长，请问代码能进一步优化吗？

RICHEER COCA 2016-04-05

打赏
举报

引用 8 楼 ch21st 的回复:

举个生成的动态代码例子，如果觉得不对，你可以修改下：

数据量少，用手工查询并验证，数据量大则无法完成啊


if object_id('tempdb..#test4') is not null drop table #test4
go 
create table #test4 ([persoNM]  nvarchar(20),[personid] nvarchar(20),[unotext] nvarchar(20))

--'A被测试者','D028 D039 D051' 
insert into #test4 select N'A被测试者' as persoNM, [personid],[unotext]  from #test1 as t1  
WHERE 1=1
and	Cast(SUBSTRING(unotext,11,1) as int)+Cast(SUBSTRING(unotext,17,1) as int)=2*Cast(SUBSTRING(unotext,14,1) as int)---D028
and	Cast(SUBSTRING(unotext,14,1) as int)+Cast(SUBSTRING(unotext,2,1) as int)=2*Cast(SUBSTRING(unotext,17,1) as int)---D039
and	Cast(SUBSTRING(unotext,17,1) as int)=2*Cast(SUBSTRING(unotext,14,1) as int)---D051

--'B被测试者','D042 D059'
insert into #test4 select N'B被测试者' as persoNM, [personid],[unotext]  from #test1 as t1 
where 1=1  
and	Cast(SUBSTRING(unotext,14,1) as int)=2*Cast(SUBSTRING(unotext,2,1) as int)---D042
and	Cast(SUBSTRING(unotext,4,1) as int)=Cast(SUBSTRING(unotext,17,1) as int)---D059

--'C被测试者','D032' 
insert into #test4 select N'C被测试者' as persoNM, [personid],[unotext]  from #test1 as t1 
where 1=1
and	Cast(SUBSTRING(unotext,11,1) as int)=2*Cast(SUBSTRING(unotext,5,1) as int)---D032  

--'D被测试者','D003 D013 D017 D026 D028 D038 D040 D048 D060'
insert into #test4 select N'D被测试者' as persoNM, [personid],[unotext]  from #test1 as t1 
where 1=1  
and	Cast(SUBSTRING(unotext, 2,1) as int)=2*Cast(SUBSTRING(unotext,17,1) as int)---D003
and	Cast(SUBSTRING(unotext, 8,1) as int)=2*Cast(SUBSTRING(unotext,17,1) as int)---D013
and	Cast(SUBSTRING(unotext,1,2) as int)%3=0---D017
and	Cast(SUBSTRING(unotext,10,2) as int)+Cast(SUBSTRING(unotext,16,2) as int)=2*Cast(SUBSTRING(unotext,13,2) as int)---D026
and	Cast(SUBSTRING(unotext,11,1) as int)+Cast(SUBSTRING(unotext,17,1) as int)=2*Cast(SUBSTRING(unotext,14,1) as int)---D028
and	Cast(SUBSTRING(unotext,13,2) as int)-Cast(SUBSTRING(unotext,10,2) as int)=1---D038
and	Cast(SUBSTRING(unotext,14,1) as int)=2*Cast(SUBSTRING(unotext,11,1) as int)---D040
and	Cast(SUBSTRING(unotext,16,2) as int)-Cast(SUBSTRING(unotext,13,2) as int)=1---D048
and	Cast(SUBSTRING(unotext,4,2) as int)%3=0---D060

SELECT * FROM #test4 order by persoNM 

/*
--结果
persoNM    personid   unotext
-------------------- ----------- --------------------
A被测试者	100010	23007028010001032
B被测试者	100012	13004007025006010
C被测试者	100002	29011008032003030
C被测试者	100025	31033032006015026
D被测试者	100001	06009016021022023
D被测试者	100003	06009016031032033
D被测试者	100005	06009026031032033
D被测试者	100007	06012016021022023
D被测试者	100009	06012016031032033
D被测试者	100011	06012026031032033
D被测试者	100013	06015016021022023
D被测试者	100015	06015016031032033
D被测试者	100017	06015026031032033
D被测试者	100019	06018026031032033
D被测试者	100021	06021026031032033
D被测试者	100023	06024026031032033 
*/

唐诗三百首 2016-04-05

打赏
举报


-- 建结果表
create table #result
(persoNM nvarchar(20),
 personid int,
 unotext nvarchar(20))


-- 计算
declare @persoNM nvarchar(20),@SID nvarchar(200),@tsql nvarchar(max)
declare ap scroll cursor for select persoNM,SID from #test3

open ap
fetch first from ap into @persoNM,@SID
while(@@fetch_status<>-1)
begin
  select @tsql=null
  select @tsql=isnull(@tsql+' and ','')+c.jytzum
    from (select @SID 'SID') a
    inner join master.dbo.spt_values b on b.number between 1 and len(a.SID) 
                                                                    and substring(N' '+a.SID,b.number,1)=N' '
    inner join #test2 c on substring(a.SID,b.number,charindex(' ',a.SID+' ',b.number)-b.number)=c.SSID
    where b.[type]=N'P'
  
  select @tsql=N'insert into #result(persoNM,personid,unotext) '
                      +N'   select  N'''+@persoNM+''',[personid],[unotext] '
                      +N'   from #test1 t '
                      +N'   where '+@tsql

  exec(@tsql)

  fetch next from ap into @persoNM,@SID
end

close ap
deallocate ap

update t
 set t.persoNM=N''
 from (select *,row_number() over(partition by persoNM order by getdate()) 'rn' 
           from #result) t
 where t.rn>=2


-- 查询结果
select * from #result

/*
persoNM                                            personid                                           unotext
-------------------------------------------------- -------------------------------------------------- --------------------------------------------------
A被测试者                                              100010                                             23007028010001032
B被测试者                                              100012                                             13004007025006010
C被测试者                                              100002                                             29011008032003030
                                                                100025                                             31033032006015026
D被测试者                                              100001                                             06009016021022023
                                                                100003                                             06009016031032033
                                                                100005                                             06009026031032033
                                                                100007                                             06012016021022023
                                                                100009                                             06012016031032033
                                                                100011                                             06012026031032033
                                                                100013                                             06015016021022023
                                                                100015                                             06015016031032033
                                                                100017                                             06015026031032033
                                                                100019                                             06018026031032033
                                                                100021                                             06021026031032033
                                                                100023                                             06024026031032033

(16 row(s) affected)
*/

RICHEER COCA 2016-04-05

打赏
举报

引用 12 楼 CZP98168 的回复:

7楼的代码应该能达到你要的结果吧？


7#结果是
/*
--结果
persoNM     personid  unotext
-------------------- ----------- --------------------
A被测试者	100010	23007028010001032
B被测试者	100012	13004007025006010
C被测试者	100025	31033032006015026
D被测试者	100023	06024026031032033
*/

也很感谢楼主盼望的正解是


/*
--需要的结果
persoNM     personid    unotext
-------------------- ----------- --------------------
A被测试者	100010		23007028010001032
B被测试者	100012		13004007025006010
C被测试者	100002		29011008032003030
			100025		31033032006015026
D被测试者	100001		06009016021022023
			100003		06009016031032033
			100005		06009026031032033
			100007		06012016021022023
			100009		06012016031032033
			100011		06012026031032033
			100013		06015016021022023
			100015		06015016031032033
			100017		06015026031032033
			100019		06018026031032033
			100021		06021026031032033
			100023		06024026031032033 
*/

RICHEER COCA 2016-04-05

打赏
举报

引用 13 楼 ap0405140 的回复:

C被测试者的"D032", 在#test2表中找不到喔?

#14补充了D028 D039 D051 D042 D059 D032,楼主慌忙中大意，很抱歉。补充说明：#test3表中SID里多个特征的关系是 AND的关系，继续请教，谢谢

唐诗三百首 2016-04-05

打赏
举报

C被测试者的"D032", 在#test2表中找不到喔?

RICHEER COCA 2016-04-05

打赏
举报

订正基因特征#test2


if object_id('tempdb..#test2') is not null drop table #test2
go 
create table #test2([SSID] nvarchar(20),[jytzum] nvarchar(200))
insert #test2  --  select * from #test2
select 'D003','Cast(SUBSTRING(unotext,2,1) as int)=2*Cast(SUBSTRING(unotext,17,1) as int)' union all
select 'D013','Cast(SUBSTRING(unotext,8,1) as int)=2*Cast(SUBSTRING(unotext,17,1) as int)' union all
select 'D017','Cast(SUBSTRING(unotext,1,2) as int)%3=0' union all
select 'D026','Cast(SUBSTRING(unotext,10,2) as int)+Cast(SUBSTRING(unotext,16,2) as int)=2*Cast(SUBSTRING(unotext,13,2) as int)' union all
select 'D028','Cast(SUBSTRING(unotext,11,1) as int)+Cast(SUBSTRING(unotext,17,1) as int)=2*Cast(SUBSTRING(unotext,14,1) as int)' union all
select 'D032','Cast(SUBSTRING(unotext,11,1) as int)=2*Cast(SUBSTRING(unotext,5,1) as int)' union all
select 'D038','Cast(SUBSTRING(unotext,13,2) as int)-Cast(SUBSTRING(unotext,10,2) as int)=1' union all
select 'D039','Cast(SUBSTRING(unotext,14,1) as int)+Cast(SUBSTRING(unotext,2,1) as int)=2*Cast(SUBSTRING(unotext,17,1) as int)' union all
select 'D040','Cast(SUBSTRING(unotext,14,1) as int)=2*Cast(SUBSTRING(unotext,11,1) as int)' union all
select 'D042','Cast(SUBSTRING(unotext,14,1) as int)=2*Cast(SUBSTRING(unotext,2,1) as int)' union all
select 'D048','Cast(SUBSTRING(unotext,16,2) as int)-Cast(SUBSTRING(unotext,13,2) as int)=1' union all
select 'D051','Cast(SUBSTRING(unotext,17,1) as int)=2*Cast(SUBSTRING(unotext,14,1) as int)' union all
select 'D059','Cast(SUBSTRING(unotext,4,1) as int)=Cast(SUBSTRING(unotext,17,1) as int)' union all
select 'D060','Cast(SUBSTRING(unotext,4,2) as int)%3=0'
GO

RICHEER COCA 2016-04-05

打赏
举报

引用 10 楼 CZP98168 的回复:

test3表中SID里多个特征的关系你要说明白，是简单AND的关系还是OR关系，还是其他的关系

谢谢10 楼 CZP98168 的回复 test3表中SID里多个特征的关系AND的关系,例如：

--要的结果是这样的，以A被测试者为例：
A被测试者的基因特征是 D028 D039 D051
SELECT * FROM #test1 
WHERE 1=1
and	Cast(SUBSTRING(unotext,11,1) as int)+Cast(SUBSTRING(unotext,17,1) as int)=2*Cast(SUBSTRING(unotext,14,1) as int)---D028
and	Cast(SUBSTRING(unotext,14,1) as int)+Cast(SUBSTRING(unotext,2,1) as int)=2*Cast(SUBSTRING(unotext,17,1) as int)---D039
and	Cast(SUBSTRING(unotext,17,1) as int)=2*Cast(SUBSTRING(unotext,14,1) as int)---D051
/*
--结果
personUM	personid    unotext
----------- --------------------
A被测试者	100010		23007028010001032
*/

CZP98168 2016-04-05

打赏
举报

7楼的代码应该能达到你要的结果吧？

CZP98168 2016-04-05

打赏
举报

test3表中SID里多个特征的关系你要说明白，是简单AND的关系还是OR关系，还是其他的关系，如果是OR关系，把其中的代码改成下面的就可以：


  IF @WHERE = ''
    SET @WHERE = '1 < 0'
    
  --SET @SQL = 'SELECT @P1 = personid,@P2 = unotext FROM test1 where ' + @WHERE
  SET @SQL = 'insert into result ( persoNM, personid, unotext ) select @P1 as persoNM,personid,unotext from test1 where ' + @WHERE
  --EXEC Sp_executesql @SQL,N'@P1 int output,@P2 NVARCHAR(20) output',@P1 = @personid OUTPUT,@P2 = @unotext OUTPUT
  EXEC Sp_executesql @SQL,N'@P1 NVARCHAR(20)',@P1 = @persoNM 
  
  /*
  INSERT INTO result
          ( persoNM, personid, unotext )
  VALUES  ( @persoNM, -- persoNM - nvarchar(20)
            @personid, -- personid - int
            @unotext  -- unotext - nvarchar(20)
            )*/
  FETCH NEXT FROM Test4 INTO @persoNM,@SID

RICHEER COCA 2016-04-05

打赏
举报

引用 6 楼 ap0405140 的回复:

请问希望结果"对应的基因信息"是指什么? 请提供明确的表结构和样例数据.


if object_id('#test3') is not null drop table #test3
go 
create table #test3([persoNM]  nvarchar(20),[SID] nvarchar(200))
insert #test3  -- select * from #test3  
select 'A被测试者','D028 D039 D051' union all      
select 'B被测试者','D042 D059' union all       
select 'C被测试者','D032' union all        
select 'D被测试者','D003 D013 D017 D026 D028 D038 D040 D048 D060'
GO

if object_id('#test1') is not null drop table #test1
go 
create table #test1([personid] int,[unotext] nvarchar(20))
insert #test1  ---  select * from #test1 
select 100001,'06009016021022023' union all 
select 100002,'29011008032003030' union all
select 100003,'06009016031032033' union all
select 100004,'26028011013010012' union all
select 100005,'06009026031032033' union all
select 100006,'19010027025013018' union all
select 100007,'06012016021022023' union all
select 100008,'09004026021019020' union all
select 100009,'06012016031032033' union all
select 100010,'23007028010001032' union all
select 100011,'06012026031032033' union all
select 100012,'13004007025006010' union all
select 100013,'06015016021022023' union all
select 100014,'04030017015031006' union all
select 100015,'06015016031032033' union all
select 100016,'01010027026021003' union all
select 100017,'06015026031032033' union all
select 100018,'23019009026001021' union all
select 100019,'06018026031032033' union all
select 100020,'09008017023014005' union all
select 100021,'06021026031032033' union all
select 100022,'30018009022005020' union all
select 100023,'06024026031032033' union all
select 100024,'12011030004005032' union all
select 100025,'31033032006015026'
GO

if object_id('#test2') is not null drop table #test2
go 
create table #test2([SSID] nvarchar(20),[jytzum] nvarchar(200))
insert #test2  --  select * from #test2
select 'D003','Cast(SUBSTRING(unotext,2,1) as int)=2*Cast(SUBSTRING(unotext,17,1) as int)' union all
select 'D013','Cast(SUBSTRING(unotext,8,1) as int)=2*Cast(SUBSTRING(unotext,17,1) as int)' union all
select 'D017','Cast(SUBSTRING(unotext,1,2) as int)%3=0' union all
select 'D026','Cast(SUBSTRING(unotext,10,2) as int)+Cast(SUBSTRING(unotext,16,2) as int)=2*Cast(SUBSTRING(unotext,13,2) as int)' union all
select 'D028','Cast(SUBSTRING(unotext,11,1) as int)+Cast(SUBSTRING(unotext,17,1) as int)=2*Cast(SUBSTRING(unotext,14,1) as int)' union all
select 'D038','Cast(SUBSTRING(unotext,13,2) as int)-Cast(SUBSTRING(unotext,10,2) as int)=1' union all
select 'D040','Cast(SUBSTRING(unotext,14,1) as int)=2*Cast(SUBSTRING(unotext,11,1) as int)' union all
select 'D048','Cast(SUBSTRING(unotext,16,2) as int)-Cast(SUBSTRING(unotext,13,2) as int)=1' union all
select 'D060','Cast(SUBSTRING(unotext,4,2) as int)%3=0'
GO

执行5#大神的代码，借此感谢，同时的确是在学习提高工程中。受益匪浅啊，继续言表


--创建结果表
if object_id('result') is not null drop table result
go 
CREATE TABLE result([persoNM]  nvarchar(20),[personid] int,[unotext] nvarchar(20))
GO
DECLARE @persoNM NVARCHAR(20),@SID NVARCHAR(200)
DECLARE @SQL NVARCHAR(MAX),@WHERE NVARCHAR(MAX)
DECLARE @personid INT,@unotext NVARCHAR(20)

--游标表
DECLARE Test4 CURSOR FOR
SELECT *
FROM test3

OPEN Test4
FETCH NEXT FROM Test4 INTO @persoNM,@SID
WHILE @@FETCH_STATUS = 0
BEGIN
  SET @WHERE = ''
  SET @personid = NULL
  SET @unotext = NULL
  SELECT @WHERE = @WHERE + CASE WHEN @WHERE = '' THEN '(' + ISNULL(jytzum,'') + ')' ELSE 'AND (' + ISNULL(jytzum,'') + ')' END
  FROM test2
  WHERE CHARINDEX(SSID,@SID) > 0
  
  IF @WHERE = ''
    SET @WHERE = '1 < 0'
    
  SET @SQL = 'SELECT @P1 = personid,@P2 = unotext FROM test1 where ' + @WHERE
  EXEC Sp_executesql @SQL,N'@P1 int output,@P2 NVARCHAR(20) output',@P1 = @personid OUTPUT,@P2 = @unotext OUTPUT
  
  INSERT INTO result
          ( persoNM, personid, unotext )
  VALUES  ( @persoNM, -- persoNM - nvarchar(20)
            @personid, -- personid - int
            @unotext  -- unotext - nvarchar(20)
            )
  FETCH NEXT FROM Test4 INTO @persoNM,@SID
END
CLOSE Test4
DEALLOCATE Test4

SELECT * FROM result  
/*
--结果
persoNM              personid    unotext
-------------------- ----------- --------------------
A被测试者	100023	06024026031032033
B被测试者	NULL	NULL
C被测试者	NULL	NULL
D被测试者	100023	06024026031032033 
*/

谢谢楼上大神们的帮助，先说神感谢并借用5#的表述，需要实现的步骤是： 1、从#test3中分解出测试者的基因特征 2、根据分解出的基因特征从#test2得到查询条件 3、根据查询条件用动态SQL语句从#test1中得到符合条件的基因信息楼主盼望的正解是


/*
--需要的结果
persoNM              personid    unotext
-------------------- ----------- --------------------
A被测试者	100010		23007028010001032
B被测试者	100012		13004007025006010
C被测试者	100002		29011008032003030
			100025		31033032006015026
D被测试者	100001		06009016021022023
			100003		06009016031032033
			100005		06009026031032033
			100007		06012016021022023
			100009		06012016031032033
			100011		06012026031032033
			100013		06015016021022023
			100015		06015016031032033
			100017		06015026031032033
			100019		06018026031032033
			100021		06021026031032033
			100023		06024026031032033 
*/

RICHEER COCA 2016-04-04

打赏
举报

更正：被测试者名单



--> 建立数据表#test3 --数据表#test3里有'SID'字段记录（每一行的数据都是每一位被测试者基因特征）

if object_id('tempdb.dbo.#test3') is not null drop table #test3
go 
create table #test3([persoNM]  nvarchar(20),[SID] nvarchar(200))
insert #test3
select 'A被测试者','D028 D039 D051' union all      
select 'B被测试者','D042 D059' union all       
select 'C被测试者','D032' union all        
select 'D被测试者','D004 D017 D030 D031 D038 D053 D056 D057 D070 D074'

RICHEER COCA 2016-04-04

打赏
举报

补充：被测试者名单



--> 建立数据表#test3 --数据表#test3里有'unotext'字段记录（一共有1178009行数据，每一行的数据都是基因信息数据）

if object_id('tempdb.dbo.#test3') is not null drop table #test3
go 
create table #test3([persoNM] int,[SID] nvarchar(20))
insert #test3
select 'A被测试者','D028 D039 D051' union all      
select 'B被测试者','D042 D059' union all       
select 'C被测试者','D032' union all        
select 'D被测试者','D004 D017 D030 D031 D038 D053 D056 D057 D070 D074'

RICHEER COCA 2016-04-04

打赏
举报

更正数据表#test1



--> 建立数据表#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,'29011008032003030' union all
select 100002,'19010027025013018' union all
select 100003,'26028011013010012' union all
select 100004,'09004026021019020' union all
select 100005,'23007028010001032' union all
select 100006,'13004007025006010' union all
select 100007,'04030017015031006' union all
select 100008,'01010027026021003' union all
select 100009,'23019009026001021' union all
select 100010,'09008017023014005' union all
select 100011,'30018009022005020' union all
select 100012,'01017024008002013'

1.什么是SNP和SSLP？SNP：即单核苷酸多态性，是由于基因组中等位位点上单个核苷酸改变而导致的核酸序列多态性(Polymorphism)。SSLP：简单序列长度多态性，是一系列不同长度的重复序列，包括卫星DNA，小卫星，微卫星（STR）。2．知识整理：一．基因组介绍1，Gene: A DNA segment containing biological information and hence coding for an RNA and/or polypeptide molecule.Genome:

读书的好处不用说，坚持下来真不易！尝试过各种方式，收听喜马讲书，微信读书，购买读书课程。本篇不定时更新，记录读书摘要和心得感受，欢迎交流！ 20181023 第三十本《娱乐至死》作者：尼尔波兹曼 1. 娱乐为什么能改变世界 1.1 媒介的定义概念上，承载信息或内容的容器；传播上，用来传播信息的技术设备，人眼睛和耳朵的延伸；媒介即隐喻，如手表是了解时间的媒介；媒介即认识论，钟表以24小...

一直想整理个书单，譬如关于EA(企业架构)、ABCD(人工智能、大数据、云计算、设备)、逻辑、思维、沟通、影响力、领导力、管理、绩效(胜任力)、战略、商业方面的书单，这里看到了一篇整理的比较好的书单，推荐给大家~ 用这篇文章先做个备忘吧，后续我也会按主题发一个系列书单出来，我先开个头吧，譬如美团的四大名著，大家有比较好的书单可以留言补充，多多指教~ 以下内容转载自https://blog.csdn.net/sinat_35630008/article/details/82771590?utm_medium=

★考核知识点: 信息与数据附1.1.1（考核知识点解释）：计算机中信息一般有4种形态：数字、文本、声音、图像。附1.1.2（考核知识点解释）信息资源（Information Resources）：（1）可供利用并产生效益、与社会生产和活动有关的各种文字、数字、音像、图表、语言等一切信息的总称（2）无限的、可再生的、可共享的；（3）其开发利用可大大减少材料和能源的消耗，减少污染。附1.1.3（考核知识点解释）信息是反应客观世界中各种事物特征和变化的知识，是数据加工的结果，是

遗传算法(Genetic Algorithm，GA)最早是由美国的John holland于20世纪70年代提出，该算法是根据大自然中生物体进化规律而设计提出的。是模拟达尔文生物进化论的自然选择和遗传学机理的生物进化过程的计算模型，是一种通过模拟自然进化过程搜索最优解的方法。遗传算法通过数学的方式，利用计算机仿真运算，将问题的求解过程转换成类似生物进化中的染色体基因的交叉、变异等过程。在求解较为复杂的组合优化问题时，相对一些常规的优化算法，通常能够较快地获得较好的优化结果。