请前辈帮忙看看我这个视图能不能优化一下？

[想到才能做到]

目前视图如下：

 
CREATE VIEW vs_KHBMYSYF
as 
SELECT KHBM,SUM(je) AS ysye FROM (
SELECT KHBM,(JE-YHJE-SFJE)* -1 AS je FROM dbo.YWDJCX WHERE (draft=0) AND (HCBZ <> 1) AND (DJLX IN (1, 5, 8, 16, 24, 27, 34, 35, 37)) UNION ALL
SELECT KHBM,JE-YHJE-SFJE AS je FROM dbo.YWDJCX WHERE (draft=0) AND (HCBZ <> 1) AND (DJLX IN (2, 4, 7, 25, 26, 36, 38)) UNION ALL 
SELECT KHBM,JE+YHJE AS je FROM dbo.YWDJCX WHERE (draft=0) AND (HCBZ <> 1) AND (DJLX = 3) UNION ALL
SELECT KHBM,(JE+YHJE)*-1 AS je FROM dbo.YWDJCX WHERE (draft=0) AND (HCBZ <> 1) AND (DJLX = 6) UNION ALL
SELECT KHBM,QCYS-QCYF AS je FROM dbo.WLKHBM
) AS t
GROUP BY KHBM
GO
 

表dbo.YWDJCX 数据有三十万多万条，用以上视图查询所用时间十几秒，麻烦哪位帮忙看看我应该怎么优化它？ 有一种索引视图，不知是否适合我？

[kingtiy]

视图其实还是基于表的查询. 关键在于查询的条件,在基表上面是不是相应的优化,如索引.统计信息等.

[Ginnnnnnnn]

用case when 改动一下试试

SELECT KHBM,SUM(je) AS ysye FROM (
SELECT KHBM,CASE WHEN DJLX IN (1, 5, 8, 16, 24, 27, 34, 35, 37) THEN (JE-YHJE-SFJE) *1
				 WHEN DJLX IN (2, 4, 7, 25, 26, 36, 38) THEN JE-YHJE-SFJE
				 WHEN DJLX = 3 THEN JE+YHJE
				 WHEN DJLX = 6 THEN (JE+YHJE)*-1 ELSE 0 END AS je
	FROM dbo.YWDJCX WHERE draft=0 AND HCBZ <> 1
UNION ALL
SELECT KHBM,QCYS-QCYF AS je FROM dbo.WLKHBM) t
GROUP BY KHBM

[想到才能做到]

引用 2 楼 KanzakiOrange 的回复:

用case when 改动一下试试

SELECT KHBM,SUM(je) AS ysye FROM (
SELECT KHBM,CASE WHEN DJLX IN (1, 5, 8, 16, 24, 27, 34, 35, 37) THEN (JE-YHJE-SFJE) *1
				 WHEN DJLX IN (2, 4, 7, 25, 26, 36, 38) THEN JE-YHJE-SFJE
				 WHEN DJLX = 3 THEN JE+YHJE
				 WHEN DJLX = 6 THEN (JE+YHJE)*-1 ELSE 0 END AS je
	FROM dbo.YWDJCX WHERE draft=0 AND HCBZ <> 1
UNION ALL
SELECT KHBM,QCYS-QCYF AS je FROM dbo.WLKHBM) t
GROUP BY KHBM

谢谢你的热心回答，用case when 方式查询所用时间和UNION ALL差不多。请问还有没有别的方法？

[Ginnnnnnnn]

如果你的查询是这样的话，表上面建上对应的索引，也是可以提高查询的效率。