SELECT f.PAR_FAULT_CODE AS "parFaultCode", (SELECT t.FAULT_NAME FROM FAULT_TYPE t WHERE f.PAR_FAULT_CODE = t.FAULT_CODE) AS "faultName", COALESCE( r.REPORT_DEPT_CODE, 'N/A' ) AS "reportDeptCode", COUNT( r.REPORT_DEPT_CODE ) AS "report", COUNT(*) AS "reportCount" FROM FAULT_TYPE f LEFT JOIN FAULT_REPORT r ON f.FAULT_CODE = r.FAULT_CODE GROUP BY f.PAR_FAULT_CODE, f.FAULT_NAME, COALESCE( r.REPORT_DEPT_CODE, 'N/A' ), f.FAULT_CODE ORDER BY f.PAR_FAULT_CODE, f.FAULT_NAME按parfaultcode的report的和加到一起放到reportCount字段怎么修改
时间: 2024-03-20 16:43:10 浏览: 16
可以使用以下SQL语句来实现:
```
SELECT f.PAR_FAULT_CODE AS "parFaultCode",
(SELECT t.FAULT_NAME FROM FAULT_TYPE t WHERE f.PAR_FAULT_CODE = t.FAULT_CODE) AS "faultName",
COALESCE(r.REPORT_DEPT_CODE, 'N/A') AS "reportDeptCode",
COUNT(r.REPORT_DEPT_CODE) AS "report",
SUM(COUNT(r.REPORT_DEPT_CODE)) OVER (PARTITION BY f.PAR_FAULT_CODE) AS "reportCount"
FROM FAULT_TYPE f
LEFT JOIN FAULT_REPORT r ON f.FAULT_CODE = r.FAULT_CODE
GROUP BY f.PAR_FAULT_CODE, f.FAULT_NAME, COALESCE(r.REPORT_DEPT_CODE, 'N/A'), f.FAULT_CODE
ORDER BY f.PAR_FAULT_CODE, f.FAULT_NAME
```
这里使用了窗口函数 `SUM`,对每个 `parFaultCode` 分组的 `report` 字段求和并放到 `reportCount` 字段中。
相关问题
SELECT f.PAR_FAULT_CODE AS "parFaultCode", f.FAULT_NAME AS "faultName", COALESCE( r.REPORT_DEPT_CODE, 'N/A' ) AS "reportDeptCode", COUNT( r.REPORT_DEPT_CODE ) AS "report", ( SELECT COUNT( * ) FROM FAULT_REPORT d WHERE d.FAULT_CODE = f.FAULT_CODE ) AS "reportCount" FROM FAULT_TYPE f LEFT JOIN FAULT_REPORT r ON f.FAULT_CODE = r.FAULT_CODE GROUP BY f.PAR_FAULT_CODE, f.FAULT_NAME, COALESCE( r.REPORT_DEPT_CODE, 'N/A' ) ORDER BY f.PAR_FAULT_CODE, f.FAULT_NAME为什么在oracle中报不是group by表达式,请帮我正确修改
在Oracle中,如果SELECT语句中的字段包含聚合函数(如COUNT、SUM等),则GROUP BY子句中必须包含所有未聚合的字段。在你的查询中,COUNT(r.REPORT_DEPT_CODE)是一个聚合函数,但是GROUP BY子句中只包含了f.PAR_FAULT_CODE、f.FAULT_NAME和COALESCE(r.REPORT_DEPT_CODE, 'N/A')三个字段,没有包含COUNT(r.REPORT_DEPT_CODE)。因此,Oracle会报错,提示你需要将COUNT(r.REPORT_DEPT_CODE)也包含在GROUP BY子句中。
正确的查询语句如下:
SELECT f.PAR_FAULT_CODE AS "parFaultCode",
f.FAULT_NAME AS "faultName",
COALESCE(r.REPORT_DEPT_CODE, 'N/A') AS "reportDeptCode",
COUNT(r.REPORT_DEPT_CODE) AS "report",
(SELECT COUNT(*) FROM FAULT_REPORT d WHERE d.FAULT_CODE = f.FAULT_CODE) AS "reportCount"
FROM FAULT_TYPE f
LEFT JOIN FAULT_REPORT r ON f.FAULT_CODE = r.FAULT_CODE
GROUP BY f.PAR_FAULT_CODE, f.FAULT_NAME, COALESCE(r.REPORT_DEPT_CODE, 'N/A'), f.FAULT_CODE
ORDER BY f.PAR_FAULT_CODE, f.FAULT_NAME
SELECT f.PAR_FAULT_CODE AS "parFaultCode", f.FAULT_NAME AS "faultName", COALESCE( r.REPORT_DEPT_CODE, 'N/A' ) AS "reportDeptCode", COUNT( r.REPORT_DEPT_CODE ) AS "report", SUM(COUNT(r.REPORT_DEPT_CODE)) OVER (PARTITION BY f.FAULT_CODE) AS "reportCount" FROM FAULT_TYPE f LEFT JOIN FAULT_REPORT r ON f.FAULT_CODE = r.FAULT_CODE GROUP BY f.PAR_FAULT_CODE, f.FAULT_NAME, COALESCE( r.REPORT_DEPT_CODE, 'N/A' ), f.FAULT_CODE ORDER BY f.PAR_FAULT_CODE, f.FAULT_NAME在fauleName加一个标题为合计字段 并且根据reportDeptCode分组算出对应的reportCount
可以在SELECT语句中使用CASE WHEN语句来对 faultName 进行处理,添加 "合计" 字段。具体的SQL语句如下所示:
```
SELECT f.PAR_FAULT_CODE AS "parFaultCode",
CASE WHEN COALESCE(r.REPORT_DEPT_CODE, 'N/A') = 'N/A' THEN f.FAULT_NAME
ELSE f.FAULT_NAME || ' 合计' END AS "faultName",
COALESCE(r.REPORT_DEPT_CODE, 'N/A') AS "reportDeptCode",
COUNT(r.REPORT_DEPT_CODE) AS "report",
SUM(COUNT(r.REPORT_DEPT_CODE)) OVER (PARTITION BY f.FAULT_CODE) AS "reportCount"
FROM FAULT_TYPE f
LEFT JOIN FAULT_REPORT r ON f.FAULT_CODE = r.FAULT_CODE
GROUP BY f.PAR_FAULT_CODE, f.FAULT_NAME, COALESCE(r.REPORT_DEPT_CODE, 'N/A'), f.FAULT_CODE
ORDER BY f.PAR_FAULT_CODE, f.FAULT_NAME;
```
在这个SQL语句中,我们使用了 CASE WHEN 语句来判断 reportDeptCode 是否为 'N/A',如果是则不添加 "合计" 字段,否则在 faultName 后面添加 " 合计"。最后按照指定的字段进行分组和排序,计算 reportCount。