SQL中一个表中几条数据怎么合成一条！？

[wolftomb]

比如 0表示old 1表示new
NO count oldnew
001 1 0
001 2 0
001 2 1
002 1 1
002 2 1
002 2 0

我想要的结果是（首先要以NO分组，然后又要把oldnew分离）
No count oldcount newcount
001 5 3 2
002 5 2 3

[jinjazz]

--建立测试环境
Create Table 表(NO varchar(10),count integer,oldnew integer)
--插入数据
insert into 表
select '001','1','0' union
select '001','2','0' union
select '001','2','1' union
select '002','1','1' union
select '002','2','1' union
select '002','2','0'
select * from 表
--测试语句
select [no],sum([count])[count],sum(case when oldnew=1 then [count] else 0 end)[oldcount],
sum(case when oldnew=0 then [count] else 0 end)[newcount]
from 表
group by [no]

--删除测试环境
Drop Table 表

[zjcxc]

select No,
[count]=sum([count]),
oldcount=sum(case oldnew when 1 then [count] end),
newcount=sum([count])-sum(case oldnew when 1 then [count] end)
from 表 group by NO

[jinjazz]

列标志写反了,修改

--建立测试环境
Create Table 表(NO varchar(10),count integer,oldnew integer)
--插入数据
insert into 表
select '001','1','0' union
select '001','2','0' union
select '001','2','1' union
select '002','1','1' union
select '002','2','1' union
select '002','2','0'
select * from 表
--测试语句
select [no],sum([count])[count],sum(case when oldnew=1 then [count] else 0 end)[newcount],
sum(case when oldnew=0 then [count] else 0 end)[oldcount]
from 表
group by [no]

--删除测试环境
Drop Table 表

[zjcxc]

--写反了，改一下:

select No,
[count]=sum([count]),
oldcount=sum(case oldnew when 0 then [count] end),
newcount=sum([count])-sum(case oldnew when 0 then [count] end)
from 表 group by NO

[wyb0026]

select no sum(count) count ,
sum(case when oldnew=0 then count else 0 end) oldcount
sum(case when oldnew=1 then count else 0 end) newcount
from table
group by no

[wyb0026]

我还以为能抢一楼呢？还是大哥们厉害

[lxysjl]

接分。

[paoluo]

select No,
count=SUM(count),
oldcount=SUM(Case oldnew when 1 then [count] end),
newcount=SUM(Case oldnew when 0 then [count] end)
from 表 group by NO

[paoluo]

修改
select No,
count=SUM(count),
oldcount=SUM(Case oldnew when 0 then [count] End),
newcount=SUM(Case oldnew when 1 then [count] End)
from 表 Group by NO

[子陌红尘]

select
No,
count = sum([count]),
oldcount = sum(case when oldnew = 0 then [count] else 0 end),
newcount = sum(case when oldnew = 0 then [count] else 0 end)
from
t
group by
No
order by
No

[xluzhong]

select No,
[count]=sum([count]),
oldcount=sum(case oldnew when 1 then [count] end),
newcount=sum([count])-sum(case oldnew when 1 then [count] end)
from 表 group by NO

[zhaozhaozhaozhao]

select no,count=sum(count),oldcount=sum(case when oldnew=0 then count end),
newcount=sum(case when oldnew=1 then count end) from 表 group by no