SQL视图查询运行很快，但是模板数据集加载不出，

代码如下：

with A as(select '工种' as 工种,
case when INSTR(工种,'、')=0 then 工种
else SUBSTR(工种,0,INSTR(工种,'、')-1) end as 种类,1 as 数量 from 班组资源库)
,B as(
select 工种,
sum(数量) as 木工
,0 as 铝模
,0 as 钢筋工
,0 as 混凝土
,0 as 架子工
,0 as 砌筑
,0 as 抹灰
,0 as 瓦工
,0 as 机电
,0 as 大白
,0 as 粉刷石膏
,0 as 其他
from A where 种类='木工'
GROUP BY 工种
union all
select 工种,
0 as 木工
,sum(数量) as 铝模
,0 as 钢筋工
,0 as 混凝土
,0 as 架子工
,0 as 砌筑
,0 as 抹灰
,0 as 瓦工
,0 as 机电
,0 as 大白
,0 as 粉刷石膏
,0 as 其他
from A where 种类='铝模'
GROUP BY 工种
union all
select 工种,
0 as 木工
,0 as 铝模
,sum(数量) as 钢筋工
,0 as 混凝土
,0 as 架子工
,0 as 砌筑
,0 as 抹灰
,0 as 瓦工
,0 as 机电
,0 as 大白
,0 as 粉刷石膏
,0 as 其他
from A where 种类='钢筋工'
GROUP BY 工种
union all
select 工种,
0 as 木工
,0 as 铝模
,0 as 钢筋工
,sum(数量) as 混凝土
,0 as 架子工
,0 as 砌筑
,0 as 抹灰
,0 as 瓦工
,0 as 机电
,0 as 大白
,0 as 粉刷石膏
,0 as 其他
from A where 种类='混凝土'
GROUP BY 工种
union all
select 工种,
0 as 木工
,0 as 铝模
,0 as 钢筋工
,0 as 混凝土
,sum(数量) as 架子工
,0 as 砌筑
,0 as 抹灰
,0 as 瓦工
,0 as 机电
,0 as 大白
,0 as 粉刷石膏
,0 as 其他
from A where 种类='架子工'
GROUP BY 工种
union all
select 工种,
0 as 木工
,0 as 铝模
,0 as 钢筋工
,0 as 混凝土
,0 as 架子工
,sum(数量) as 砌筑
,0 as 抹灰
,0 as 瓦工
,0 as 机电
,0 as 大白
,0 as 粉刷石膏
,0 as 其他
from A where 种类='砌筑'
GROUP BY 工种
union all
select 工种,
0 as 木工
,0 as 铝模
,0 as 钢筋工
,0 as 混凝土
,0 as 架子工
,0 as 砌筑
,sum(数量) as 抹灰
,0 as 瓦工
,0 as 机电
,0 as 大白
,0 as 粉刷石膏
,0 as 其他
from A where 种类='抹灰'
GROUP BY 工种
union all
select 工种,
0 as 木工
,0 as 铝模
,0 as 钢筋工
,0 as 混凝土
,0 as 架子工
,0 as 砌筑
,0 as 抹灰
,sum(数量) as 瓦工
,0 as 机电
,0 as 大白
,0 as 粉刷石膏
,0 as 其他
from A where 种类='瓦工'
GROUP BY 工种
union all
select 工种,
0 as 木工
,0 as 铝模
,0 as 钢筋工
,0 as 混凝土
,0 as 架子工
,0 as 砌筑
,0 as 抹灰
,0 as 瓦工
,sum(数量) as 机电
,0 as 大白
,0 as 粉刷石膏
,0 as 其他
from A where 种类='机电'
GROUP BY 工种
union all
select 工种,
0 as 木工
,0 as 铝模
,0 as 钢筋工
,0 as 混凝土
,0 as 架子工
,0 as 砌筑
,0 as 抹灰
,0 as 瓦工
,0 as 机电
,sum(数量) as 大白
,0 as 粉刷石膏
,0 as 其他
from A where 种类='大白'
GROUP BY 工种
union all
select 工种,
0 as 木工
,0 as 铝模
,0 as 钢筋工
,0 as 混凝土
,0 as 架子工
,0 as 砌筑
,0 as 抹灰
,0 as 瓦工
,0 as 机电
,0 as 大白
,sum(数量) as 粉刷石膏
,0 as 其他
from A where 种类='粉刷石膏'
GROUP BY 工种
union all
select 工种,
0 as 木工
,0 as 铝模
,0 as 钢筋工
,0 as 混凝土
,0 as 架子工
,0 as 砌筑
,0 as 抹灰
,0 as 瓦工
,0 as 机电
,0 as 大白
,0 as 粉刷石膏
,sum(数量) as 其他
from A
GROUP BY 工种
)
select "工种",sum(木工) as 木工,sum(铝模) as 铝模,sum(钢筋工) as 钢筋工
,sum(混凝土) as 混凝土,sum(架子工) as 架子工,sum(砌筑) as 砌筑,sum(抹灰) as 抹灰
,sum(瓦工) as 瓦工,sum(机电) as 机电,sum(大白) as 大白,sum(粉刷石膏) as 粉刷石膏,sum(其他)-(sum(木工)+sum(铝模)+sum(钢筋工)+sum(混凝土)+sum(架子工)+sum(砌筑)+sum(抹灰)+sum(瓦工)+sum(机电)+sum(大白)+sum(粉刷石膏)) as 其他
from B group by 工种

FineReport 宋乐 发布于 2020-3-9 16:54

1min目标场景问卷

立即参与

回答问题关注问题

悬赏：4 F币 + 添加悬赏

提示：增加悬赏、完善问题、追问等操作，可使您的问题被置顶，并向所有关注者发送通知

共1回答

最佳回答

zsh331Lv8专家互助
发布于2020-3-9 19:01（编辑于 2020-3-9 19:02）

明明15行能解决的sql，硬生生被你写了200行…小伙子，要变通呀！

select 
'工种' as 工种
,sum(case when 种类 ='木工' then 1 else 0 end) as sum_木工
,sum(case when 种类 ='铝模' then 1 else 0 end) as sum_铝模
,sum(case when 种类 ='钢筋工' then 1 else 0 end) as sum_钢筋工
,sum(case when 种类 ='混凝土' then 1 else 0 end) as sum_混凝土
,sum(case when 种类 ='架子工' then 1 else 0 end) as sum_架子工
,sum(case when 种类 ='砌筑' then 1 else 0 end) as sum_砌筑
,sum(case when 种类 ='抹灰' then 1 else 0 end) as sum_抹灰
,sum(case when 种类 ='瓦工' then 1 else 0 end) as sum_瓦工
,sum(case when 种类 ='机电' then 1 else 0 end) as sum_机电
,sum(case when 种类 ='大白' then 1 else 0 end) as sum_大白
,sum(case when 种类 ='粉刷石膏' then 1 else 0 end) as sum_粉刷石膏
,sum(case when 种类  not in ('木工','铝模','钢筋工','混凝土','架子工','砌筑','抹灰','瓦工','机电','大白','粉刷石膏') then 1 else 0 end) as sum_其他
from 
(
SELECT CASE
           WHEN INSTR(工种,'、')=0 THEN 工种
           ELSE SUBSTR(工种,0,INSTR(工种,'、')-1)
       END AS 种类,
FROM 班组资源库
) a