如何从子查询中消除外部引用

maliks 发表于 Dev

马利克斯

我有一个查询为：

SELECT DISTINCT A2P.p_year [Year], A2P.aid [CoAuthor] 
FROM sub_aminer_author2paper A2P
WHERE pid IN (
               SELECT A2P.pid 
               FROM sub_aminer_author2paper A2P
               JOIN sub_aminer_paper P ON A2P.pid = P.pid
               WHERE DATALENGTH(P.p_abstract) > 0 AND
               A2P.aid IN (
                            SELECT aid 
                            FROM Authors
                          ) AND A2P.p_year BETWEEN 2005 AND 2014
             )
AND A2P.aid NOT IN (
                     SELECT aid 
                     FROM Authors
                   )
ORDER BY Year, CoAuthor

此查询给我的输出为：

Year    CoAuthor
2005    796
2005    947
2005    1032
2005    1740
2005    1960
2005    4045
2005    4472 
...  
...

而我想输出为：

Author   Year   CoAuthor   Venue
677      2005   796        234565
677      2005   947        127634
677      2005   1032       235487
1359     2005   1740       341265
1359     2005   1960       23658
1359     2005   4045       3412
1359     2005   4472       235473
...  
...

我手动添加的列aid是查询的from，即SELECT aid FROM Authors。如何选择？而我也正在使用，A2P.aid NOT IN (SELECT aid FROM Authors)因为我不想Author在CoAuthor列中显示。

托斯滕·凯特纳

因此，您只需要列出每年的所有作者/合著者团队。从sub_aminer_author2paper中选择时，您已经拥有所有作者和合著者，但是您必须确定谁是谁，以及谁与谁一起工作。为此，请使用cte（WITH子句）并从中选择两次：

with a2p as
(
  select 
    aid, pid, p_year, 
    case when aid in (select aid from authors) then 'author' else 'co-author' end as what
  from sub_aminer_author2paper
  where p_year between 2005 and 2014
  and pid in (select pid from sub_aminer_paper where datalength(p_abstract) > 0)
)
select distinct a.aid as [Author], a.p_year as [Year], c.aid as [CoAuthor] 
from (select * from a2p where what = 'author') a
join (select * from a2p where what = 'co-author') c on c.pid = a.pid
order by ...;

本文收集自互联网，转载请注明来源。

如有侵权，请联系[email protected] 删除。