如何在HiveQL中仅输出ID字段？或保存结果后提取ID列

Absolutely! You’ve got a couple solid options here—including the exact approach you’re asking about (saving the query result first then extracting the ID), plus a more streamlined method that cuts out the intermediate step entirely. Let me walk you through both:

Option 1: Tweak your original query directly (most efficient) #

Since you only need the id in your final output—you’re just using weight to sort the results—you can simplify the query to only select b.id while still sorting by m.weight. This avoids any extra storage or subqueries:

SELECT DISTINCT b.id
FROM batting b
JOIN Master m ON (m.id = b.id)
WHERE b.year = 2005 AND b.triples > 4
ORDER BY m.weight DESC
LIMIT 1;

Option 2: Save the intermediate result first, then query the ID #

If you need to keep that full intermediate result (with both id and weight) for later use, you’ve got two common ways to do this in Hive:

Using a Common Table Expression (CTE)

CTEs let you define a temporary result set that you can query right after—no need to create a persistent table:

WITH temp_results AS (
    SELECT DISTINCT b.id, m.weight AS weight
    FROM batting b
    JOIN Master m ON (m.id = b.id)
    WHERE b.year = 2005 AND b.triples > 4
    ORDER BY weight DESC
    LIMIT 1
)
SELECT id FROM temp_results;

Using a Temporary Table

If you need to reuse the intermediate result multiple times in your session, a temporary table is a great choice (it gets automatically deleted when your Hive session ends):

-- Create a temporary table to store the full result
CREATE TEMPORARY TABLE temp_batting_data AS
SELECT DISTINCT b.id, m.weight AS weight
FROM batting b
JOIN Master m ON (m.id = b.id)
WHERE b.year = 2005 AND b.triples > 4
ORDER BY weight DESC
LIMIT 1;

-- Query just the ID column from the temporary table
SELECT id FROM temp_batting_data;

If you need the intermediate data to stick around longer, just remove the TEMPORARY keyword to create a persistent table instead.

Either approach works perfectly—it just depends on whether you need to hang onto that full intermediate result or not!

内容的提问来源于stack exchange，提问作者zach