使用GROUP BY-MYSQL基于行级项目获取列

Question

MYSQL表

上下文：对于上表，我想创建一个视图"VIEW_A"；在组级别上具有order_no和Manufacture列。

where Manufacture  = A if line level items have `manf` = A or "".
where Manufacture  = B if line level items have `manf` = B
where Manufacture  = Mixed if line level items have `manf` A and B.
How can I solve the issue?

Answer 1

您可以计算行项目"A"和行项目"B"，然后将这些数字与每个订单的行项目总数进行比较：

SELECT order_no, 
CASE WHEN sum(CASE WHEN `manf` = 'A' THEN 1 END) = count(*) THEN 'A'
WHEN sum(CASE WHEN `manf` = 'B' THEN 1 END) = count(*) THEN 'B'
ELSE 'Mixed' END Manufacture
FROM mytable
GROUP BY order_no;