sql-server - 如何从子查询中消除外部引用(How to eliminate outer reference from subquery)

Question

I have a query as:

(我有一个查询：)

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

This query gives me output as:

(此查询给我的输出为：)

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

Whereas I want to have output as:

(而我想输??出为：)

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
...  
...

The column which I add manually are the aid 's from part of query ie SELECT aid FROM Authors .

(我手动添加列是aid “从查询即部S SELECT aid FROM Authors 。)

How can it be selected ?

(如何选择？)

Whereas I'm also using A2P.aid NOT IN (SELECT aid FROM Authors) because I don't want to display Author in CoAuthor column.

(而我也使用A2P.aid NOT IN (SELECT aid FROM Authors)因为我不想在CoAuthor列中显示Author 。)

  ask by maliks translate from so

Answer 1

So you simply want to list all author / co-author teams per year.

(因此，您只想列出每年的所有作者/合著者团队。)

When selecting from sub_aminer_author2paper you already have all authors and co-authors, but you must determine who is who and who worked with whom.

(从sub_aminer_author2paper中进行选择时，您已经拥有所有作者和合著者，但是您必须确定谁是谁以及与谁合作。)

In order to do so use a cte (a WITH clause) and select twice from it:

(为此，请使用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 ...;