您可以使用相关子查询并计算不同类别的数量，如果只有一个类别，则显示否则显示Mixed（假设您在orders表中有一个主键列，如id，并且在order_details表中有一个外键列，如order_id，引用前一个主键）：

SELECT o.*,
       ( SELECT CASE COUNT(DISTINCT category)
                WHEN 1
                THEN MAX(category)
                ELSE 'Mixed'
                END
         FROM   order_details d
         WHERE  o.id = d.order_id ) AS category
FROM   orders o;

其中，对于样本数据：

CREATE TABLE orders (id, name) AS
SELECT 1, 'order1' FROM DUAL UNION ALL
SELECT 2, 'order2' FROM DUAL UNION ALL
SELECT 3, 'order3' FROM DUAL UNION ALL
SELECT 4, 'order4' FROM DUAL;

CREATE TABLE order_details (id, order_id, category) AS
SELECT 1, 1, 'Food' FROM DUAL UNION ALL
SELECT 2, 2, 'Food' FROM DUAL UNION ALL
SELECT 3, 2, 'Book' FROM DUAL UNION ALL
SELECT 4, 3, 'Book' FROM DUAL UNION ALL
SELECT 5, 3, 'Book' FROM DUAL UNION ALL
SELECT 6, 4, 'Food' FROM DUAL UNION ALL
SELECT 7, 4, 'Book' FROM DUAL;

输出：
| ID|名称|产品分类|
| --------------|--------------|--------------|
| 1|订单1|食品类|
| 二|订单2|混合|
| 三|订单3|书|
| 四个|订单4|混合|
fiddle

select 
 case 
   when not exists (select 1
     from order_details d where m.order_id=d.order_id
     and d.category!='Food') then 'Food' --- there is no row having other cathegory than Food
   else 'Mixed'
  end
 ,m.*
from orders m;

或者，它可以是：

with ord_categs (
  select order_id, category, count(distinct category) cnt_categ
    ,row_number() over (partition by order_id order by category) rn
  from order_details
  group by order_id, category
)
select 
  m.*
  ,case
    when coalesce(c.cnt_categ,1)=1 then c.category --- null if no details exist
    else 'Mixed'
  end
from orders m
left join ord_categs c on m.order_id=c.order_id and 1=rn;