1.4w+
社区成员
25.3w+
社区内容
- 主页
- 基础类
- 应用实例
- 新技术前沿
SQL中一个表中几条数据怎么合成一条！？
比如 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
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
...全文
zhaozhaozhaozhao 2005年03月18日
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
newcount=sum(case when oldnew=1 then count end) from 表 group by no
xluzhong 2005年03月18日
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
[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
子陌红尘 2005年03月18日
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
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
paoluo 2005年03月18日
修改
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 oldnew when 0 then [count] End),
newcount=SUM(Case oldnew when 1 then [count] End)
from 表 Group by NO
paoluo 2005年03月18日
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
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
lxysjl 2005年03月18日
接分。
wyb0026 2005年03月18日
我还以为能抢一楼呢？还是大哥们厉害
wyb0026 2005年03月18日
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
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
zjcxc--个人微信公共号同名 2005年03月18日
--写反了，改一下:
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
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
jinjazz 2005年03月18日
列标志写反了,修改
--建立测试环境
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 表
--建立测试环境
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--个人微信公共号同名 2005年03月18日
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
[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 2005年03月18日
--建立测试环境
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 表
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 表