这条sql如何书写 在线急用
0
悬赏园豆：50
[已解决问题]
解决于 2017-08-01 10:21
表如下
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也是一样
最佳答案
0
正好我也在找类似的，试了一下前面的方法，可能是我数据比较多有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
收获园豆：20
month in (1,2,3,4,5,6) 我觉得这个可以改成 month between 1 and 6 ,这样比较快点
其他回答(3)
0
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关键字
收获园豆：10
0
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，可能会和数据库自带的有冲突，所以最好改下
收获园豆：10
0
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
收获园豆：10