English 中文(简体)
页: 1 比较单一表格中的行文
原标题:SQL Query comparing rows from single table
  • 时间:2010-10-28 16:00:01
  •  标签:
  • sql

我们在此对基因表记录进行了比较:首先,将产生基因,并将之与所有基因进行比较,例如,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
最佳回答
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。

问题回答

不清楚您的逻辑如何运作(特别是在<条码>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使我感到困惑,因此你可能希望重复检查。

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





相关问题
SQL SubQuery getting particular column

I noticed that there were some threads with similar questions, and I did look through them but did not really get a convincing answer. Here s my question: The subquery below returns a Table with 3 ...

难以执行 REGEXP_SUBSTR

I m 查询Oracle 10g。 我有两张表格(样本数据见下文)。 i m 试图提取一些领域

SQL Query Shortcuts

What are some cool SQL shorthands that you know of? For example, something I learned today is you can specify to group by an index: SELECT col1, col2 FROM table GROUP BY 2 This will group by col2

PHP array callback functions for cleaning output

I have an array of output from a database. I am wondering what the cleanest way to filter the values is example array Array ( [0] => Array ( [title] => title 1 ...

OracleParameter and DBNull.Value

we have a table in an Oracle Database which contains a column with the type Char(3 Byte). Now we use a parameterized sql to select some rows with a DBNull.Value and it doesn t work: OracleCommand ...

Running numbers in SQL

I have a SQL-statement like this: SELECT name FROM users WHERE deleted = 0; How can i create a result set with a running number in the first row? So the result would look like this: 1 Name_1 2 ...

How to get SQL queries for each user where env is production

I’m developing an application dedicated to generate statistical reports, I would like that user after saving their stat report they save sql queries too. To do that I wrote the following module: ...