declare @table table(year int,month int,num1 int,lx int) insert @table select 2009,3,-20,0 union all select 2009,3,-30,0 union all select 2010,3,20,1 union all select 2010,4,20,1 union all select 2010,5,100,1 union all select 2010,5,30,1 union all select 2010,5,-20,0 SELECT year,month,num1=sum(num1),lx FROM @table GROUP BY year,month,lx ORDER BY year,month/* year month num1 lx ----------- ----------- ----------- ----------- 2009 3 -50 0 2010 3 20 1 2010 4 20 1 2010 5 -20 0 2010 5 130 1(5 行受影响) */
select t1.*, t2.num2 from ( select [year], [month], sum(num1) as num1 group by [year], [month], lx having lx = 1 ) t1 inner join ( select [year], [month], sum(num1) as num2 group by [year], [month], lx having lx = 0 ) t2 on t1.[year] = t2.[year] and t1.[month] = t2.[month]
declare @table table(year int,month int,num1 int,lx int) insert @table select 2009,3,-20,0 union all select 2009,3,-30,0 union all select 2010,3,20,1 union all select 2010,4,20,1 union all select 2010,5,100,1 union all select 2010,5,30,1 union all select 2010,5,-20,0 SELECT year,month,num1=sum(case when lx=1 then num1 else 0 end),num2=sum(case when lx=0 then num1 else 0 end) FROM @table GROUP BY year,month ORDER BY year,month/* year month num1 num2 ----------- ----------- ----------- ----------- 2009 3 0 -50 2010 3 20 0 2010 4 20 0 2010 5 130 -20(4 行受影响) */
select year, month, (select sum(num1) from tablename where year=t.year and month=t.month and lx=1) as num1, (select sum(num1) from tablename where year=t.year and month=t.month and lx=0) as num1 from tablename as t group by year,month order by year,month
错了,应该是: select t3.[year], t3.[month], isnull(t1.num1, 0), isnull(t2.num2, 0) from ( select [year], [month] from t group by [year], [month] ) t3 left join ( select [year], [month], sum(num1) as num1 from t group by [year], [month], lx having lx = 1 ) t1 on t3.[year] = t1.[year] and t3.[month] = t1.[month] left join ( select [year], [month], sum(num1) as num2 from t group by [year], [month], lx having lx = 0 ) t2 on t3.[year] = t2.[year] and t3.[month] = t2.[month]
如何通过一条sql语句得到这样的数据表呢(就是查出按year,month来排序,并分别求lx=1和lx=0情况下的对num1列求和)
year month num1 num2
2009 3 0 -50
2010 3 20 0
2010 4 20 0
2010 5 130 -20
declare @table table(year int,month int,num1 int,lx int)
insert @table
select 2009,3,-20,0 union all
select 2009,3,-30,0 union all
select 2010,3,20,1 union all
select 2010,4,20,1 union all
select 2010,5,100,1 union all
select 2010,5,30,1 union all
select 2010,5,-20,0
SELECT year,month,num1=sum(num1),lx
FROM @table
GROUP BY year,month,lx
ORDER BY year,month/*
year month num1 lx
----------- ----------- ----------- -----------
2009 3 -50 0
2010 3 20 1
2010 4 20 1
2010 5 -20 0
2010 5 130 1(5 行受影响)
*/
select t1.*, t2.num2 from
(
select [year], [month], sum(num1) as num1 group by [year], [month], lx having lx = 1
) t1
inner join
(
select [year], [month], sum(num1) as num2 group by [year], [month], lx having lx = 0
) t2
on t1.[year] = t2.[year] and t1.[month] = t2.[month]
insert @table
select 2009,3,-20,0 union all
select 2009,3,-30,0 union all
select 2010,3,20,1 union all
select 2010,4,20,1 union all
select 2010,5,100,1 union all
select 2010,5,30,1 union all
select 2010,5,-20,0
SELECT year,month,num1=sum(case when lx=1 then num1 else 0 end),num2=sum(case when lx=0 then num1 else 0 end)
FROM @table
GROUP BY year,month
ORDER BY year,month/*
year month num1 num2
----------- ----------- ----------- -----------
2009 3 0 -50
2010 3 20 0
2010 4 20 0
2010 5 130 -20(4 行受影响)
*/
from tablename as t group by year,month order by year,month
select t3.[year], t3.[month], isnull(t1.num1, 0), isnull(t2.num2, 0) from
(
select [year], [month] from t group by [year], [month]
)
t3 left join
(
select [year], [month], sum(num1) as num1 from t group by [year], [month], lx having lx = 1
) t1
on t3.[year] = t1.[year] and t3.[month] = t1.[month]
left join
(
select [year], [month], sum(num1) as num2 from t group by [year], [month], lx having lx = 0
) t2
on t3.[year] = t2.[year] and t3.[month] = t2.[month]