sql，将从第一个表获得的行值与第二个表的列名进行不带循环的匹配。

Question

如果有人能解决以下问题，我会很有帮助的。我有两张桌子和一个要求-

必需的SQL脚本：

在不使用循环的情况下，一个有效的SQL脚本可以将从第一个表获得的行值(sampleID)匹配到列名(X2、X4、X8.)并获得给定Gene_ID的值(例如NFYA)。

预期结果：

X1  15.2856
X10 18.2201
X14 13.3406
. .
. .

表(行X列:135X32)

表描述(部分行和列)

+-------+--------------------+--------+----------+-------+---------+
| Batch | filename_generate  | pcode  | SampleID | check | Diagnos |
+-------+--------------------+--------+----------+-------+---------+
| B     | cufflinks_out_2_B  | 01-111 | X2       | TRUE  | RH      |
| D     | cufflinks_out_4D   | 01-163 | X4       | TRUE  | RH      |
| B     | cufflinks_out_5_B  | 01-166 | X5       | TRUE  | RH      |
| D     | cufflinks_out_6D   | 02-007 | X6       | TRUE  | RH      |
| C     | cufflinks_out_8C   | 02-012 | X8       | TRUE  | RH      |
| C     | cufflinks_out_9C   | 02-014 | X9       | TRUE  | RH      |
| B     | cufflinks_out_10_B | 02-017 | X10      | TRUE  | RH      |
| B     | cufflinks_out_13_B | 02-030 | X13      | TRUE  | ON     |
| D     | cufflinks_out_14D  | 02-031 | X14      | TRUE  | RH      |
| B     | cufflinks_out_15B  | 02-037 | X15      | TRUE  | RH      |
| C     | cufflinks_out_16C  | 02-038 | X16      | TRUE  | IS     |
| B     | cufflinks_out_17_B | 02-041 | X17      | TRUE  | ON     |
| B     | cufflinks_out_19_B | 02-050 | X19      | TRUE  | ON     |
| B     | cufflinks_out_20_B | 02-056 | X20      | TRUE  | RH      |
+-------+--------------------+--------+----------+-------+---------+

表2(行X列: 56000 X 137)

表描述(部分行和列)

+-----------------+----------+---------+---------+----------+----------+----------+----------+---------+
|   Ensembl_ID    | Gene_ID  |   X1    |   X10   |   X13    |   X14    |   X15    |   X16    |   X17   |
+-----------------+----------+---------+---------+----------+----------+----------+----------+---------+
| ENSG00000000003 | TSPAN6   | 1.388   | 0.443   | 0.563    | 0.350    | 0.390    | 0.220    | 0.528   | 
| ENSG00000000005 | TNMD     | 0       | 0       | 0        | 0        | 0        | 0        | 0       |
| ENSG00000000419 | DPM1     | 34.309  | 40.2635 | 28.8669  | 21.0556  | 18.1733  | 22.0223  | 25.4352 |
| ENSG00000000457 | SCYL3    | 7.84987 | 9.35551 | 7.45483  | 7.1601   | 6.53686  | 7.26445  | 6.30419 |
| ENSG00000000460 | C1orf112 | 2.36851 | 3.76825 | 3.10324  | 2.0262   | 1.84606  | 3.01185  | 3.02763 |
| ENSG00000000938 | FGR      | 227.024 | 222.578 | 247.124  | 234.995  | 255.226  | 265.288  | 323.6   |
| ENSG00000000971 | CFH      | 3.17952 | 3.60279 | 1.68429  | 3.74301  | 2.10637  | 0.763278 | 1.78278 |
| ENSG00000001036 | FUCA2    | 16.5566 | 19.1703 | 25.5005  | 18.5244  | 20.7771  | 18.353   | 25.2364 |
| ENSG00000001084 | GCLC     | 9.45121 | 16.1362 | 12.6239  | 13.1074  | 10.6472  | 18.9938  | 12.8249 |
| ENSG00000001167 | NFYA     | 15.2856 | 18.2201 | 12.4789  | 13.3406  | 15.0146  | 13.2608  | 11.5385 |
| ENSG00000001460 | C1orf201 | 1.64558 | 1.93322 | 0.7927   | 1.71796  | 2.27997  | 0.938738 | 1.3911  |
| ENSG00000001461 | NIPAL3   | 14.6073 | 14.1772 | 11.1503  | 12.5077  | 15.1269  | 13.6     | 11.227  |
| ENSG00000001497 | LAS1L    | 14.4519 | 15.3965 | 11.8901  | 16.8572  | 16.7174  | 14.6004  | 15.7266 |
+-----------------+----------+---------+---------+----------+----------+----------+----------+---------+

用python编写的脚本，模块使用sqlite3

我正在添加完整的code..It，如果您能提前帮助我的话，here..thanks可能会很好！

import os, sys, time
import sqlite3   
import apsw  
disk_db = apsw.Connection('sampleinfogenotype.db')  
memcon=apsw.Connection(":memory:")  
with memcon.backup("main",disk_db, "main") as backup:  
    backup.step() # copy whole database in one go  

mdata=memcon.cursor()  
for row in memcon.cursor().execute("SELECT tableone.SampleID from tableone WHERE   tableone.Diagnos=='RH'"):
    sampleID_row=str(row[0])  
    sqlscript="SELECT "+sampleID_row+ " FROM tabletwo WHERE tabletwo.Gene_ID=='NFYA'"  
    data=memdata.execute(sqlscript).fetchall()[0]  
    print sampleID_row,data[0]  
memcon.close()  
disk_db.close()

它给出了预期的结果，但这是耗时的，因为有一个循环！是否有一个有效的sql脚本或method...any信息将是helpful..thanks提前。

Answer 1

您不需要一次又一次地重新查询第二个表，只需检索一次一行并将其用作查找表。如果您使用的 row factory与检索一行一样容易：

memcon.row_factory = sqlite3.Row
memdata.execute("select * from tabletwo where tabletwo.Gene_ID == 'NFYA'")
nfya_row = memdata.fetchone()

memdata.execute("SELECT tableone.SampleID from tableone WHERE tableone.Diagnos=='RH'")
for row in memdata:
    sampleID = row['SampleID']
    print sampleID, nfya_row[sampleID]

nfya_row值现在包含一个sqlite3.Row对象，其键是列名。现在遍历tabelone结果时，可以直接查找任何给定列的值。

如果您使用的是apsw模块，那么就没有一个简单的现成行工厂可以返回一个字典，但是您可以轻松地为一行构建字典：

memdata.execute("select * from tabletwo where tabletwo.Gene_ID == 'NFYA'")
nfya_row = {desc[0]: column for desc, column in zip(memdata.getdescription(), memdata.next())}

memdata.execute("SELECT tableone.SampleID from tableone WHERE tableone.Diagnos=='RH'")
for row in memdata:
    sampleID = row[0]
    print sampleID, nfya_row[sampleID]