如何基于用户属性关联表实现MySQL多表垂直连接？

Hey there! Let's walk through how to work with multi-table joins (including what you're calling vertical joins) in MySQL using your experimental schema. First, let's get your table structures clear—plus I'll fill in a logical missing piece to make the data meaningful, since right now we can't tie attribute values to their actual attribute types.

Your Table Structures (With Logical Adjustment) #

I’ve added an attribute_id column to the attribute_values table (it should link to attributes.id to clarify which attribute a value belongs to) and filled in the assumed schema for user_attribute_values—the junction table that connects users to their specific attribute values.

users table #

attributes table #

attribute_values table (corrected with foreign key) #

user_attribute_values table (junction table) #

1. Basic Horizontal Join (Combine Columns Across Tables) #

This is the most common use case for your schema: linking all tables to get a user’s attributes and their corresponding values in one result set. We’ll use INNER JOIN to connect each table via their foreign keys:

SELECT 
    u.id AS user_id,
    a.attribute,
    av.attribute_value
FROM 
    users u
INNER JOIN 
    user_attribute_values uav ON u.id = uav.user_id
INNER JOIN 
    attribute_values av ON uav.attribute_value_id = av.id
INNER JOIN 
    attributes a ON av.attribute_id = a.id;

Result: #

2. Pivot Rows to Columns (Turn Attribute-Value Pairs into a Single User Row) #

If you want to transform the row-based attribute data into columns (so you have one row per user with name and age as separate columns), this is called pivoting. In MySQL, you can do this with conditional aggregation:

SELECT 
    u.id AS user_id,
    MAX(CASE WHEN a.attribute = 'name' THEN av.attribute_value END) AS name,
    MAX(CASE WHEN a.attribute = 'age' THEN av.attribute_value END) AS age
FROM 
    users u
INNER JOIN 
    user_attribute_values uav ON u.id = uav.user_id
INNER JOIN 
    attribute_values av ON uav.attribute_value_id = av.id
INNER JOIN 
    attributes a ON av.attribute_id = a.id
GROUP BY 
    u.id;

Result: #

3. Vertical Join (Combine Rows from Different Tables) #

If you actually meant a true vertical join (combining rows from separate tables into a single result set), use UNION or UNION ALL. For example, to list all attribute names and their values in one column:

-- UNION ALL keeps duplicates; use UNION to remove them
SELECT attribute AS combined_value FROM attributes
UNION ALL
SELECT attribute_value AS combined_value FROM attribute_values;

Result: #

Key Tips for Your Experiments #

• Always use table aliases (like u for users) to keep queries clean and avoid ambiguity.
• INNER JOIN only returns rows where there’s a match in all joined tables. If you want to include users who have no attributes at all, swap it for LEFT JOIN.
• The user_attribute_values junction table is critical—it’s what lets you associate specific users with their specific attribute values. Without it, you can’t link the other tables meaningfully.

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