MySQL单列存储结构下多条件过滤及透视表输出方法问询

Hey Amir, great question—since you already know your way around SQL, let's dive into the core logic behind filtering and pivoting this EAV (Entity-Attribute-Value) structure, which is a common pattern but can feel counterintuitive at first.

First, let's ground this: in your table, each entity (the record you actually care about) is split across multiple rows—one row per attribute (fieldID + fieldValue). That means a single row can never have both fieldID=2 and fieldID=11 at the same time. So the core problem isn't filtering rows—it's filtering entities that have both of those attribute-value pairs present.

Here are the most common approaches, each tied directly to that core logic:

1. Using EXISTS Subqueries (Explicit Entity Validation) #

This method directly checks: "Does this entity have a row matching condition A, AND does it also have a row matching condition B?" It’s straightforward and makes the logic crystal clear.

SELECT DISTINCT entityID
FROM your_table t
WHERE 
  -- Verify the entity has fieldID=2 with value 'High'
  EXISTS (
    SELECT 1
    FROM your_table t2
    WHERE t2.entityID = t.entityID
      AND t2.fieldID = 2
      AND t2.fieldValue = 'High'
  )
  -- AND verify the same entity has fieldID=11 with value 'Discrepancy'
  AND EXISTS (
    SELECT 1
    FROM your_table t3
    WHERE t3.entityID = t.entityID
      AND t3.fieldID = 11
      AND t3.fieldValue = 'Discrepancy'
  );

The DISTINCT ensures we only get each qualifying entity once (since each entity has multiple rows in the table).

2. Using GROUP BY + HAVING (Aggregate Validation) #

Here, we first collect all rows that match either condition, group them by entity, then check if the entity has both conditions satisfied.

SELECT entityID
FROM your_table
WHERE 
  (fieldID = 2 AND fieldValue = 'High')
  OR (fieldID = 11 AND fieldValue = 'Discrepancy')
GROUP BY entityID
-- Ensure the entity has both distinct fieldIDs matching our criteria
HAVING COUNT(DISTINCT fieldID) = 2;

The WHERE clause narrows down to relevant rows, then GROUP BY clusters them by entity. The HAVING clause confirms the entity has exactly 2 unique fieldIDs (one for each condition)—meaning it satisfied both.

3. Using Self-Joins (Join Entities to Their Own Attributes) #

Think of this as treating each attribute as a separate "table" for the same entity. We join the table to itself on entityID, so each join represents a required attribute-value pair.

SELECT t1.entityID
FROM your_table t1
JOIN your_table t2 
  ON t1.entityID = t2.entityID
WHERE 
  t1.fieldID = 2 AND t1.fieldValue = 'High'
  AND t2.fieldID = 11 AND t2.fieldValue = 'Discrepancy';

Only entities that have both attributes will pass the join condition—since t1 holds the fieldID=2 row and t2 holds the fieldID=11 row for the same entity.

Once you've filtered the qualifying entities, converting the EAV rows into a pivot table (columns for each fieldID) uses conditional aggregation. The logic is: for each entity, grab the fieldValue for specific fieldIDs and turn them into columns.

Here's how to combine filtering and pivoting in one query, using the EXISTS approach for clarity:

SELECT 
  entityID,
  -- Pivot fieldID=2 into a column
  MAX(CASE WHEN fieldID = 2 THEN fieldValue END) AS fieldID2_Value,
  -- Pivot fieldID=11 into a column
  MAX(CASE WHEN fieldID = 11 THEN fieldValue END) AS fieldID11_Value
FROM your_table t
WHERE 
  EXISTS (
    SELECT 1
    FROM your_table t2
    WHERE t2.entityID = t.entityID
      AND t2.fieldID = 2
      AND t2.fieldValue = 'High'
  )
  AND EXISTS (
    SELECT 1
    FROM your_table t3
    WHERE t3.entityID = t.entityID
      AND t3.fieldID = 11
      AND t3.fieldValue = 'Discrepancy'
  )
GROUP BY entityID;

We use MAX() (or MIN() works too) because each entity has only one fieldValue per fieldID—the aggregate function just picks that single value out of the grouped rows.

Alternatively, you can filter directly in the HAVING clause for a more concise pivot:

SELECT 
  entityID,
  MAX(CASE WHEN fieldID = 2 THEN fieldValue END) AS fieldID2_Value,
  MAX(CASE WHEN fieldID = 11 THEN fieldValue END) AS fieldID11_Value
FROM your_table
GROUP BY entityID
HAVING 
  MAX(CASE WHEN fieldID = 2 THEN fieldValue END) = 'High'
  AND MAX(CASE WHEN fieldID = 11 THEN fieldValue END) = 'Discrepancy';

This checks the pivoted column values directly, making the filter logic align with the final output structure.

All these methods boil down to one core idea: in EAV, multi-condition filtering isn't about rows meeting multiple criteria—it's about entities having all the required attribute-value pairs present across their rows. The pivot step then transforms those rows back into a more intuitive columnar format.

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