将字段与GROUP BY ROLLUP组合

Question

我想有一个查询，显示我公司签订的每一份合同，并按功能分组。

我的问题是我有很多功能，例如：

• 合作伙伴
• 卖方
• 经理
• 船长
• 等等

我想显示3行，其中一行包含"；Partners"；，其他由所有"；实习生；(每个不是合作伙伴的人(，最后一个是用ROLLUP实现的，总共两个。但我似乎不能只显示这3行，我的查询将所有不是合作伙伴的内容都变成名称；实习生"；，但是没有结合这些细胞，所以我得到了这个结果：

+--------------+-------------+--------------+---------+
| Seller Type  |  Installed  |   Scheduled  |  Total  |
+--------------+-------------+--------------+---------+
| Intern       |      1      |      0       |    1    |
+--------------+-------------+--------------+---------+
| Intern       |      3      |      0       |    3    |
+--------------+-------------+--------------+---------+
| Partner      |     10      |      5       |    15   |
+--------------+-------------+--------------+---------+
| Intern       |     19      |      10      |    29   |
+--------------+-------------+--------------+---------+
| Total        |     33      |      15      |    48   |
+--------------+-------------+--------------+---------+

我的目标是把所有不是"；合伙人；进入同一行，并显示如下：

+--------------+-------------+--------------+---------+
| Seller Type  |  Installed  |   Scheduled  |  Total  |
+--------------+-------------+--------------+---------+
| Partner      |     10      |      5       |    15   |
+--------------+-------------+--------------+---------+
| Intern       |     23      |      10      |    33   |
+--------------+-------------+--------------+---------+
| Total        |     33      |      15      |    48   |
+--------------+-------------+--------------+---------+

表CM3的我的数据

+--------------+-------------+--------------+
| u_func       |     cm      |     name     |
+--------------+-------------+--------------+
| Captain      |      1      |     Chris    |
+--------------+-------------+--------------+
| Manager      |      2      |     Mary     |
+--------------+-------------+--------------+
| Partner      |      3      |    Anthony   |
+--------------+-------------+--------------+
| Seller       |      4      |    Hannah    |
+--------------+-------------+--------------+

表BO的我的数据

+--------------+-------------+--------------+
| stamp        |    seller   |      sta     |
+--------------+-------------+--------------+
| PO109832910  |      1      |   Installed  |
+--------------+-------------+--------------+
| PO389213201  |      2      |   Installed  |
+--------------+-------------+--------------+
| PO930821639  |      3      |   Scheduled  |
+--------------+-------------+--------------+
| PO987583213  |      4      |   Scheduled  |
+--------------+-------------+--------------+

我的查询是

SELECT CASE 
WHEN GROUPING(CM3.u_func)=1 THEN 'TOTAL'
WHEN CM3.u_func <> 'Partner' THEN 'Intern'
END "Seller Type"
, COUNT(CASE 
WHEN BO.stat = 'Installed' THEN 1 
END) "Installed"
, COUNT(CASE
WHEN BO.stat = 'Scheduled' THEN 1
END) "Scheduled"
, COUNT(CASE
WHEN BO.stat IN ('Installed','Scheduled') THEN 1
END) "Total"
FROM BO
JOIN CM3 ON cm3.cm = BO.seller
GROUP BY ROLLUP(CM3.u_func)

Answer 1

您需要GROUP BY来镜像SELECT语句中的逻辑(也需要一些调整(：

SELECT CASE 
WHEN GROUPING
(CASE 
WHEN CM3.u_func = 'Partner' THEN CM3.u_func 
ELSE 'Intern'
END) = 1 THEN 'TOTAL'
ELSE CASE 
WHEN CM3.u_func = 'Partner' THEN CM3.u_func 
ELSE 'Intern'
END 
END "Seller Type"
, COUNT(CASE 
WHEN BO.stat = 'Installed' THEN 1 
END) "Installed"
, COUNT(CASE
WHEN BO.stat = 'Scheduled' THEN 1
END) "Scheduled"
, COUNT(CASE
WHEN BO.stat IN ('Installed','Scheduled') THEN 1
END) "Total"
FROM BO
JOIN CM3 ON cm3.cm = BO.seller
GROUP BY CASE 
WHEN CM3.u_func = 'Partner' THEN CM3.u_func ELSE 'Intern'
END WITH ROLLUP