您可以按以下方式使用聚合：

SELECT * FROM ATTRIBUTE_TAB
  WHERE ID IN
  (
    SELECT ID FROM ATTRIBUTE_TAB
    WHERE VALUE_NO='6000' OR VALUE_TEXT='TEMPERED'OR VALUE_TEXT='BLUE'
    GROUP BY ID
    HAVING COUNT(*) = 3 -- or maybe HAVING COUNT(DISTINCT ATTRIBUTE) = 3 if duplicates are possible 
  )
ORDER BY ID, ATTRIBUTE DESC

demo

一种选择是 * 重新格式化 * 源表，以便每个“值”都适合自己的列（这就是我的查询中的tempCTE所做的）。然后，提取您需要的数据非常简单。
样本数据：

SQL> with attribute_tab (id, attribute, value_no, value_text) as
  2    (select 1, 'WIDTH', 6000, null        from dual union all
  3     select 1, 'COLOR', null, 'BLUE'      from dual union all
  4     select 1, 'GLASS', null, 'TEMPERED'  from dual union all
  5     --
  6     select 2, 'WIDTH', 6500, null        from dual union all
  7     select 2, 'COLOR', null, 'YELLOW'    from dual union all
  8     select 2, 'GLASS', null, 'BIRD SAFE' from dual union all
  9     --
 10     select 4, 'WIDTH', 6000, null        from dual union all
 11     select 4, 'COLOR', null, 'BLUE'      from dual union all
 12     select 4, 'GLASS', null, 'TEMPERED'  from dual union all
 13     --
 14     select 6, 'WIDTH', 6500, null        from dual union all
 15     select 6, 'COLOR', null, 'BLUE'      from dual union all
 16     select 6, 'GLASS', null, 'BIRD SAFE' from dual
 17    ),

查询从这里开始：

18  temp as
 19    (select id,
 20       max(case when attribute = 'WIDTH' then value_no end) width,
 21       max(case when attribute = 'COLOR' then value_text end) color,
 22       max(case when attribute = 'GLASS' then value_text end) glass
 23     from attribute_tab
 24     group by id
 25    )
 26  select *
 27  from temp
 28  where width = 6000
 29    and color = 'BLUE'
 30    and glass = 'TEMPERED'
 31  order by id;

        ID      WIDTH COLOR     GLASS
---------- ---------- --------- ---------
         1       6000 BLUE      TEMPERED
         4       6000 BLUE      TEMPERED

SQL>