SQL作业求助：如何关联varchar与integer实现表连接？

数据老陈

2026-5-15

解决思路与SQL实现

Hey there! Let's work through this problem together. The error you're seeing happens because your order_items (either a comma-separated string or a character varying array) can't directly match the integer menu_items_number in the menu table—we need to break down the order items, convert their type properly, then map them to the menu names before putting everything back together.

Step-by-Step Breakdown

Split the order items: First, we need to turn the grouped order item numbers into individual rows. If order_items is a comma-separated string (like '1,1,2,3'), use STRING_TO_ARRAY to convert it into an array, then UNNEST to split that array into separate rows. If it's already a character varying[] array, skip the STRING_TO_ARRAY step and just use UNNEST.
Fix type mismatch: Convert each split item number from text to an integer so it can match the menu_items_number (which is an integer type).
Map to menu names: Join the split items with the menu table to get the corresponding dish names.
Reassemble the order: Group the results by order_number and use STRING_AGG to concatenate the dish names back into a single comma-separated string.

Working SQL Query

If order_items is a comma-separated string:

SELECT 
    o.order_number,
    STRING_AGG(m.menu_item, ', ') AS order_item_names
FROM 
    d.orders o
CROSS JOIN UNNEST(STRING_TO_ARRAY(o.order_items, ',')) AS item_num
JOIN 
    d.menu m ON CAST(item_num AS INTEGER) = m.menu_items_number
GROUP BY 
    o.order_number;

If order_items is already a character varying[] array:

SELECT 
    o.order_number,
    STRING_AGG(m.menu_item, ', ') AS order_item_names
FROM 
    d.orders o
CROSS JOIN UNNEST(o.order_items) AS item_num
JOIN 
    d.menu m ON CAST(item_num AS INTEGER) = m.menu_items_number
GROUP BY 
    o.order_number;