Hive怎么用子查询作自连接呢

[孙悟充]

hive> SELECT tb2.name
> FROM(
> SELECT tb1.season, tb1.name, ROW_NUMBER() OVER(ORDER BY SUBSTR(tb1.season, 2, 4) ) AS number --增加序号
> FROM(
> SELECT a.season, a.name, a.scores --赛季，得分王，得分
> FROM (
> SELECT season, max(scores) scores
> FROM player
> GROUP BY season
> )
> b JOIN player a ON a.season = b.season AND a.scores = b.scores
> )tb1
> )tb2, tb2 tb3
> WHERE tb2.season=tb3.season AND tb2.name=tb3.name AND tb2.number=tb3.number-1;
FAILED: SemanticException [Error 10001]: Line 13:6 Table not found 'tb2'


子查询结果是：
"1996-1997" "迈克尔 乔丹" 1
"1997-1998" "迈克尔 乔丹" 2
"1998-1999" "阿伦 艾弗森" 3
"1999-2000" "沙奎尔 奥尼尔" 4
"2000-2001" "特雷西 麦格雷迪" 5
"2001-2002" "特雷西 麦格雷迪" 6
"2002-2003" "科比 布莱恩特" 7
"2003-2004" "德克 诺维茨基" 8
"2005-2006" "吉尔伯特 阿里纳斯" 9
"2006-2007" "科比 布莱恩特" 10
"2007-2008" "科比 布莱恩特" 11
"2008-2009" "勒布朗 詹姆斯" 12
"2009-2010" "德怀恩 韦德" 13
"2010-2011" "凯文 杜兰特" 14
"2011-2012" "勒布朗 詹姆斯" 15
"2012-2013" "凯文 杜兰特" 16
"2013-2014" "凯文 杜兰特" 17
"2014-2015" "安东尼 戴维斯" 18
"2015-2016" "凯文 杜兰特" 19
"2016-2017" "拉塞尔 威斯布鲁克" 20

[二月十六]

SELECT tb2.name
FROM
(
    SELECT tb1.season,
           tb1.name,
           ROW_NUMBER() OVER (ORDER BY SUBSTR(tb1.season, 2, 4)) AS number --增加序号
    FROM
    (
        SELECT a.season,
               a.name,
               a.scores --赛季，得分王，得分
        FROM
        (SELECT season, MAX(scores) scores FROM player GROUP BY season) b
            JOIN player a
                ON a.season = b.season
                   AND a.scores = b.scores
    ) tb1
) tb2 , tb3
WHERE tb2.season = tb3.season
      AND tb2.name = tb3.name
      AND tb2.number = tb3.numbe;

[zhaoyg-code]

自连接你确定可以这样写？误人子弟，解决方案看这里 https://blog.csdn.net/zhongqi2513/article/details/93610120

[zhaoyg-code]

解决问题的 给我点个赞即可