sql 有优化或简写吗

[MrYangkang]

--计算本年的个数量,金额
select a.wlcode,a.sumqty1-b.sumqty2
（
select wlcode,oyear
,sum(qty1)sumqty1,sum(qty2)sumqty2
,sum(money1)summoney1,sum(money2) as summoney2 from table1
where oyear ='2013'
group by wlcode,oyear
）a inner join
(
--因为一个条件不同，我就的写两次
select wlcode,oyear
,sum(qty1)sumqty1,sum(qty2)sumqty2
,sum(money1)summoney1,sum(money2) as summoney2 from table1
where oyear ='2012'
group by wlcode,oyear
)b a.wlcode = b.wlcode

[發糞塗牆]

2005以上可以先用CTE把相同的数据存一起

[發糞塗牆]

这个看看结果有没有问题

SELECT wlcode,SUM(CASE WHEN oyear='2013' THEN sumqty1 ELSE 0 END )-SUM(CASE WHEN oyear='2012' THEN sumqty1 ELSE 0 END )
FROM (
SELECT  wlcode ,
        oyear ,
        SUM(qty1) sumqty1 ,
        SUM(qty2) sumqty2 ,
        SUM(money1) summoney1 ,
        SUM(money2) AS summoney2
FROM    table1
WHERE   oyear IN ( '2013', '2012' )
GROUP BY wlcode ,
        oyear)a
GROUP BY wlcode
        

[發糞塗牆]

貌似可以用apply，不过我不熟

[阳泉酒家小当家]

其实你这种写法，看上去好像写的挺多的，但是性能上应该没什么问题的。 要优化，可以考虑在oyear,wlcode 字段上建立索引

[阳泉酒家小当家]

也可以做简化。 select a.wlcode,a.sumqty1-b.sumqty2 可以这样：

SELECT  wlcode ,
        SUM(CASE WHEN oyear='2013' THEN qty1 ELSE 0 END ) - SUM(CASE WHEN oyear='2012' THEN qty2 ELSE 0 END )
FROM    table1
WHERE   oyear IN ( '2013', '2012' )
GROUP BY wlcode

[MrYangkang]

，先这样处理呗！！！

[發糞塗牆]

首先确保数据正确，性能问题晚点再处理，可以贴执行计划