首页 新闻 搜索 专区 学院

这条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也是一样

长跑的主页 长跑 | 初学一级 | 园豆:11
提问于:2017-07-28 09:32
< >
分享
最佳答案
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
苍枫露雨 | 小虾三级 |园豆:1027 | 2017-07-28 11:32

month in (1,2,3,4,5,6) 我觉得这个可以改成 month between 1 and 6 ,这样比较快点

金琥 | 园豆:2560 (老鸟四级) | 2017-07-28 11:42
其他回答(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
没什么比你更优秀 | 园豆:130 (初学一级) | 2017-07-28 10:00
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
金琥 | 园豆:2560 (老鸟四级) | 2017-07-28 11:29
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
学弱 | 园豆:54 (初学一级) | 2017-07-31 12:43
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册