Question

我们在此对基因表记录进行了比较:首先,将产生基因,并将之与所有基因进行比较,例如,a,ab,c, 那么,乙、乙、乙、乙、乙、乙、乙、乙、乙、乙、乙、乙、乙、.。

因此,对比结果是第二(b)项,因为对等记录是589 822项(b)项的普通基因术语,是1项,因为对记录586和所有其他组合而言,记录是零。

goterm  gene auto
--------------------
589     a    1
822     a    2
478     a    3
586     b    4
589     b    5
600     c    6
586     c    7
822     b    8

学历:

select count(*), 
       x.gene,
       x.ng 
 from (select t.gene,
              v.gene as ng 
        from (select distinct gene 
                from gene) as t 
  cross join (select distinct gene from gene) as v) as x 
   left join (select (g.gene),(n.gene) as ng from gene g 
        join gene n on n.goterm=g.goterm where g.auto<n.auto ) as y on y.gene = x.ng 
                                                                   and y.ng = x.gene
group by x.gene,x.ng

最后,上述问题的结果是:

count  gene    gene
1      a       a
2      b       a
1      c       a
1      a       b
1      b       b
1      c       b
1      a       c
1      b       c
1      c       c

但产出必须:

count  gene    gene
0      a       a
2      b       a  
0      c       a
0      a       b
0      b       b
1      c       b
0      a       c
0      b       c
0      c       c

Answer 1

select combo1.gene, combo2.gene, count (gene2.goterm)
from (select distinct gene from gene) combo1
cross join (select distinct gene from gene) combo2
join gene gene1 on combo1.gene = gene1.gene
left join gene gene2 on combo2.gene = gene2.gene
    and gene1.goterm = gene2.goterm and gene1.auto < gene2.auto
group by combo1.gene, combo2.gene
order by combo1.gene, combo2.gene

Edit:已实现,我根本不需要DISTINCT。

Answer 2

不清楚您的逻辑如何运作(特别是在<条码>auto上的比较),但这一询问的结果是:

declare @t table (goterm  int, gene char(1), auto int identity)
insert @t
          select 589,      a 
union select all 822,      a 
union select all 478,      a 
union select all 586,      b 
union select all 589,      b 
union select all 600,      c 
union select all 586,      c 
union select all 822,      b 

select  t1.gene
,       t2.gene
,       (
        select  COUNT(*)
        from    @t t3
        join    @t t4
        on      t3.goterm = t4.goterm
                and t3.auto > t4.auto
        where   t3.gene = t1.gene
                and t4.gene = t2.gene
        ) as Total
from    (
        select  distinct gene 
        from    @t
        ) t1
cross join
        (
        select  distinct gene 
        from    @t
        ) t2
order by
        t2.gene
,       t1.gene

印本:

gene    gene    Total
a       a       0
b       a       2
c       a       0
a       b       0
b       b       0
c       b       1
a       c       0
b       c       0
c       c       0

<代码>t1和t2 均建立了基因组合矩阵。。 <代码>t3.auto > t4.auto使我感到困惑,因此你可能希望重复检查。

Answer 3

电话:count(*)/code>至count(y.gene)。

友情链接