select aliases.*,KEGGClass.* from KEGGClass right join
(select KEGGXrefs.PathwayID,count(KEGGXrefs.GeneID ) as PathwayCount from GeneXrefsIPI join KEGGXrefs on KEGGXrefs.GeneID = GeneXrefsIPI.EntrezGeneID where GeneXrefsIPI.accession='IPI00796116' group by KEGGXrefs.PathwayID)
aliases on KEGGClass.PathwayID=aliases.PathwayID order by PathwayCount一共三个表
---------------
GeneXrefsIPI
KEGGXrefs
KEGGClass
---------------
KEGGXrefs.GeneID = GeneXrefsIPI.EntrezGeneID
KEGGXrefs.PathwayID=KEGGClass.PathwayID
--------------------------------------------
为什么明明在KEGGClass表中有对应的PathwayID,KEGGClass.ClassName却查出的都是空PathwayID PathwayCount ClassName
hsa00010 1 NULL
hsa00053 1 NULL
hsa00071 1 NULL
hsa00280 1 NULL
hsa00310 2 NULL
——————————————————————————————
如果是改为full join 更能看出问题 好像是KEGGXrefs.PathwayID=KEGGClass.PathwayID不管用
PathwayID PathwayCount ClassName
NULL NULL ggg
NULL NULL hhh
NULL NULL kkk
NULL NULL ttt
NULL NULL test2
NULL NULL rr2
hsa00410 1 NULL
hsa00053 1 NULL
hsa00310 1 NULL
hsa00280 1 NULL
hsa00561 1 NULL
hsa00620 1 NULL
hsa00650 1 NULL
hsa00071 1 NULL
hsa00340 1 NULL
hsa00640 1 NULL
hsa00903 1 NULL
hsa00010 1 NULL
hsa00380 2 NULL
hsa00641 2 NULL
hsa00330 2 NULL 求解
(select KEGGXrefs.PathwayID,count(KEGGXrefs.GeneID ) as PathwayCount from GeneXrefsIPI join KEGGXrefs on KEGGXrefs.GeneID = GeneXrefsIPI.EntrezGeneID where GeneXrefsIPI.accession='IPI00796116' group by KEGGXrefs.PathwayID)
aliases on KEGGClass.PathwayID=aliases.PathwayID order by PathwayCount一共三个表
---------------
GeneXrefsIPI
KEGGXrefs
KEGGClass
---------------
KEGGXrefs.GeneID = GeneXrefsIPI.EntrezGeneID
KEGGXrefs.PathwayID=KEGGClass.PathwayID
--------------------------------------------
为什么明明在KEGGClass表中有对应的PathwayID,KEGGClass.ClassName却查出的都是空PathwayID PathwayCount ClassName
hsa00010 1 NULL
hsa00053 1 NULL
hsa00071 1 NULL
hsa00280 1 NULL
hsa00310 2 NULL
——————————————————————————————
如果是改为full join 更能看出问题 好像是KEGGXrefs.PathwayID=KEGGClass.PathwayID不管用
PathwayID PathwayCount ClassName
NULL NULL ggg
NULL NULL hhh
NULL NULL kkk
NULL NULL ttt
NULL NULL test2
NULL NULL rr2
hsa00410 1 NULL
hsa00053 1 NULL
hsa00310 1 NULL
hsa00280 1 NULL
hsa00561 1 NULL
hsa00620 1 NULL
hsa00650 1 NULL
hsa00071 1 NULL
hsa00340 1 NULL
hsa00640 1 NULL
hsa00903 1 NULL
hsa00010 1 NULL
hsa00380 2 NULL
hsa00641 2 NULL
hsa00330 2 NULL 求解
KEGGXrefs.GeneID = GeneXrefsIPI.EntrezGeneID
GeneXrefsIPI.字段=KEGGClass.PathwayID 最好用这个表连接这个KEGGClass
(select KEGGXrefs.PathwayID,count(KEGGXrefs.GeneID ) as PathwayCount from GeneXrefsIPI join KEGGXrefs on KEGGXrefs.GeneID = GeneXrefsIPI.EntrezGeneID where GeneXrefsIPI.accession='IPI00796116' group by KEGGXrefs.PathwayID) AS T
aliases on KEGGClass.PathwayID=aliases.PathwayID order by PathwayCount这里表别名也没有
(select KEGGXrefs.PathwayID,count(KEGGXrefs.GeneID ) as PathwayCount from
GeneXrefsIPI join KEGGXrefs on KEGGXrefs.GeneID = GeneXrefsIPI.EntrezGeneID
where GeneXrefsIPI.accession='IPI00796116' group by KEGGXrefs.PathwayID)
as aliases on KEGGClass.PathwayID=aliases.PathwayID order by PathwayCount加了别名也不行,找不出问题
select aliases.*,KEGGClass.ClassName from KEGGClass full join (select KEGGXrefs.PathwayID,count(KEGGXrefs.GeneID ) as PathwayCount from GeneXrefsIPI join KEGGXrefs on KEGGXrefs.GeneID = GeneXrefsIPI.EntrezGeneID where GeneXrefsIPI.accession='IPI00796116' group by KEGGXrefs.PathwayID) as aliases on KEGGClass.PathwayID=aliases.PathwayID order by PathwayCount----------------------
select KEGGXrefs.PathwayID,count(KEGGXrefs.GeneID ) as PathwayCount from GeneXrefsIPI join KEGGXrefs on KEGGXrefs.GeneID = GeneXrefsIPI.EntrezGeneID where GeneXrefsIPI.accession='IPI00796116' group by KEGGXrefs.PathwayID
中间这一句的查询结果是正确的
PathwayID PathwayCount
hsa00010 1
hsa00053 1
hsa00310 1
hsa00330 2
利用这个查询结果再去KEGGClass表中找ClassName 却显示为空
hsa00010 1 null
hsa00053 1 null
hsa00310 1 null
hsa00330 2 null