22,209
社区成员
发帖
与我相关
我的任务
分享SQL组合数参与多语句的查询
--AA BB CC DD EE FF分别代表基因优化算法的代码，见表 #test1
-->步骤一 建立数据表#test1 表#test1里有AlgorithmDB'字段记录（一共有5行数据，每一行的数据都是基因优化算法）
if object_id('tempdb.dbo.#test1') is not null drop table #test1
go
create table #test1([RCid] nvarchar(5),[AlgorithmDB] nvarchar(200))
insert #test1 -- select * from #test1
select 'AA','Cast(SUBSTRING(Unotext,1,1) as int)+Cast(SUBSTRING(Unotext,2,1) as int)' union all
select 'BB ','Cast(SUBSTRING(Unotext,4,1) as int)+Cast(SUBSTRING(Unotext,5,1) as int)' union all
select 'CC ','Cast(SUBSTRING(Unotext,7,1) as int)+Cast(SUBSTRING(Unotext,8,1) as int)' union all
select 'DD ','Cast(SUBSTRING(Unotext,10,1) as int)+Cast(SUBSTRING(Unotext,11,1) as int)' union all
select 'EE ','Cast(SUBSTRING(Unotext,13,1) as int)+Cast(SUBSTRING(Unotext,14,1) as int)' union all
select 'FF ','Cast(SUBSTRING(Unotext,16,1) as int)+Cast(SUBSTRING(Unotext,17,1) as int)'
-->步骤二 AA BB CC DD EE FF分别代表基因优化算法的代码(实际数据有60310多个，现只取6个举例），想通过SQL代码实现以下结果#result1
--执行步骤二：分别从AA BB CC DD EE FF取2个、3个、4个、5个、6个相等的关系【实际数据有n个数里取出m个数的组合是n*(n-1)*...*(n-m+1)/m*(m-1)*...2*1的 总和】
-- select * from #result1 可得到查询结果#result1（建立数据表#test2 ）
/*
SSID AlgorithmDB
-------------------------------------
D001 AA=BB
D002 AA=CC
D003 BB=CC
D004 AA=DD
D005 BB=DD
D006 CC=DD
D007 AA=EE
D008 BB=EE
D009 CC=EE
D010 DD=EE
D011 AA=FF
D012 BB=FF
D013 CC=FF
D014 DD=FF
D015 EE=FF
D016 AA=BB=CC
D017 AA=BB=DD
D018 AA=BB=EE
D019 AA=BB=FF
D020 AA=CC=DD
D021 AA=CC=EE
D022 AA=CC=FF
D023 AA=DD=EE
D024 AA=DD=FF
D025 AA=EE=FF
D026 BB=CC=DD
D027 BB=CC=EE
D028 BB=CC=FF
D029 BB=DD=EE
D030 BB=DD=FF
D031 BB=EE=FF
D032 CC=DD=EE
D033 CC=DD=FF
D034 CC=EE=FF
D035 DD=EE=FF
D036 AA=BB=CC=DD
D037 AA=BB=CC=EE
D038 AA=BB=CC=FF
D039 AA=BB=DD=EE
D040 AA=BB=DD=FF
D041 AA=BB=EE=FF
D042 AA=CC=DD=EE
D043 AA=CC=DD=FF
D044 AA=CC=EE=FF
D045 AA=DD=EE=FF
D046 BB=CC=DD=EE
D047 BB=CC=DD=FF
D048 BB=CC=EE=FF
D049 BB=DD=EE=FF
D050 CC=DD=EE=FF
D051 AA=BB=CC=DD=EE
D052 AA=BB=CC=DD=FF
D053 AA=BB=CC=EE=FF
D054 AA=BB=DD=EE=FF
D055 AA=CC=DD=EE=FF
D056 BB=CC=DD=EE=FF
D057 AA=BB=CC=DD=EE=FF
(57 row(s) affected)
*/
-->步骤3 建立数据表#test3 --数据表#test3里有'unotext'字段记录（一共有1378009行数据，每一行的数据都是基因信息数据，现在取14行举例）
if object_id('tempdb.dbo.#test11') is not null drop table #test11
go
create table #test11([personid] int,[unotext] nvarchar(20))
insert #test11 -- select * from #test11
select 100001,'26028011013010012' union all
select 100002,'09004026021019020' union all
select 100003,'23007028010001032' union all
select 100004,'13004007025006010' union all
select 100005,'04030017015031006' union all
select 100006,'01010027026021003' union all
select 100007,'23019009026001021' union all
select 100008,'09008017023014005' union all
select 100009,'30018009022005020' union all
select 100010,'01017024008002013' union all
select 100011,'12011030004005032' union all
select 100012,'17002012016027030' union all
select 100013,'23013008032017021' union all
select 100014,'03021007031008005'
---->步骤4 计算得到结果（感谢ch21st 道素，借用如下）
if object_id('tempdb.dbo.#test3') is not null drop table #test3
go
CREATE TABLE #test3([personid] int,[unotext] nvarchar(20),SID VARCHAR(100))
GO
TRUNCATE TABLE #test3
DECLARE @formular VARCHAR(200)
DECLARE @sid VARCHAR(20)
DECLARE @sql VARCHAR(max)
DECLARE curFormular CURSOR FOR (SELECT [personid] ,[jytzum] FROM #result1 AS t)
OPEN curFormular
FETCH NEXT FROM curFormular INTO @sid,@formular;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @sql='MERGE INTO #test3 AS tg USING
(
SELECT t.*,'''+@sid+''' as sid FROM #test11 AS t WHERE '+@formular+'
) AS s ON s.[personid]=tg.[personid]
WHEN matched THEN
UPDATE set tg.sid=tg.sid+'' ''+s.sid
when not matched then
insert values(s.[personid],s.[unotext],s.sid)
;'
PRINT @sql
EXEC (@sql)
FETCH NEXT FROM curFormular INTO @sid,@formular;
END
CLOSE curFormular
DEALLOCATE curFormular
SELECT * FROM #test3 AS t
ORDER BY personid
-- 想要的查询结果
/*
persoNM unotext Sid
-------------------------------------
100003 23007028010001032 D010 D011
100004 13004007025006010 D001 D006
100005 04030017015031006 D007 D014
100006 01010027026021003 D001 D015
100008 09008017023014005 D003 D010 D014 D015
100009 30018009022005020 D003
100010 01017024008002013 D005
100011 12011030004005032 D002 D015
100012 17002012016027030 D013
100013 23013008032017021 D004 D009
100014 03021007031008005 D001
(11 row(s) affected)
*/
问题：-->步骤2 不知如何用代码完成，请大神帮助指点-->步骤2与步骤4的完美结合，谢谢。
...全文
请发表友善的回复…
发表回复
道素 2016-04-15
- 打赏
- 举报
验证下结果数量：
if object_id('tempdb.dbo.#stat_result') is not null drop table #stat_result
go
create table #stat_result(t varchar(100),[SIDQty] int)
DECLARE @MaxItemCount int =15
DECLARE @iCount int,@i int=0,@j int=0
DECLARE @sql VARCHAR(max)
set @i=0
WHILE @i<@MaxItemCount
BEGIN
SET @sql='/*insert into #result2*/'+char(10)+'SELECT '
set @j=@i+1
WHILE @j>0
BEGIN
set @sql=@sql+case when @j=@i+1 then ' t' else '+'' ''+ t' end +convert(varchar,@j)+'.persoNM'
SET @j=@j-1
END
SET @sql=@sql+' as sid from #result1 as t1'+char(10)
set @j=1
WHILE @j<=@i
BEGIN
set @sql=@sql+' CROSS APPLY (select * from #result1 as tt'+convert(varchar,@j+1)+' where tt'+convert(varchar,@j+1)+'.persoNM<t'+convert(varchar,@j)+'.persoNM) t'+convert(varchar,@j+1)+char(10)
SET @j=@j+1
END
set @sql='insert into #stat_result select '''+convert(varchar,@i+1)+' of 15'' as a,count(*) as SIDQty from ('+@sql+') tt'
print @sql
EXEC(@sql)
SET @i=@i+1
END
select * from #stat_result
/*
1 of 15 15
2 of 15 105
3 of 15 455
4 of 15 1365
5 of 15 3003
6 of 15 5005
7 of 15 6435
8 of 15 6435
9 of 15 5005
10 of 15 3003
11 of 15 1365
12 of 15 455
13 of 15 105
14 of 15 15
15 of 15 1
*/
道素 2016-04-15
- 打赏
- 举报
和前面生成组合的方式一样，比如：
select t3.persoNM+' '+ t2.persoNM+' '+t1.persoNM from #result1 as t1
CROSS APPLY (select * from #result1 as tt2 where tt2.persoNM<t1.persoNM) t2
CROSS APPLY (select * from #result1 as tt3 where tt3.persoNM<t2.persoNM) t3
/* 455各
D001 D002 D003
D001 D002 D004
D001 D003 D004
D002 D003 D004
D001 D002 D005
D001 D003 D005
D002 D003 D005
D001 D004 D005
D002 D004 D005
D003 D004 D005
D001 D002 D006
D001 D003 D006
D002 D003 D006
D001 D004 D006
D002 D004 D006
D003 D004 D006
D001 D005 D006
D002 D005 D006
D003 D005 D006
D004 D005 D006
D001 D002 D007
D001 D003 D007
D002 D003 D007
D001 D004 D007
D002 D004 D007
D003 D004 D007
D001 D005 D007
D002 D005 D007
D003 D005 D007
D004 D005 D007
D001 D006 D007
D002 D006 D007
D003 D006 D007
D004 D006 D007
D005 D006 D007
D001 D002 D008
D001 D003 D008
D002 D003 D008
D001 D004 D008
D002 D004 D008
D003 D004 D008
D001 D005 D008
D002 D005 D008
D003 D005 D008
D004 D005 D008
D001 D006 D008
D002 D006 D008
D003 D006 D008
D004 D006 D008
D005 D006 D008
D001 D007 D008
D002 D007 D008
D003 D007 D008
D004 D007 D008
D005 D007 D008
D006 D007 D008
D001 D002 D009
D001 D003 D009
D002 D003 D009
D001 D004 D009
D002 D004 D009
D003 D004 D009
D001 D005 D009
D002 D005 D009
D003 D005 D009
D004 D005 D009
D001 D006 D009
D002 D006 D009
D003 D006 D009
D004 D006 D009
D005 D006 D009
D001 D007 D009
D002 D007 D009
D003 D007 D009
D004 D007 D009
D005 D007 D009
D006 D007 D009
D001 D008 D009
D002 D008 D009
D003 D008 D009
D004 D008 D009
D005 D008 D009
D006 D008 D009
D007 D008 D009
D001 D002 D010
D001 D003 D010
D002 D003 D010
D001 D004 D010
D002 D004 D010
D003 D004 D010
D001 D005 D010
D002 D005 D010
D003 D005 D010
D004 D005 D010
D001 D006 D010
D002 D006 D010
D003 D006 D010
D004 D006 D010
D005 D006 D010
D001 D007 D010
D002 D007 D010
D003 D007 D010
D004 D007 D010
D005 D007 D010
D006 D007 D010
D001 D008 D010
D002 D008 D010
D003 D008 D010
D004 D008 D010
D005 D008 D010
D006 D008 D010
D007 D008 D010
D001 D009 D010
D002 D009 D010
D003 D009 D010
D004 D009 D010
D005 D009 D010
D006 D009 D010
D007 D009 D010
D008 D009 D010
D001 D002 D011
D001 D003 D011
D002 D003 D011
D001 D004 D011
D002 D004 D011
D003 D004 D011
D001 D005 D011
D002 D005 D011
D003 D005 D011
D004 D005 D011
D001 D006 D011
D002 D006 D011
D003 D006 D011
D004 D006 D011
D005 D006 D011
D001 D007 D011
D002 D007 D011
D003 D007 D011
D004 D007 D011
D005 D007 D011
D006 D007 D011
D001 D008 D011
D002 D008 D011
D003 D008 D011
D004 D008 D011
D005 D008 D011
D006 D008 D011
D007 D008 D011
D001 D009 D011
D002 D009 D011
D003 D009 D011
D004 D009 D011
D005 D009 D011
D006 D009 D011
D007 D009 D011
D008 D009 D011
D001 D010 D011
D002 D010 D011
D003 D010 D011
D004 D010 D011
D005 D010 D011
D006 D010 D011
D007 D010 D011
D008 D010 D011
D009 D010 D011
D001 D002 D012
D001 D003 D012
D002 D003 D012
D001 D004 D012
D002 D004 D012
D003 D004 D012
D001 D005 D012
D002 D005 D012
D003 D005 D012
D004 D005 D012
D001 D006 D012
D002 D006 D012
D003 D006 D012
D004 D006 D012
D005 D006 D012
D001 D007 D012
D002 D007 D012
D003 D007 D012
D004 D007 D012
D005 D007 D012
D006 D007 D012
D001 D008 D012
D002 D008 D012
D003 D008 D012
D004 D008 D012
D005 D008 D012
D006 D008 D012
D007 D008 D012
D001 D009 D012
D002 D009 D012
D003 D009 D012
D004 D009 D012
D005 D009 D012
D006 D009 D012
D007 D009 D012
D008 D009 D012
D001 D010 D012
D002 D010 D012
D003 D010 D012
D004 D010 D012
D005 D010 D012
D006 D010 D012
D007 D010 D012
D008 D010 D012
D009 D010 D012
D001 D011 D012
D002 D011 D012
D003 D011 D012
D004 D011 D012
D005 D011 D012
D006 D011 D012
D007 D011 D012
D008 D011 D012
D009 D011 D012
D010 D011 D012
D001 D002 D013
D001 D003 D013
D002 D003 D013
D001 D004 D013
D002 D004 D013
D003 D004 D013
D001 D005 D013
D002 D005 D013
D003 D005 D013
D004 D005 D013
D001 D006 D013
D002 D006 D013
D003 D006 D013
D004 D006 D013
D005 D006 D013
D001 D007 D013
D002 D007 D013
D003 D007 D013
D004 D007 D013
D005 D007 D013
D006 D007 D013
D001 D008 D013
D002 D008 D013
D003 D008 D013
D004 D008 D013
D005 D008 D013
D006 D008 D013
D007 D008 D013
D001 D009 D013
D002 D009 D013
D003 D009 D013
D004 D009 D013
D005 D009 D013
D006 D009 D013
D007 D009 D013
D008 D009 D013
D001 D010 D013
D002 D010 D013
D003 D010 D013
D004 D010 D013
D005 D010 D013
D006 D010 D013
D007 D010 D013
D008 D010 D013
D009 D010 D013
D001 D011 D013
D002 D011 D013
D003 D011 D013
D004 D011 D013
D005 D011 D013
D006 D011 D013
D007 D011 D013
D008 D011 D013
D009 D011 D013
D010 D011 D013
D001 D012 D013
D002 D012 D013
D003 D012 D013
D004 D012 D013
D005 D012 D013
D006 D012 D013
D007 D012 D013
D008 D012 D013
D009 D012 D013
D010 D012 D013
D011 D012 D013
D001 D002 D014
D001 D003 D014
D002 D003 D014
D001 D004 D014
D002 D004 D014
D003 D004 D014
D001 D005 D014
D002 D005 D014
D003 D005 D014
D004 D005 D014
D001 D006 D014
D002 D006 D014
D003 D006 D014
D004 D006 D014
D005 D006 D014
D001 D007 D014
D002 D007 D014
D003 D007 D014
D004 D007 D014
D005 D007 D014
D006 D007 D014
D001 D008 D014
D002 D008 D014
D003 D008 D014
D004 D008 D014
D005 D008 D014
D006 D008 D014
D007 D008 D014
D001 D009 D014
D002 D009 D014
D003 D009 D014
D004 D009 D014
D005 D009 D014
D006 D009 D014
D007 D009 D014
D008 D009 D014
D001 D010 D014
D002 D010 D014
D003 D010 D014
D004 D010 D014
D005 D010 D014
D006 D010 D014
D007 D010 D014
D008 D010 D014
D009 D010 D014
D001 D011 D014
D002 D011 D014
D003 D011 D014
D004 D011 D014
D005 D011 D014
D006 D011 D014
D007 D011 D014
D008 D011 D014
D009 D011 D014
D010 D011 D014
D001 D012 D014
D002 D012 D014
D003 D012 D014
D004 D012 D014
D005 D012 D014
D006 D012 D014
D007 D012 D014
D008 D012 D014
D009 D012 D014
D010 D012 D014
D011 D012 D014
D001 D013 D014
D002 D013 D014
D003 D013 D014
D004 D013 D014
D005 D013 D014
D006 D013 D014
D007 D013 D014
D008 D013 D014
D009 D013 D014
D010 D013 D014
D011 D013 D014
D012 D013 D014
D001 D002 D015
D001 D003 D015
D002 D003 D015
D001 D004 D015
D002 D004 D015
D003 D004 D015
D001 D005 D015
D002 D005 D015
D003 D005 D015
D004 D005 D015
D001 D006 D015
D002 D006 D015
D003 D006 D015
D004 D006 D015
D005 D006 D015
D001 D007 D015
D002 D007 D015
D003 D007 D015
D004 D007 D015
D005 D007 D015
D006 D007 D015
D001 D008 D015
D002 D008 D015
D003 D008 D015
D004 D008 D015
D005 D008 D015
D006 D008 D015
D007 D008 D015
D001 D009 D015
D002 D009 D015
D003 D009 D015
D004 D009 D015
D005 D009 D015
D006 D009 D015
D007 D009 D015
D008 D009 D015
D001 D010 D015
D002 D010 D015
D003 D010 D015
D004 D010 D015
D005 D010 D015
D006 D010 D015
D007 D010 D015
D008 D010 D015
D009 D010 D015
D001 D011 D015
D002 D011 D015
D003 D011 D015
D004 D011 D015
D005 D011 D015
D006 D011 D015
D007 D011 D015
D008 D011 D015
D009 D011 D015
D010 D011 D015
D001 D012 D015
D002 D012 D015
D003 D012 D015
D004 D012 D015
D005 D012 D015
D006 D012 D015
D007 D012 D015
D008 D012 D015
D009 D012 D015
D010 D012 D015
D011 D012 D015
D001 D013 D015
D002 D013 D015
D003 D013 D015
D004 D013 D015
D005 D013 D015
D006 D013 D015
D007 D013 D015
D008 D013 D015
D009 D013 D015
D010 D013 D015
D011 D013 D015
D012 D013 D015
D001 D014 D015
D002 D014 D015
D003 D014 D015
D004 D014 D015
D005 D014 D015
D006 D014 D015
D007 D014 D015
D008 D014 D015
D009 D014 D015
D010 D014 D015
D011 D014 D015
D012 D014 D015
D013 D014 D015
*/
DECLARE @MaxItemCount int =15 --可以由记录集读出，不用写成常数
DECLARE @iCount int,@i int=0,@j int=0
DECLARE @sql VARCHAR(max)
set @i=0
WHILE @i<@MaxItemCount
BEGIN
SET @sql='insert into #result1'+char(10)+'SELECT '
set @j=@i+1
WHILE @j>0
BEGIN
set @sql=@sql+case when @j=@i+1 then ' t' else '+'' ''+ t' end +convert(varchar,@j)+'.persoNM'
SET @j=@j-1
END
SET @sql=@sql+' from #result1 as t1'+char(10)
set @j=1
WHILE @j<=@i
BEGIN
set @sql=@sql+' CROSS APPLY (select * from #result1 as tt'+convert(varchar,@j+1)+' where tt'+convert(varchar,@j+1)+'.persoNM<t'+convert(varchar,@j)+'.persoNM) t'+convert(varchar,@j+1)+char(10)
SET @j=@j+1
END
print @sql
EXEC(@sql)
SET @i=@i+1
END
RICHEER COCA 2016-04-11
- 打赏
- 举报
且看 #5的要求说明，#13楼对与 step2理解有误
step2 --> 对表D001——D015进行如下计算，想通过SQL代码实现以下结果#result1 （建立特征总数据库#result1）
完成：在这15个代码进行取1——15的组合数【n个数里取出m个数的组合是n*(n-1)*...*(n-m+1)/m*(m-1)*...2*1的 总和】
15取1 15 行记录（参见下面结果格式里Personidbetween 100001 and 100015）
15取2 105 行记录（参见下面结果格式里Personidbetween 1000016 and 100121）
15取3 455 行记录
15取4 1365 行记录
15取5 3003 行记录
15取6 5005 行记录
15取7 6435 行记录
15取8 6435 行记录
15取9 5005 行记录
15取10 3003 行记录
15取11 1365 行记录
15取12 455 行记录
15取13 105 行记录
15取14 15 行记录
15取15 1 行记录
总计 32767 行记录道素 2016-04-11
- 打赏
- 举报
能有这样的结果吗？多一个字段SSID
SSID AlgorithmDB
-------------------------------------
D001 AA=BB
D002 AA=CC
D003 BB=CC
D004 AA=DD
D005 BB=DD
D006 CC=DD
D007 AA=EE
D008 BB=EE
D009 CC=EE
D010 DD=EE
D011 AA=FF
D012 BB=FF
D013 CC=FF
D014 DD=FF
D015 EE=FF
SELECT 'D'+right('000'+convert(VARCHAR,row_number()over(ORDER BY t2.ssid)),3) as SSID
, rtrim(t3.ssid)+'='+rtrim(t2.ssid) + '=' + rtrim(t1.ssid) AS AlgorithmDB
FROM #test2 AS t1
CROSS APPLY (SELECT tt2.ssid FROM #test2 AS tt2 WHERE tt2.ssid < t1.ssid) t2
CROSS APPLY (SELECT tt3.ssid FROM #test2 AS tt3 WHERE tt3.ssid < t2.ssid) t3
/*
D001 AA=BB=CC
D002 AA=BB=DD
D003 AA=BB=EE
D004 AA=BB=FF
D005 AA=CC=DD
D006 BB=CC=DD
D007 AA=CC=EE
D008 BB=CC=EE
D009 AA=CC=FF
D010 BB=CC=FF
D011 AA=DD=EE
D012 BB=DD=EE
D013 CC=DD=EE
D014 AA=DD=FF
D015 BB=DD=FF
D016 CC=DD=FF
D017 AA=EE=FF
D018 BB=EE=FF
D019 CC=EE=FF
D020 DD=EE=FF
*/
道素 2016-04-11
- 打赏
- 举报
这样吗？
SELECT 'D'+right('000'+convert(VARCHAR,row_number()over(ORDER BY t2.ssid)),3) as SSID, rtrim(t2.ssid) + '=' + rtrim(t1.ssid) AS AlgorithmDB
FROM #test2 AS t1
CROSS APPLY (SELECT tt2.ssid
FROM #test2 AS tt2
WHERE tt2.ssid < t1.ssid) t2
/*
D001 AA=BB
D002 AA=CC
D003 AA=DD
D004 AA=EE
D005 AA=FF
D006 BB=CC
D007 BB=DD
D008 BB=EE
D009 BB=FF
D010 CC=DD
D011 CC=EE
D012 CC=FF
D013 DD=EE
D014 DD=FF
D015 EE=FF
*/
RICHEER COCA 2016-04-11
- 打赏
- 举报
#5楼的是整理过的，前面的可以不看（#3——#4较乱）
能有这样的结果吗？多一个字段SSID
SSID AlgorithmDB
-------------------------------------
D001 AA=BB
D002 AA=CC
D003 BB=CC
D004 AA=DD
D005 BB=DD
D006 CC=DD
D007 AA=EE
D008 BB=EE
D009 CC=EE
D010 DD=EE
D011 AA=FF
D012 BB=FF
D013 CC=FF
D014 DD=FF
D015 EE=FF
道素 2016-04-11
- 打赏
- 举报
我写的SQL中有Rtrim是因为测试数据结尾有对于空格，否则不需要，如果你看现在的结果不顺眼（如前几行AA在等号右边)
你讲rtrim(t1.ssid)+'='+rtrim(t2.ssid) 改为rtrim(t2.ssid)+'='+rtrim(t1.ssid)就行了
至于以后变成3，4，5等更多你都可以按照这个算法
2=1+1
3=2+1 (2就是上面的结果）
...
道素 2016-04-11
- 打赏
- 举报
好长，我看全，我先分析其中的一个问题，6 个数，两两组合，没有重复值（也不分前后）这样会产生15个数。
不能直接取迪尔卡集，会有很多重复值，你可以这样弄：
select rtrim(t1.ssid)+'='+rtrim(t2.ssid) as AlgorithmDB from #test2 as t1
CROSS APPLY (select tt2.ssid from #test2 as tt2 where tt2.ssid<t1.ssid) t2
ORDER BY t2.ssid
/*
BB=AA
CC=AA
DD=AA
EE=AA
FF=AA
CC=BB
DD=BB
EE=BB
FF=BB
DD=CC
EE=CC
FF=CC
EE=DD
FF=DD
FF=EE
*/
RICHEER COCA 2016-04-10
- 打赏
- 举报
自己琢磨写了以下代码，但输出数据格式有误，
-----15取1
;WITH t AS
(
SELECT TOP 15 rn=ROW_NUMBER()OVER(ORDER BY @@spid)
FROM sysobjects
)
SELECT RIGHT('00'+LTRIM(a.rn),2) as DM1
into #test1
FROM t a
-----15取2
;WITH t AS
(
SELECT TOP 15 rn=ROW_NUMBER()OVER(ORDER BY @@spid)
FROM sysobjects
)
SELECT RIGHT('00'+LTRIM(a.rn),2) as DM1
,RIGHT('00'+LTRIM(b.rn),2) as DM2
into #test2
FROM t a
JOIN t b ON a.rn<b.rn
-----15取3
;WITH t AS
(
SELECT TOP 15 rn=ROW_NUMBER()OVER(ORDER BY @@spid)
FROM sysobjects
)
SELECT RIGHT('00'+LTRIM(a.rn),2) as DM1
,RIGHT('00'+LTRIM(b.rn),2) as DM2
,RIGHT('00'+LTRIM(c.rn),2) as DM3
into #test3
FROM t a
JOIN t b ON a.rn<b.rn
JOIN t c ON b.rn<c.rn
-----15取4
;WITH t AS
(
SELECT TOP 15 rn=ROW_NUMBER()OVER(ORDER BY @@spid)
FROM sysobjects
)
SELECT RIGHT('00'+LTRIM(a.rn),2) as DM1
,RIGHT('00'+LTRIM(b.rn),2) as DM2
,RIGHT('00'+LTRIM(c.rn),2) as DM3
,RIGHT('00'+LTRIM(d.rn),2) as DM4
into #test4
FROM t a
JOIN t b ON a.rn<b.rn
JOIN t c ON b.rn<c.rn
JOIN t d ON c.rn<d.rn
-----15取5
;WITH t AS
(
SELECT TOP 15 rn=ROW_NUMBER()OVER(ORDER BY @@spid)
FROM sysobjects
)
SELECT RIGHT('00'+LTRIM(a.rn),2) as DM1
,RIGHT('00'+LTRIM(b.rn),2) as DM2
,RIGHT('00'+LTRIM(c.rn),2) as DM3
,RIGHT('00'+LTRIM(d.rn),2) as DM4
,RIGHT('00'+LTRIM(e.rn),2) as DM5
into #test5
FROM t a
JOIN t b ON a.rn<b.rn
JOIN t c ON b.rn<c.rn
JOIN t d ON c.rn<d.rn
JOIN t e ON d.rn<e.rn
-----15取6
;WITH t AS
(
SELECT TOP 15 rn=ROW_NUMBER()OVER(ORDER BY @@spid)
FROM sysobjects
)
SELECT RIGHT('00'+LTRIM(a.rn),2) as DM1
,RIGHT('00'+LTRIM(b.rn),2) as DM2
,RIGHT('00'+LTRIM(c.rn),2) as DM3
,RIGHT('00'+LTRIM(d.rn),2) as DM4
,RIGHT('00'+LTRIM(e.rn),2) as DM5
,RIGHT('00'+LTRIM(f.rn),2) as DM6
into #test6
FROM t a
JOIN t b ON a.rn<b.rn
JOIN t c ON b.rn<c.rn
JOIN t d ON c.rn<d.rn
JOIN t e ON d.rn<e.rn
JOIN t f ON e.rn<f.rn
-----15取7
.......
......
-----15取15
;WITH t AS
(
SELECT TOP 15 rn=ROW_NUMBER()OVER(ORDER BY @@spid)
FROM sysobjects
)
SELECT RIGHT('00'+LTRIM(a.rn),2) as DM1
,RIGHT('00'+LTRIM(b.rn),2) as DM2
,RIGHT('00'+LTRIM(c.rn),2) as DM3
,RIGHT('00'+LTRIM(d.rn),2) as DM4
,RIGHT('00'+LTRIM(e.rn),2) as DM5
,RIGHT('00'+LTRIM(f.rn),2) as DM6
,RIGHT('00'+LTRIM(g.rn),2) as DM7
,RIGHT('00'+LTRIM(h.rn),2) as DM8
,RIGHT('00'+LTRIM(i.rn),2) as DM9
,RIGHT('00'+LTRIM(j.rn),2) as DM10
,RIGHT('00'+LTRIM(k.rn),2) as DM11
,RIGHT('00'+LTRIM(l.rn),2) as DM12
,RIGHT('00'+LTRIM(m.rn),2) as DM13
,RIGHT('00'+LTRIM(n.rn),2) as DM14
,RIGHT('00'+LTRIM(o.rn),2) as DM15
into #test15
FROM t a
JOIN t b ON a.rn<b.rn
JOIN t c ON b.rn<c.rn
JOIN t d ON c.rn<d.rn
JOIN t e ON d.rn<e.rn
JOIN t f ON e.rn<f.rn
JOIN t g ON f.rn<g.rn
JOIN t h ON g.rn<h.rn
JOIN t i ON h.rn<i.rn
JOIN t j ON i.rn<j.rn
JOIN t k ON j.rn<k.rn
JOIN t l ON k.rn<l.rn
JOIN t m ON l.rn<m.rn
JOIN t n ON m.rn<n.rn
JOIN t o ON n.rn<o.rn
if object_id('SSBallDB..TZK32767') is not null drop table TZK32767
go
create table TZK32767([RCid] [int] IDENTITY(100001,1) NOT NULL
,[DM1] nvarchar(15)
,[DM2] nvarchar(15)
,[DM3] nvarchar(15)
,[DM4] nvarchar(15)
,[DM5] nvarchar(15)
,[DM6] nvarchar(15)
,[DM7] nvarchar(15)
,[DM8] nvarchar(15)
,[DM9] nvarchar(15)
,[DM10] nvarchar(15)
,[DM11] nvarchar(15)
,[DM12] nvarchar(15)
,[DM13] nvarchar(15)
,[DM14] nvarchar(15)
,[DM15] nvarchar(15))
insert TZK32767
select *,''AS DM2,''AS DM3,''AS DM4,''AS DM5,''AS DM6,''AS DM7,''AS DM8,''AS DM9,''AS DM10,''AS DM11,''AS DM12,''AS DM13,''AS DM14,''AS DM15
from #test1 ORDER BY DM1,DM2,DM3,DM4,DM5,DM6,DM7,DM8,DM9,DM10,DM11,DM12,DM13,DM14,DM15
insert TZK32767
select *,''AS DM3,''AS DM4,''AS DM5,''AS DM6,''AS DM7,''AS DM8,''AS DM9,''AS DM10,''AS DM11,''AS DM12,''AS DM13,''AS DM14,''AS DM15
from #test2 ORDER BY DM1,DM2,DM3,DM4,DM5,DM6,DM7,DM8,DM9,DM10,DM11,DM12,DM13,DM14,DM15
insert TZK32767
select *,''AS DM4,''AS DM5,''AS DM6,''AS DM7,''AS DM8,''AS DM9,''AS DM10,''AS DM11,''AS DM12,''AS DM13,''AS DM14,''AS DM15
from #test3 ORDER BY DM1,DM2,DM3,DM4,DM5,DM6,DM7,DM8,DM9,DM10,DM11,DM12,DM13,DM14,DM15
insert TZK32767
select *,''AS DM5,''AS DM6,''AS DM7,''AS DM8,''AS DM9,''AS DM10,''AS DM11,''AS DM12,''AS DM13,''AS DM14,''AS DM15
from #test4 ORDER BY DM1,DM2,DM3,DM4,DM5,DM6,DM7,DM8,DM9,DM10,DM11,DM12,DM13,DM14,DM15
insert TZK32767
select *,''AS DM6,''AS DM7,''AS DM8,''AS DM9,''AS DM10,''AS DM11,''AS DM12,''AS DM13,''AS DM14,''AS DM15
from #test5 ORDER BY DM1,DM2,DM3,DM4,DM5,DM6,DM7,DM8,DM9,DM10,DM11,DM12,DM13,DM14,DM15
insert TZK32767
select *,''AS DM7,''AS DM8,''AS DM9,''AS DM10,''AS DM11,''AS DM12,''AS DM13,''AS DM14,''AS DM15
from #test6 ORDER BY DM1,DM2,DM3,DM4,DM5,DM6,DM7,DM8,DM9,DM10,DM11,DM12,DM13,DM14,DM15
insert TZK32767
select *,''AS DM8,''AS DM9,''AS DM10,''AS DM11,''AS DM12,''AS DM13,''AS DM14,''AS DM15
from #test7 ORDER BY DM1,DM2,DM3,DM4,DM5,DM6,DM7,DM8,DM9,DM10,DM11,DM12,DM13,DM14,DM15
insert TZK32767
select *,''AS DM9,''AS DM10,''AS DM11,''AS DM12,''AS DM13,''AS DM14,''AS DM15
from #test8 ORDER BY DM1,DM2,DM3,DM4,DM5,DM6,DM7,DM8,DM9,DM10,DM11,DM12,DM13,DM14,DM15
insert TZK32767
select *,''AS DM10,''AS DM11,''AS DM12,''AS DM13,''AS DM14,''AS DM15
from #test9 ORDER BY DM1,DM2,DM3,DM4,DM5,DM6,DM7,DM8,DM9,DM10,DM11,DM12,DM13,DM14,DM15
insert TZK32767
select *,''AS DM11,''AS DM12,''AS DM13,''AS DM14,''AS DM15
from #test10 ORDER BY DM1,DM2,DM3,DM4,DM5,DM6,DM7,DM8,DM9,DM10,DM11,DM12,DM13,DM14,DM15
insert TZK32767
select *,''AS DM12,''AS DM13,''AS DM14,''AS DM15
from #test11 ORDER BY DM1,DM2,DM3,DM4,DM5,DM6,DM7,DM8,DM9,DM10,DM11,DM12,DM13,DM14,DM15
insert TZK32767
select *,''AS DM13,''AS DM14,''AS DM15
from #test12 ORDER BY DM1,DM2,DM3,DM4,DM5,DM6,DM7,DM8,DM9,DM10,DM11,DM12,DM13,DM14,DM15
insert TZK32767
select *,''AS DM14,''AS DM15
from #test13 ORDER BY DM1,DM2,DM3,DM4,DM5,DM6,DM7,DM8,DM9,DM10,DM11,DM12,DM13,DM14,DM15
insert TZK32767
select *,''AS DM15
from #test14 ORDER BY DM1,DM2,DM3,DM4,DM5,DM6,DM7,DM8,DM9,DM10,DM11,DM12,DM13,DM14,DM15
insert TZK32767
select *
from #test15 ORDER BY DM1,DM2,DM3,DM4,DM5,DM6,DM7,DM8,DM9,DM10,DM11,DM12,DM13,DM14,DM15
DROP TABLE #TEST1 DROP TABLE #TEST2 DROP TABLE #TEST3 DROP TABLE #TEST4 DROP TABLE #TEST5 DROP TABLE #TEST6 DROP TABLE #TEST7 DROP TABLE #TEST8 DROP TABLE #TEST9 DROP TABLE #TEST10 DROP TABLE #TEST11 DROP TABLE #TEST12 DROP TABLE #TEST13 DROP TABLE #TEST14 DROP TABLE #TEST15
select * from SSBallDB..TZK32767
ORDER BY RCID,DM1,DM2,DM3,DM4,DM5,DM6,DM7,DM8,DM9,DM10,DM11,DM12,DM13,DM14,DM15
--ORDER BY RCID/*
--但输出数据格式有误,DOO1后面有14个' ' --' '代表字符串（不是null），因为代码DM1+' '+DM2+' '+DM3+' '+DM4+' '+DM5+' '+DM6+' '+DM7+' '+DM8+' '+DM9+' '+DM10+' '+DM11+' '+DM12+' '+DM13+' '+DM14+' '+DM15 as SID造成
RCID SID
---------------
100001 D001
......
......
*/
declare @s varchar(200)
set @s='ABCDEFGHIJK' --- HIJKLMNO
;WITH CTE AS
(
SELECT ROW_NUMBER() OVER (ORDER BY ID) AS SEQ FROM SYSOBJECTS
),
CTE2 AS
(
SELECT SUBSTRING(@s,SEQ,1) AS COL FROM CTE WHERE LEN(@s)>=SEQ
),
CTE3 AS
(
SELECT CAST(COL AS VARCHAR(MAX)) AS COL FROM CTE2
UNION ALL
SELECT T3.COL+T2.COL FROM CTE2 AS T2,CTE3 AS T3 WHERE CHARINDEX(T2.COL, T3.COL)=0
)
SELECT * FROM CTE3 ORDER BY LEN(COL)
RICHEER COCA 2016-04-09
- 打赏
- 举报
谢谢，言之有理，但仅仅解决了部分，现在的难题是：建立数据表#result1对于楼主无法完成的，盼解ING，谢谢！
唐诗三百首 2016-04-09
- 打赏
- 举报
分步处理, 各步骤应该都能在网上找到对应Demo代码的.
其实LZ之前所发几个帖已解决了此帖的部分问题.
RICHEER COCA 2016-04-09
- 打赏
- 举报
现有数据, #test1 #test2
--> 建立数据表#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 900001,'29011008032003030' union all
select 900002,'19010027025013018' union all
select 900003,'26028011013010012' union all
select 900004,'09004026021019020' union all
select 900005,'23007028010001032' union all
select 900006,'13004007025006010' union all
select 900007,'04030017015031006' union all
select 900008,'01010027026021003' union all
select 900009,'23019009026001021' union all
select 900010,'09008017023014005' union all
select 900011,'30018009022005020' union all
select 900012,'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 'AA','Cast(SUBSTRING(Unotext,1,1) as int)+Cast(SUBSTRING(Unotext,2,1) as int)' union all
select 'BB ','Cast(SUBSTRING(Unotext,4,1) as int)+Cast(SUBSTRING(Unotext,5,1) as int)' union all
select 'CC ','Cast(SUBSTRING(Unotext,7,1) as int)+Cast(SUBSTRING(Unotext,8,1) as int)' union all
select 'DD ','Cast(SUBSTRING(Unotext,10,1) as int)+Cast(SUBSTRING(Unotext,11,1) as int)' union all
select 'EE ','Cast(SUBSTRING(Unotext,13,1) as int)+Cast(SUBSTRING(Unotext,14,1) as int)' union all
select 'FF ','Cast(SUBSTRING(Unotext,16,1) as int)+Cast(SUBSTRING(Unotext,17,1) as int)'
--> 建立数据表#result1
if object_id('tempdb.dbo.#result1') is not null drop table #result1
go
create table #result1([persoNM] nvarchar(20),[SID] nvarchar(200))
insert #result1
难题：建立数据表#result1对于楼主无法完成的，可见step1、step2、step3
step1 --> 完成表#test2里AA BB CC DD EE FF进行6选2的组合数基因特征D001——D015
首先在6个优化代码取任意2个,完成的的组合数为15个
D001 AA=BB
D002 AA=CC
D003 AA=DD
D004 AA=EE
D005 AA=FF
D006 BB=CC
D007 BB=DD
D008 BB=EE
D009 BB=FF
D010 CC=DD
D011 CC=EE
D012 CC=FF
D013 DD=EE
D014 DD=FF
D015 EE=FF
step2 --> 对表D001——D015进行如下计算，想通过SQL代码实现以下结果#result1 （建立特征总数据库#result1）
完成：在这15个代码进行取1——15的组合数【n个数里取出m个数的组合是n*(n-1)*...*(n-m+1)/m*(m-1)*...2*1的 总和】
15取1 15 行记录（参见下面结果格式里Personidbetween 100001 and 100015）
15取2 105 行记录（参见下面结果格式里Personidbetween 1000016 and 100121）
15取3 455 行记录
15取4 1365 行记录
15取5 3003 行记录
15取6 5005 行记录
15取7 6435 行记录
15取8 6435 行记录
15取9 5005 行记录
15取10 3003 行记录
15取11 1365 行记录
15取12 455 行记录
15取13 105 行记录
15取14 15 行记录
15取15 1 行记录
总计 32767 行记录
--step3 --> select * from #result1 可得到查询结果#result1 (特征总数据库)
结果格式为
/*
Personid SID
-------------------------------------
100001 D001
100002 D002
100003 D003
100004 D004
100005 D005
100006 D006
100007 D007
100008 D008
100009 D009
100010 D010
100011 D011
100012 D012
100013 D013
100014 D014
100015 D015
100016 D001 D002
......
100050 D003 D011
100051 D003 D012
......
100085 D007 D008
100086 D007 D009
100087 D007 D010
......
100111 D011 D012
100112 D011 D013
.....
100033 D001 D002 D006
100034 D001 D003 D007
100035 D001 D004 D008
100036 D001 D005 D009
......
100046 D007 D013 D008
100047 D007 D014 D009
100048 D008 D015 D009
100049 D010 D013 D011
100050 D010 D014 D012
100051 D011 D015 D012
100052 D013 D015 D014
100053 D001 D006 D002 D010 D003 D007
100054 D001 D006 D002 D011 D004 D008
......
100067 D010 D013 D011 D015 D012 D014
.....
100072 D002 D003 D004 D005 D010 D011 D012 D013 D014 D015
100073 D006 D007 D008 D009 D010 D011 D012 D013 D014 D015
....
132767 D001 D002 D003 D004 D005 D006 D007 D008 D009 D010 D011 D012 D013 D014 D015
(32767 row(s) affected)
/*
--需要的结果
persoNM personid unotext
-------------------- ----------- --------------------
100001 900010 23007028010001032
100002 900012 13004007025006010
100003 900002 29011008032003030
900025 31033032006015026
100004 900001 06009016021022023
900003 06009016031032033
900005 06009026031032033
900007 06012016021022023
900009 06012016031032033
900011 06012026031032033
900013 06015016021022023
900015 06015016031032033
900017 06015026031032033
900019 06018026031032033
900021 06021026031032033
900023 06024026031032033
......
......
*/
RICHEER COCA 2016-04-08
- 打赏
- 举报
补充说明
-->步骤二 AA BB CC DD EE FF分别代表基因优化算法的代码(实际数据有60310多个，现只取6个举例），想通过SQL代码实现以下结果#result1
首先在6个优化代码取任意2个的组合数为
D001 AA=BB
D002 AA=CC
D003 AA=DD
D004 AA=EE
D005 AA=FF
D006 BB=CC
D007 BB=DD
D008 BB=EE
D009 BB=FF
D010 CC=DD
D011 CC=EE
D012 CC=FF
D013 DD=EE
D014 DD=FF
D015 EE=FF
然后在这15个代码进行取1——15的组合数【n个数里取出m个数的组合是n*(n-1)*...*(n-m+1)/m*(m-1)*...2*1的 总和】
15取1 15
15取2 105
15取3 455
15取4 1365
15取5 3003
15取6 5005
15取7 6435
15取8 6435
15取9 5005
15取10 3003
15取11 1365
15取12 455
15取13 105
15取14 15
15取15 1
总计 32767
-- select * from #result1 可得到查询结果#result1
步骤二想得到结果格式为
/*
Personid SID
-------------------------------------
100001 D001
100002 D002
100003 D003
100004 D004
100005 D005
100006 D006
100007 D007
100008 D008
100009 D009
100010 D010
100011 D011
100012 D012
100013 D013
100014 D014
100015 D015
100016 D001 D002
......
100050 D003 D011
100051 D003 D012
......
100085 D007 D008
100086 D007 D009
100087 D007 D010
......
100111 D011 D012
100112 D011 D013
.....
100033 D001 D002 D006
100034 D001 D003 D007
100035 D001 D004 D008
100036 D001 D005 D009
......
100046 D007 D013 D008
100047 D007 D014 D009
100048 D008 D015 D009
100049 D010 D013 D011
100050 D010 D014 D012
100051 D011 D015 D012
100052 D013 D015 D014
100053 D001 D006 D002 D010 D003 D007
100054 D001 D006 D002 D011 D004 D008
......
100067 D010 D013 D011 D015 D012 D014
.....
100072 D002 D003 D004 D005 D010 D011 D012 D013 D014 D015
100073 D006 D007 D008 D009 D010 D011 D012 D013 D014 D015
....
132767 D001 D002 D003 D004 D005 D006 D007 D008 D009 D010 D011 D012 D013 D014 D015
(32767 row(s) affected)
*/
唐诗三百首 2016-04-08
- 打赏
- 举报
看不明白,建议整理清楚再发,
包括几个必要部分: 现有数据, 处理逻辑, 希望结果.RICHEER COCA 2016-04-07
- 打赏
- 举报
简单地说就是
步骤2 手工建立的AlgorithmDB
SSID AlgorithmDB
-------------------------------------
D001 AA=BB
D002 AA=CC
D003 BB=CC
D004 AA=DD
D005 BB=DD
D006 CC=DD
D007 AA=EE
D008 BB=EE
D009 CC=EE
D010 DD=EE
D011 AA=FF
D012 BB=FF
D013 CC=FF
D014 DD=FF
D015 EE=FF
RICHEER COCA 2016-04-07
- 打赏
- 举报
补充：手工建立的表
因为存在
D050 CC=DD=EE=FF
D051 AA=BB=CC=DD=EE
D052 AA=BB=CC=DD=FF
D053 AA=BB=CC=EE=FF
D054 AA=BB=DD=EE=FF
D055 AA=CC=DD=EE=FF
D056 BB=CC=DD=EE=FF
D057 AA=BB=CC=DD=EE=FF/*
MERGE INTO #test3 AS tg USING
----------- -----------
(3 行受影响)
MERGE INTO #test3 AS tg USING
(
SELECT t.*,'D016' as sid FROM #test11 AS t WHERE Cast(SUBSTRING(Unotext,1,1) as int)+Cast(SUBSTRING(Unotext,2,1) as int)=Cast(SUBSTRING(Unotext,4,1) as int)+Cast(SUBSTRING(Unotext,5,1) as int)=Cast(SUBSTRING(Unotext,7,1) as int)+Cast(SUBSTRING(Unote
) AS s ON s.[personid]=tg.[personid]
WHEN matched THEN
UPDATE set tg.sid=tg.sid+' '+s.sid
when not matched then
insert values(s.[personid],s.[unotext],s.sid)
;
消息 102，级别 15，状态 1，第 3 行
'=' 附近有语法错误。
MERGE INTO #test3 AS tg USING
(
SELECT t.*,'D017' as sid FROM #test11 AS t WHERE Cast(SUBSTRING(Unotext,1,1) as int)+Cast(SUBSTRING(Unotext,2,1) as int)=Cast(SUBSTRING(Unotext,4,1) as int)+Cast(SUBSTRING(Unotext,5,1) as int)=Cast(SUBSTRING(Unotext,10,1) as int)+Cast(SUBSTRING(Unot
) AS s ON s.[personid]=tg.[personid]
WHEN matched THEN
UPDATE set tg.sid=tg.sid+' '+s.sid
when not matched then
insert values(s.[personid],s.[unotext],s.sid)
;
消息 102，级别 15，状态 1，第 3 行
'=' 附近有语法错误。
*/
