表如下
table ass(
id balance year month
112101 30 2016 9
112102 50 2017 3
112101 10 2017 2
112101 20 2016 8
112102 43 2017 6
)
查询后显示为
id 2017/6/30 2016/12/31
112101 sum(banlance) sum(banlance)
112102 sum(banlance) sum(banlance)
要如何书写sql语句呢?
sum(banlance)的意思为:2017年一月到6月的 balance 值得和 2016/12/31也是一样
正好我也在找类似的,试了一下前面的方法,可能是我数据比较多有40多万条,超时了, 你看看这个能满足你的要求不,
select a.id, t1.sum as '2017/6/30' , t2.sum as '2016/12/31' from tableName a left join (select id, sum(balance) sum from tableName where year = 2017 and month in (1,2,3,4,5,6) group by id) t1 on t1.id=a.id left join (select id, sum(balance) sum from tableName where year = 2016 and month in (7,8,9,10,11,12) group by id) t2 on t2.id=a.id
month in (1,2,3,4,5,6) 我觉得这个可以改成 month between 1 and 6 ,这样比较快点
select a.id ,
(select sum(balance) from 表名 where year = 2017 and month in (1,2,3,4,5,6) and id = a.id)as '2017/6/30',
(select sum(balance) from 表名 where year = 2016 and month in (7,8,9,10,11,12) and id = a.id)as '2016/12/31'
from 表名 a GROUP BY id
最好是把字段名year ,month ,加个s,他们是sql关键字
select a.id , (select sum(balance) from 表名 where year = 2017 and (month between 1 and 6) and id = a.id)as '2017/6/30', (select sum(balance) from 表名 where year = 2016 and (month between 7 and 12) and id = a.id)as '2016/12/31' from 表名 a GROUP BY id
对于year和month,可能会和数据库自带的有冲突,所以最好改下
SELECT ID, sum(case when year=2017 and month in(1,2,3,4,5,6) then balance else 0 end) x, sum(case when year=2016 and month in(7,8,9,10,11,12) then balance else 0 end) y FROM tableName group by ID