I write such code in hiveql.

SELECT
    ttt1.rid,
    a,
    s,
    ttt2.num
FROM
    (
        (
            SELECT
                rid,
                r ['a'] AS a,
                r ['s'] AS s
            FROM
                l
            WHERE
                dt BETWEEN '20210914'
                AND '20210917'
                AND r ['act'] = 3
                AND r ['pos'] = 1
                AND r ['s'] = 50011
                AND r ['c'] = 'anti'
        ) ttt1
        JOIN (
            SELECT
                r ['a'] AS a,
                r ['s'] AS s,
                count(1) AS num
            FROM
                l
            WHERE
                dt BETWEEN '20210914'
                AND '20210917'
                AND r ['act'] = 3
                AND r ['pos'] = 1
                AND r ['s'] = 50011
                AND r ['c'] = 'anti'
            GROUP BY
                r ['s'],
                r ['a']
        ) ttt2 ON (
            ttt1.a = ttt2.a
            AND ttt1.s = ttt2.s
        )
    )

I am wondering why it got ambiguous err.

line 5:5: Column 'c' is ambiguous

CUZ a and s are the join keys for these two tables, it could ignore the <TABLE_NAME> before <COLUMN_NAME>.
Could anyone give me some explaination?
Thanks in advance.

CodePudding user response：

i reckon the problem is in the final select, you have not specified from which tables the a and s is to be selected from

SELECT
    ttt1.rid,
    a, --change this to ttt1.a
    s, --change this to ttt1.s 
    ttt2.num