mysql - GROUP BY 显示所有不同的列

Question

mysql> select * from products;

+---------+-------------+-----------+------+
| prod_id | prod_source | prod_type | flag |
+---------+-------------+-----------+------+
|       1 | USA         |         2 |    0 |
|       2 | USA         |         2 |    0 |
|       3 | USA         |         2 |    0 |
|       4 | USA         |         3 |    1 |
|       5 | USA         |         3 |    0 |
|       6 | BRA         |         1 |    1 |
|       7 | BRA         |         2 |    1 |
+---------+-------------+-----------+------+

要求的结果:

+-----------+----------+
| prod_type | count(*) |
+-----------+----------+
|         2 |        3 |
|         3 |        0 |
|         4 |        0 |
+-----------+----------+


where condition
 prod_source = USA
 flag=0

但我得到的结果是

+-----------+----------+
| prod_type | count(*) |
+-----------+----------+
|         2 |        3 |
+-----------+----------+

与查询

 SELECT prod_type ,count(*) 
   FROM products
   WHERE prod_source='USA'  and flag=0  
   GROUP by prod_type 
   ORDER by prod_type;

我如何获得美国所有 prod_type 的结果集？即使计数为 0，它也必须显示美国的所有 prod_type。

表定义:

CREATE TABLE `products` (
  `prod_id` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `prod_source` VARCHAR(45) NOT NULL,
  `prod_type` INTEGER UNSIGNED NOT NULL,
  `flag` INTEGER UNSIGNED NOT NULL,
  PRIMARY KEY (`prod_id`)
)
ENGINE = InnoDB;

数据插入:

INSERT INTO `products` 
VALUES (1,'USA',2,0),
       (2,'USA',2,0),
       (3,'USA',2,0),
       (4,'USA',3,1),
       (5,'USA',4,1),
       (6,'BRA',1,1),
       (7,'BRA',2,1);

Answer 1

在 SQL Server 中，我会使用以下内容:

SELECT prod_type,SUM(CASE WHEN flag=0 THEN 1 ELSE 0 END)
FROM products
   WHERE prod_source='USA' 
   GROUP by prod_type 
   ORDER by prod_type

我不能说这是否适用于 MySQL。

(厚颜无耻的编辑以更正 SELECT 子句中的列名)