<think>我们有一个复杂的SQL查询,它使用UNION将两个查询合并:第一个查询按部门分组统计数据,第二个查询计算总计(合计)。现在需求是:让总计行显示在最后一行,其他行按照punches_In_Total降序排列。
分析:
第一个查询按部门分组(包括待分配),第二个查询是合计(使用固定字符串'合计'作为responsible_Dept)。我们想要的结果是:先显示各个部门的行(按punches_In_Total降序),最后显示合计行。
解决方案:
我们可以将整个查询作为子查询,然后在外层进行排序。但是注意,合计行我们希望它总是在最后,所以我们可以为合计行设置一个特殊的排序值,而其他行则按punches_In_Total降序。
具体做法:
1. 在子查询中,为每一行增加一个用于排序的列。对于非合计行,我们使用0作为排序标记,并按punches_In_Total降序;对于合计行,我们使用1作为排序标记(这样它就会排在最后,因为0<1,升序时0在前,但我们希望合计在最后,所以可以按这个标记升序,然后非合计行再按punches_In_Total降序)。
2. 或者,我们可以这样:先按是否是合计行排序(合计行排最后),然后按punches_In_Total降序。但是注意,合计行的punches_In_Total是总和,通常很大,如果我们直接按punches_In_Total降序,合计行可能排在最前面。所以我们需要在排序时区分合计行和其他行。
我们可以在外层查询中这样排序:
先按是否为合计行(我们可以在子查询中增加一个标记列,例如:当responsible_Dept='合计'时,标记为1,否则为0)升序(这样合计行在最后),然后对于非合计行,按punches_In_Total降序。
但是注意:在合计行中,punches_In_Total是总和,我们不想让它参与和其他行的降序排列,而是希望它单独在最后。所以我们可以这样:
按标记列升序(非合计行为0,合计行为1,这样非合计行在前,合计行在后),然后在非合计行内部,按punches_In_Total降序。
步骤:
1. 将原查询作为子查询,并添加一列(例如:sort_order)用于排序。
在第一个查询(部门分组)中,sort_order设置为0,并且我们保留responsible_Dept和punches_In_Total。
在第二个查询(合计)中,sort_order设置为1。
2. 然后外层查询选择所有列,并按照sort_order升序(这样合计行在最后),然后对于sort_order=0的行(即非合计行)按punches_In_Total降序,对于sort_order=1的行(合计行)无所谓顺序,因为只有一行。
但是注意:我们无法在同一个排序中混合升序和降序?实际上可以在ORDER BY子句中指定多个条件,每个条件可以指定不同的排序方向。
我们可以这样写:
ORDER BY sort_order ASC,
CASE WHEN sort_order=0 THEN punches_In_Total END DESC,
responsible_Dept
但是,对于合计行,punches_In_Total的值可能很大,但我们并不想用它来排序(因为我们已经用sort_order将合计行放在最后了),所以我们可以这样:
ORDER BY sort_order,
CASE WHEN sort_order=0 THEN punches_In_Total END DESC,
CASE WHEN sort_order=0 THEN responsible_Dept END
但是这样写,对于合计行,CASE WHEN sort_order=0 THEN punches_In_Total 会返回NULL,所以按NULL排序(不影响,因为合计行已经按sort_order排到后面了)。同时,我们可能需要考虑在非合计行中punches_In_Total相同的情况,可以再按responsible_Dept排序(升序或降序)。这里按responsible_Dept升序。
或者,我们可以更简单:先按sort_order升序,然后按punches_In_Total降序(但是这样合计行因为punches_In_Total很大,会排在最前面)——这不符合要求。所以必须区分。
因此,我们采用:
ORDER BY
sort_order ASC,
CASE WHEN sort_order=0 THEN punches_In_Total END DESC,
responsible_Dept -- 这里对于非合计行,我们按responsible_Dept排序,避免相同punches_In_Total时的顺序问题,但合计行的responsible_Dept是'合计',我们并不关心,因为sort_order已经确保它在最后。
但是,合计行的sort_order为1,非合计行为0,所以第一排序字段升序,非合计行(0)在前,合计行(1)在后。然后非合计行按punches_In_Total降序,合计行的punches_In_Total将不被考虑(因为CASE WHEN sort_order=0才返回punches_In_Total,否则返回NULL,而NULL在排序中通常被当作最小值,在降序中会排在最后,但这里由于sort_order已经区分,所以没关系)。
另一种更清晰的做法是:在非合计行中,我们按punches_In_Total降序,而合计行我们不参与这个排序,我们可以给合计行一个固定的最小值(或最大值)来确保它在最后?但是这样比较麻烦。
我推荐使用第一种方法:增加一个排序标记列,然后分两个条件排序。
修改原查询:
我们给两个查询都加上一个sort_order列,第一个查询(部门分组)的sort_order设为0,第二个查询(合计)设为1。
然后在外层用ORDER BY sort_order, ... 排序。
具体修改如下:
将原查询包装为子查询,并添加sort_order列:
SELECT
responsible_Dept,
punches_In_Total,
... -- 其他所有列
, sort_order -- 我们新增的排序标记
FROM (
-- 第一个查询:按部门分组
SELECT
nvl(b.NAME,'待分配') responsible_Dept,
... , -- 其他列
0 as sort_order -- 标记为0
FROM ...
GROUP BY b.NAME
UNION
-- 第二个查询:合计
SELECT
'合计' responsible_Dept,
... , -- 其他列
1 as sort_order -- 标记为1
FROM ...
) t
ORDER BY sort_order, -- 先按sort_order升序(0在前,1在后)
CASE WHEN sort_order=0 THEN punches_In_Total END DESC, -- 对于非合计行,按punches_In_Total降序
responsible_Dept -- 然后按responsible_Dept排序(对于非合计行,当punches_In_Total相同时按responsible_Dept排序,合计行responsible_Dept是'合计',但此时sort_order=1,所以不影响)
注意:在合计行中,CASE WHEN sort_order=0 THEN punches_In_Total 会返回NULL,所以降序排列时NULL会排在最后,但是因为sort_order=1的行已经排在后面了,所以合计行会紧接着非合计行之后(即最后)。
但是,我们可能希望合计行严格在最后,而且非合计行内部按punches_In_Total降序。上面的排序可以满足。
另外,我们也可以这样:
ORDER BY sort_order ASC,
CASE sort_order
WHEN 0 THEN punches_In_Total
END DESC,
responsible_Dept
或者,为了避免NULL值在降序时被放在最后(而合计行我们并不希望参与这个排序),我们可以用另一种方式:在第一个排序条件中,我们让合计行排最后,非合计行按punches_In_Total降序,可以这样:
ORDER BY
sort_order, -- 0和1,升序则0在前1在后
CASE WHEN sort_order=0 THEN -punches_In_Total ELSE NULL END ASC
这样,非合计行按-punches_In_Total升序(相当于punches_In_Total降序),而合计行的这个表达式为NULL,会被放在最后(在升序中NULL在最后)。但是这样写可能更复杂,而且我们还需要考虑responsible_Dept。
所以还是用第一种方法。
由于原查询较长,我们修改如下(注意:原查询中两个SELECT的列数和类型必须一致,我们在最后加了一个sort_order,所以在UNION的两个查询中都要加上这个常量列):
修改后的完整SQL:
SELECT
responsible_Dept,
punches_In_Total,
at_Punches_In_Total,
bt_Punches_In_Total,
closed_In_Total,
at_Punches_Closed,
bt_Punches_Closed,
punch_At_Closed_Rate,
punch_Bt_Closed_Rate,
closed_Rate,
at_Punches_Remaining,
bt_Punches_Remaining,
remaining_In_Total,
total_Planned_Elimination,
total_Actual_Elimination,
total_Difference,
weekly_Planned_Elimination,
weekly_Actual_Elimination,
weekly_Difference,
daily_Planned_Elimination,
daily_Actual_Elimination,
daily_Difference
FROM (
-- 第一个查询:按部门分组
SELECT
nvl(b.NAME,'待分配') responsible_Dept,
count(1) punches_In_Total,
count(CASE WHEN a.COMMENT_LEVEL= 'A' THEN 1 end) at_Punches_In_Total,
count(CASE WHEN a.COMMENT_LEVEL= 'B' THEN 1 end) bt_Punches_In_Total,
count(CASE WHEN a.FINISH_DATE IS NOT NULL THEN 1 end) closed_In_Total,
count(CASE WHEN a.FINISH_DATE IS NOT NULL AND a.COMMENT_LEVEL= 'A' THEN 1 END) at_Punches_Closed,
count(CASE WHEN a.FINISH_DATE IS NOT NULL AND a.COMMENT_LEVEL= 'B' THEN 1 END ) bt_Punches_Closed,
CASE WHEN count(CASE WHEN a.COMMENT_LEVEL= 'A' THEN 1 end) = 0 THEN 0 ELSE round(count(CASE WHEN a.FINISH_DATE IS NOT null AND a.COMMENT_LEVEL= 'A' THEN 1 END)/count(CASE WHEN a.COMMENT_LEVEL= 'A' THEN 1 end),2) end punch_At_Closed_Rate,
CASE WHEN count(CASE WHEN a.COMMENT_LEVEL= 'B' THEN 1 end) = 0 THEN 0 ELSE round(count(CASE WHEN a.FINISH_DATE IS NOT null AND a.COMMENT_LEVEL= 'B' THEN 1 END)/count(CASE WHEN a.COMMENT_LEVEL= 'B' THEN 1 end),2) end punch_Bt_Closed_Rate,
CASE WHEN count(1) =0 THEN 0 ELSE round(count(CASE WHEN a.FINISH_DATE IS NOT NULL THEN 1 end)/count(1),2) END closed_Rate,
count(CASE WHEN a.FINISH_DATE IS NULL AND a.COMMENT_LEVEL= 'A' THEN 1 END) at_Punches_Remaining,
count(CASE WHEN a.FINISH_DATE IS NULL AND a.COMMENT_LEVEL= 'B' THEN 1 END ) bt_Punches_Remaining,
count(CASE WHEN a.FINISH_DATE IS NULL THEN 1 end) remaining_In_Total,
count(CASE WHEN a.PLAN_DATE IS not NULL AND (to_date(a.PLAN_DATE,'yyyy-mm-dd'))<=to_date('2025-12-05','yyyy-mm-dd') THEN 1 end) total_Planned_Elimination,
count(CASE WHEN a.PLAN_DATE IS not NULL AND (to_date(a.PLAN_DATE,'yyyy-mm-dd'))<=to_date('2025-12-05','yyyy-mm-dd') AND a.FINISH_DATE IS NOT NULL THEN 1 end) total_Actual_Elimination,
count(CASE WHEN a.PLAN_DATE IS not NULL AND (to_date(a.PLAN_DATE,'yyyy-mm-dd'))<=to_date('2025-12-05','yyyy-mm-dd') AND a.FINISH_DATE IS NOT NULL THEN 1 end)-count(CASE WHEN a.PLAN_DATE IS not NULL AND (to_date(a.PLAN_DATE,'yyyy-mm-dd')+1)<to_date('2025-12-05','yyyy-mm-dd') THEN 1 end) total_Difference,
count(CASE WHEN a.PLAN_DATE IS not NULL AND (to_date(a.PLAN_DATE,'yyyy-mm-dd'))<=to_date('2025-12-05','yyyy-mm-dd') AND (to_date(a.PLAN_DATE,'yyyy-mm-dd'))>=(to_date('2025-12-05','yyyy-mm-dd')-7) THEN 1 end) weekly_Planned_Elimination,
count(CASE WHEN a.PLAN_DATE IS not NULL AND (to_date(a.PLAN_DATE,'yyyy-mm-dd'))<=to_date('2025-12-05','yyyy-mm-dd') AND (to_date(a.PLAN_DATE,'yyyy-mm-dd'))>=(to_date('2025-12-05','yyyy-mm-dd')-7) AND a.FINISH_DATE IS NOT NULL THEN 1 end) weekly_Actual_Elimination,
count(CASE WHEN a.PLAN_DATE IS not NULL AND (to_date(a.PLAN_DATE,'yyyy-mm-dd'))<=to_date('2025-12-05','yyyy-mm-dd') AND (to_date(a.PLAN_DATE,'yyyy-mm-dd'))>=(to_date('2025-12-05','yyyy-mm-dd')-7) AND a.FINISH_DATE IS NOT NULL THEN 1 end)-count(CASE WHEN a.PLAN_DATE IS not NULL AND (to_date(a.PLAN_DATE,'yyyy-mm-dd'))<=to_date('2025-12-05','yyyy-mm-dd') AND (to_date(a.PLAN_DATE,'yyyy-mm-dd'))>=(to_date('2025-12-05','yyyy-mm-dd')-7) THEN 1 end) weekly_Difference,
count(CASE WHEN a.PLAN_DATE IS not NULL AND (to_date(a.PLAN_DATE,'yyyy-mm-dd'))=to_date('2025-12-05','yyyy-mm-dd') THEN 1 end) daily_Planned_Elimination,
count(CASE WHEN a.PLAN_DATE IS not NULL AND (to_date(a.PLAN_DATE,'yyyy-mm-dd'))=to_date('2025-12-05','yyyy-mm-dd') AND a.FINISH_DATE IS NOT NULL THEN 1 end) daily_Actual_Elimination,
count(CASE WHEN a.PLAN_DATE IS not NULL AND (to_date(a.PLAN_DATE,'yyyy-mm-dd'))=to_date('2025-12-05','yyyy-mm-dd') AND a.FINISH_DATE IS NOT NULL THEN 1 end)-count(CASE WHEN a.PLAN_DATE IS not NULL AND (to_date(a.PLAN_DATE,'yyyy-mm-dd'))=to_date('2025-12-05','yyyy-mm-dd') THEN 1 end) daily_Difference,
0 as sort_order -- 新增的排序标记,部门行为0
FROM COMMENT_MAINDETAILS a
LEFT JOIN T_ORGANIZATION b ON b.CODE = a.OWNER_DEPT
WHERE MAIN_TYPE_CODE = 'N06' and SUB_TYPE_CODE = 'N06602' AND a.PROJECT_ID = 3202
GROUP BY b.NAME
UNION
-- 第二个查询:合计
SELECT
'合计' responsible_Dept,
count(1) punches_In_Total,
count(CASE WHEN a.COMMENT_LEVEL= 'A' THEN 1 end) at_Punches_In_Total,
count(CASE WHEN a.COMMENT_LEVEL= 'B' THEN 1 end) bt_Punches_In_Total,
count(CASE WHEN a.FINISH_DATE IS NOT NULL THEN 1 end) closed_In_Total,
count(CASE WHEN a.FINISH_DATE IS NOT NULL AND a.COMMENT_LEVEL= 'A' THEN 1 END) at_Punches_Closed,
count(CASE WHEN a.FINISH_DATE IS NOT NULL AND a.COMMENT_LEVEL= 'B' THEN 1 END ) bt_Punches_Closed,
CASE WHEN count(CASE WHEN a.COMMENT_LEVEL= 'A' THEN 1 end) = 0 THEN 0 ELSE round(count(CASE WHEN a.FINISH_DATE IS NOT null AND a.COMMENT_LEVEL= 'A' THEN 1 END)/count(CASE WHEN a.COMMENT_LEVEL= 'A' THEN 1 end),2) end punch_At_Closed_Rate,
CASE WHEN count(CASE WHEN a.COMMENT_LEVEL= 'B' THEN 1 end) = 0 THEN 0 ELSE round(count(CASE WHEN a.FINISH_DATE IS NOT null AND a.COMMENT_LEVEL= 'B' THEN 1 END)/count(CASE WHEN a.COMMENT_LEVEL= 'B' THEN 1 end),2) end punch_Bt_Closed_Rate,
CASE WHEN count(1) =0 THEN 0 ELSE round(count(CASE WHEN a.FINISH_DATE IS NOT NULL THEN 1 end)/count(1),2) END closed_Rate,
count(CASE WHEN a.FINISH_DATE IS NULL AND a.COMMENT_LEVEL= 'A' THEN 1 END) at_Punches_Remaining,
count(CASE WHEN a.FINISH_DATE IS NULL AND a.COMMENT_LEVEL= 'B' THEN 1 END ) bt_Punches_Remaining,
count(CASE WHEN a.FINISH_DATE IS NULL THEN 1 end) remaining_In_Total,
count(CASE WHEN a.PLAN_DATE IS not NULL AND (to_date(a.PLAN_DATE,'yyyy-mm-dd'))<=to_date('2025-12-05','yyyy-mm-dd') THEN 1 end) total_Planned_Elimination,
count(CASE WHEN a.PLAN_DATE IS not NULL AND (to_date(a.PLAN_DATE,'yyyy-mm-dd'))<=to_date('2025-12-05','yyyy-mm-dd') AND a.FINISH_DATE IS NOT NULL THEN 1 end) total_Actual_Elimination,
count(CASE WHEN a.PLAN_DATE IS not NULL AND (to_date(a.PLAN_DATE,'yyyy-mm-dd'))<=to_date('2025-12-05','yyyy-mm-dd') AND a.FINISH_DATE IS NOT NULL THEN 1 end)-count(CASE WHEN a.PLAN_DATE IS not NULL AND (to_date(a.PLAN_DATE,'yyyy-mm-dd')+1)<to_date('2025-12-05','yyyy-mm-dd') THEN 1 end) total_Difference,
count(CASE WHEN a.PLAN_DATE IS not NULL AND (to_date(a.PLAN_DATE,'yyyy-mm-dd'))<=to_date('2025-12-05','yyyy-mm-dd') AND (to_date(a.PLAN_DATE,'yyyy-mm-dd'))>=(to_date('2025-12-05','yyyy-mm-dd')-7) THEN 1 end) weekly_Planned_Elimination,
count(CASE WHEN a.PLAN_DATE IS not NULL AND (to_date(a.PLAN_DATE,'yyyy-mm-dd'))<=to_date('2025-12-05','yyyy-mm-dd') AND (to_date(a.PLAN_DATE,'yyyy-mm-dd'))>=(to_date('2025-12-05','yyyy-mm-dd')-7) AND a.FINISH_DATE IS NOT NULL THEN 1 end) weekly_Actual_Elimination,
count(CASE WHEN a.PLAN_DATE IS not NULL AND (to_date(a.PLAN_DATE,'yyyy-mm-dd'))<=to_date('2025-12-05','yyyy-mm-dd') AND (to_date(a.PLAN_DATE,'yyyy-mm-dd'))>=(to_date('2025-12-05','yyyy-mm-dd')-7) AND a.FINISH_DATE IS NOT NULL THEN 1 end)-count(CASE WHEN a.PLAN_DATE IS not NULL AND (to_date(a.PLAN_DATE,'yyyy-mm-dd'))<=to_date('2025-12-05','yyyy-mm-dd') AND (to_date(a.PLAN_DATE,'yyyy-mm-dd'))>=(to_date('2025-12-05','yyyy-mm-dd')-7) THEN 1 end) weekly_Difference,
count(CASE WHEN a.PLAN_DATE IS not NULL AND (to_date(a.PLAN_DATE,'yyyy-mm-dd'))=to_date('2025-12-05','yyyy-mm-dd') THEN 1 end) daily_Planned_Elimination,
count(CASE WHEN a.PLAN_DATE IS not NULL AND (to_date(a.PLAN_DATE,'yyyy-mm-dd'))=to_date('2025-12-05','yyyy-mm-dd') AND a.FINISH_DATE IS NOT NULL THEN 1 end) daily_Actual_Elimination,
count(CASE WHEN a.PLAN_DATE IS not NULL AND (to_date(a.PLAN_DATE,'yyyy-mm-dd'))=to_date('2025-12-05','yyyy-mm-dd') AND a.FINISH_DATE IS NOT NULL THEN 1 end)-count(CASE WHEN a.PLAN_DATE IS not NULL AND (to_date(a.PLAN_DATE,'yyyy-mm-dd'))=to_date('2025-12-05','yyyy-mm-dd') THEN 1 end) daily_Difference,
1 as sort_order -- 新增的排序标记,合计行为1
FROM COMMENT_MAINDETAILS a
LEFT JOIN T_ORGANIZATION b ON b.CODE = a.OWNER_DEPT
WHERE MAIN_TYPE_CODE = 'N06' and SUB_TYPE_CODE = 'N06602' AND a.PROJECT_ID = 3202
) t
ORDER BY sort_order ASC, -- 先按sort_order升序,这样0(部门行)在前,1(合计行)在后
CASE WHEN sort_order=0 THEN punches_In_Total END DESC, -- 对于sort_order=0的行,按punches_In_Total降序
responsible_Dept -- 然后按responsible_Dept(在sort_order=0的行中,如果punches_In_Total相同,则按responsible_Dept排序,默认升序)
注意:在合计行中,CASE WHEN sort_order=0 THEN punches_In_Total 会返回NULL,所以合计行在第二排序条件上为NULL,而NULL在降序排序中会被放在最后,但因为sort_order已经为1(排在后面)所以没关系。
这样,我们就得到了先按部门行(非合计行)的punches_In_Total降序排列,然后合计行在最后。
但是,我们也可以不在外层选择sort_order列,因为我们不需要显示它。所以可以在最后选择列时去掉sort_order。
修改外层SELECT,去掉sort_order列:
SELECT
responsible_Dept,
punches_In_Total,
... -- 其他列(除了sort_order)
FROM (...)
ORDER BY ...
所以最终代码中,外层查询不选择sort_order。
完整代码(去掉排序字段)如下:
</think>
要解决你的需求——让总计行(responsible_Dept='合计')显示在最后一行,其他行按 `punches_In_Total` 降序排列——可以通过以下优化实现:
### 优化后的 SQL 代码
```sql
SELECT
responsible_Dept,
punches_In_Total,
at_Punches_In_Total,
bt_Punches_In_Total,
closed_In_Total,
at_Punches_Closed,
bt_Punches_Closed,
punch_At_Closed_Rate,
punch_Bt_Closed_Rate,
closed_Rate,
at_Punches_Remaining,
bt_Punches_Remaining,
remaining_In_Total,
total_Planned_Elimination,
total_Actual_Elimination,
total_Difference,
weekly_Planned_Elimination,
weekly_Actual_Elimination,
weekly_Difference,
daily_Planned_Elimination,
daily_Actual_Elimination,
daily_Difference
FROM (
SELECT
nvl(b.NAME, '待分配') AS responsible_Dept,
count(1) punches_In_Total,
count(CASE WHEN a.COMMENT_LEVEL = 'A' THEN 1 END) at_Punches_In_Total,
count(CASE WHEN a.COMMENT_LEVEL = 'B' THEN 1 END) bt_Punches_In_Total,
count(CASE WHEN a.FINISH_DATE IS NOT NULL THEN 1 END) closed_In_Total,
count(CASE WHEN a.FINISH_DATE IS NOT NULL AND a.COMMENT_LEVEL = 'A' THEN 1 END) at_Punches_Closed,
count(CASE WHEN a.FINISH_DATE IS NOT NULL AND a.COMMENT_LEVEL = 'B' THEN 1 END) bt_Punches_Closed,
CASE
WHEN count(CASE WHEN a.COMMENT_LEVEL = 'A' THEN 1 END) = 0 THEN 0
ELSE round(count(CASE WHEN a.FINISH_DATE IS NOT NULL AND a.COMMENT_LEVEL = 'A' THEN 1 END) / count(CASE WHEN a.COMMENT_LEVEL = 'A' THEN 1 END), 2)
END punch_At_Closed_Rate,
CASE
WHEN count(CASE WHEN a.COMMENT_LEVEL = 'B' THEN 1 END) = 0 THEN 0
ELSE round(count(CASE WHEN a.FINISH_DATE IS NOT NULL AND a.COMMENT_LEVEL = 'B' THEN 1 END) / count(CASE WHEN a.COMMENT_LEVEL = 'B' THEN 1 END), 2)
END punch_Bt_Closed_Rate,
CASE
WHEN count(1) = 0 THEN 0
ELSE round(count(CASE WHEN a.FINISH_DATE IS NOT NULL THEN 1 END) / count(1), 2)
END closed_Rate,
count(CASE WHEN a.FINISH_DATE IS NULL AND a.COMMENT_LEVEL = 'A' THEN 1 END) at_Punches_Remaining,
count(CASE WHEN a.FINISH_DATE IS NULL AND a.COMMENT_LEVEL = 'B' THEN 1 END) bt_Punches_Remaining,
count(CASE WHEN a.FINISH_DATE IS NULL THEN 1 END) remaining_In_Total,
count(CASE WHEN a.PLAN_DATE IS NOT NULL AND to_date(a.PLAN_DATE, 'yyyy-mm-dd') <= to_date('2025-12-05', 'yyyy-mm-dd') THEN 1 END) total_Planned_Elimination,
count(CASE WHEN a.PLAN_DATE IS NOT NULL AND to_date(a.PLAN_DATE, 'yyyy-mm-dd') <= to_date('2025-12-05', 'yyyy-mm-dd') AND a.FINISH_DATE IS NOT NULL THEN 1 END) total_Actual_Elimination,
count(CASE WHEN a.PLAN_DATE IS NOT NULL AND to_date(a.PLAN_DATE, 'yyyy-mm-dd') <= to_date('2025-12-05', 'yyyy-mm-dd') AND a.FINISH_DATE IS NOT NULL THEN 1 END) -
count(CASE WHEN a.PLAN_DATE IS NOT NULL AND to_date(a.PLAN_DATE, 'yyyy-mm-dd') + 1 < to_date('2025-12-05', 'yyyy-mm-dd') THEN 1 END) total_Difference,
count(CASE WHEN a.PLAN_DATE IS NOT NULL AND to_date(a.PLAN_DATE, 'yyyy-mm-dd') <= to_date('2025-12-05', 'yyyy-mm-dd') AND to_date(a.PLAN_DATE, 'yyyy-mm-dd') >= to_date('2025-12-05', 'yyyy-mm-dd') - 7 THEN 1 END) weekly_Planned_Elimination,
count(CASE WHEN a.PLAN_DATE IS NOT NULL AND to_date(a.PLAN_DATE, 'yyyy-mm-dd') <= to_date('2025-12-05', 'yyyy-mm-dd') AND to_date(a.PLAN_DATE, 'yyyy-mm-dd') >= to_date('2025-12-05', 'yyyy-mm-dd') - 7 AND a.FINISH_DATE IS NOT NULL THEN 1 END) weekly_Actual_Elimination,
count(CASE WHEN a.PLAN_DATE IS NOT NULL AND to_date(a.PLAN_DATE, 'yyyy-mm-dd') <= to_date('2025-12-05', 'yyyy-mm-dd') AND to_date(a.PLAN_DATE, 'yyyy-mm-dd') >= to_date('2025-12-05', 'yyyy-mm-dd') - 7 AND a.FINISH_DATE IS NOT NULL THEN 1 END) -
count(CASE WHEN a.PLAN_DATE IS NOT NULL AND to_date(a.PLAN_DATE, 'yyyy-mm-dd') <= to_date('2025-12-05', 'yyyy-mm-dd') AND to_date(a.PLAN_DATE, 'yyyy-mm-dd') >= to_date('2025-12-05', 'yyyy-mm-dd') - 7 THEN 1 END) weekly_Difference,
count(CASE WHEN a.PLAN_DATE IS NOT NULL AND to_date(a.PLAN_DATE, 'yyyy-mm-dd') = to_date('2025-12-05', 'yyyy-mm-dd') THEN 1 END) daily_Planned_Elimination,
count(CASE WHEN a.PLAN_DATE IS NOT NULL AND to_date(a.PLAN_DATE, 'yyyy-mm-dd') = to_date('2025-12-05', 'yyyy-mm-dd') AND a.FINISH_DATE IS NOT NULL THEN 1 END) daily_Actual_Elimination,
count(CASE WHEN a.PLAN_DATE IS NOT NULL AND to_date(a.PLAN_DATE, 'yyyy-mm-dd') = to_date('2025-12-05', 'yyyy-mm-dd') AND a.FINISH_DATE IS NOT NULL THEN 1 END) -
count(CASE WHEN a.PLAN_DATE IS NOT NULL AND to_date(a.PLAN_DATE, 'yyyy-mm-dd') = to_date('2025-12-05', 'yyyy-mm-dd') THEN 1 END) daily_Difference,
0 AS is_total -- 标记非合计行
FROM COMMENT_MAINDETAILS a
LEFT JOIN T_ORGANIZATION b ON b.CODE = a.OWNER_DEPT
WHERE MAIN_TYPE_CODE = 'N06' AND SUB_TYPE_CODE = 'N06602' AND a.PROJECT_ID = 3202
GROUP BY b.NAME
UNION ALL
SELECT
'合计' responsible_Dept,
count(1) punches_In_Total,
count(CASE WHEN a.COMMENT_LEVEL = 'A' THEN 1 END) at_Punches_In_Total,
count(CASE WHEN a.COMMENT_LEVEL = 'B' THEN 1 END) bt_Punches_In_Total,
count(CASE WHEN a.FINISH_DATE IS NOT NULL THEN 1 END) closed_In_Total,
count(CASE WHEN a.FINISH_DATE IS NOT NULL AND a.COMMENT_LEVEL = 'A' THEN 1 END) at_Punches_Closed,
count(CASE WHEN a.FINISH_DATE IS NOT NULL AND a.COMMENT_LEVEL = 'B' THEN 1 END) bt_Punches_Closed,
CASE
WHEN count(CASE WHEN a.COMMENT_LEVEL = 'A' THEN 1 END) = 0 THEN 0
ELSE round(count(CASE WHEN a.FINISH_DATE IS NOT NULL AND a.COMMENT_LEVEL = 'A' THEN 1 END) / count(CASE WHEN a.COMMENT_LEVEL = 'A' THEN 1 END), 2)
END punch_At_Closed_Rate,
CASE
WHEN count(CASE WHEN a.COMMENT_LEVEL = 'B' THEN 1 END) = 0 THEN 0
ELSE round(count(CASE WHEN a.FINISH_DATE IS NOT NULL AND a.COMMENT_LEVEL = 'B' THEN 1 END) / count(CASE WHEN a.COMMENT_LEVEL = 'B' THEN 1 END), 2)
END punch_Bt_Closed_Rate,
CASE
WHEN count(1) = 0 THEN 0
ELSE round(count(CASE WHEN a.FINISH_DATE IS NOT NULL THEN 1 END) / count(1), 2)
END closed_Rate,
count(CASE WHEN a.FINISH_DATE IS NULL AND a.COMMENT_LEVEL = 'A' THEN 1 END) at_Punches_Remaining,
count(CASE WHEN a.FINISH_DATE IS NULL AND a.COMMENT_LEVEL = 'B' THEN 1 END) bt_Punches_Remaining,
count(CASE WHEN a.FINISH_DATE IS NULL THEN 1 END) remaining_In_Total,
count(CASE WHEN a.PLAN_DATE IS NOT NULL AND to_date(a.PLAN_DATE, 'yyyy-mm-dd') <= to_date('2025-12-05', 'yyyy-mm-dd') THEN 1 END) total_Planned_Elimination,
count(CASE WHEN a.PLAN_DATE IS NOT NULL AND to_date(a.PLAN_DATE, 'yyyy-mm-dd') <= to_date('2025-12-05', 'yyyy-mm-dd') AND a.FINISH_DATE IS NOT NULL THEN 1 END) total_Actual_Elimination,
count(CASE WHEN a.PLAN_DATE IS NOT NULL AND to_date(a.PLAN_DATE, 'yyyy-mm-dd') <= to_date('2025-12-05', 'yyyy-mm-dd') AND a.FINISH_DATE IS NOT NULL THEN 1 END) -
count(CASE WHEN a.PLAN_DATE IS NOT NULL AND to_date(a.PLAN_DATE, 'yyyy-mm-dd') + 1 < to_date('2025-12-05', 'yyyy-mm-dd') THEN 1 END) total_Difference,
count(CASE WHEN a.PLAN_DATE IS NOT NULL AND to_date(a.PLAN_DATE, 'yyyy-mm-dd') <= to_date('2025-12-05', 'yyyy-mm-dd') AND to_date(a.PLAN_DATE, 'yyyy-mm-dd') >= to_date('2025-12-05', 'yyyy-mm-dd') - 7 THEN 1 END) weekly_Planned_Elimination,
count(CASE WHEN a.PLAN_DATE IS NOT NULL AND to_date(a.PLAN_DATE, 'yyyy-mm-dd') <= to_date('2025-12-05', 'yyyy-mm-dd') AND to_date(a.PLAN_DATE, 'yyyy-mm-dd') >= to_date('2025-12-05', 'yyyy-mm-dd') - 7 AND a.FINISH_DATE IS NOT NULL THEN 1 END) weekly_Actual_Elimination,
count(CASE WHEN a.PLAN_DATE IS NOT NULL AND to_date(a.PLAN_DATE, 'yyyy-mm-dd') <= to_date('2025-12-05', 'yyyy-mm-dd') AND to_date(a.PLAN_DATE, 'yyyy-mm-dd') >= to_date('2025-12-05', 'yyyy-mm-dd') - 7 AND a.FINISH_DATE IS NOT NULL THEN 1 END) -
count(CASE WHEN a.PLAN_DATE IS NOT NULL AND to_date(a.PLAN_DATE, 'yyyy-mm-dd') <= to_date('2025-12-05', 'yyyy-mm-dd') AND to_date(a.PLAN_DATE, 'yyyy-mm-dd') >= to_date('2025-12-05', 'yyyy-mm-dd') - 7 THEN 1 END) weekly_Difference,
count(CASE WHEN a.PLAN_DATE IS NOT NULL AND to_date(a.PLAN_DATE, 'yyyy-mm-dd') = to_date('2025-12-05', 'yyyy-mm-dd') THEN 1 END) daily_Planned_Elimination,
count(CASE WHEN a.PLAN_DATE IS NOT NULL AND to_date(a.PLAN_DATE, 'yyyy-mm-dd') = to_date('2025-12-05', 'yyyy-mm-dd') AND a.FINISH_DATE IS NOT NULL THEN 1 END) daily_Actual_Elimination,
count(CASE WHEN a.PLAN_DATE IS NOT NULL AND to_date(a.PLAN_DATE, 'yyyy-mm-dd') = to_date('2025-12-05', 'yyyy-mm-dd') AND a.FINISH_DATE IS NOT NULL THEN 1 END) -
count(CASE WHEN a.PLAN_DATE IS NOT NULL AND to_date(a.PLAN_DATE, 'yyyy-mm-dd') = to_date('2025-12-05', 'yyyy-mm-dd') THEN 1 END) daily_Difference,
1 AS is_total -- 标记合计行
FROM COMMENT_MAINDETAILS a
LEFT JOIN T_ORGANIZATION b ON b.CODE = a.OWNER_DEPT
WHERE MAIN_TYPE_CODE = 'N06' AND SUB_TYPE_CODE = 'N06602' AND a.PROJECT_ID = 3202
) t
ORDER BY
is_total ASC, -- 先按标记排序(0在前,1在后)
CASE WHEN is_total = 0 THEN punches_In_Total END DESC; -- 非合计行按数量降序
```
### 关键优化解释:
1. **添加排序标记列 `is_total`**:
- 非合计行标记为 `0`
- 合计行标记为 `1`
2. **优化排序逻辑**:
- 第一优先级:按 `is_total` 升序,确保合计行(`is_total=1`)永远在最后
- 第二优先级:对非合计行(`is_total=0`)按 `punches_In_Total` 降序排列
- 使用 `CASE WHEN` 条件确保只对非合计行应用降序排序
3. **性能提升**:
- 用 `UNION ALL` 替代 `UNION`:避免去重操作(原数据无重复),提升查询效率
- 所有统计逻辑保持原样,仅通过排序标记控制输出顺序
### 示例输出效果:
| responsible_Dept | punches_In_Total | ... |
|------------------|------------------|-----|
| 技术部 | 150 | ... | // 按数量降序排列
| 运维部 | 120 | ... |
| 待分配 | 80 | ... |
| **合计** | **350** | ... | // 固定最后一行
---