id name sex age student
1 cngothic 1 null null
2 cngothic null 24 null
3 cngothic null null 1这三种如何拼成1 cngothic 1 24 1另把id:2, 3删除吊
1 cngothic 1 null null
2 cngothic null 24 null
3 cngothic null null 1这三种如何拼成1 cngothic 1 24 1另把id:2, 3删除吊
调试欢乐多
drop table tb
go
create table tb(id int, name varchar(10), sex int, age int, student int)
insert tb select 1,'cngothic',1,null,null
insert tb select 2,'cngothic',null,24,null
insert tb select 3,'cngothic',null,null,1select min(id) id,name,max(isnull(sex,0)) sex,max(isnull(age,0)) age,max(isnull(student,0)) student from tb group by name
/*
id name sex age student
----------- ---------- ----------- ----------- -----------
1 cngothic 1 24 1(1 行受影响)
*/
insert #1 select 1,'cngothic',1,null,null
insert #1 select 2,'cngothic',null,24,null
insert #1 select 3,'cngothic',null,null,1select 1 id,name,max(sex) sex,max(age) age,max(student) student from #1
group by name
/*
id name sex age student
----------- ---------- ----------- ----------- -----------
1 cngothic 1 24 1
Warning: Null value is eliminated by an aggregate or other SET operation.(1 row(s) affected)
*/
我的意思是数据库中实实在在的只存在一条/*
id name sex age student
----------- ---------- ----------- ----------- -----------
1 cngothic 1 24 1这个怎么实现
如果你的table是這個樣子:
id name sex age student
1 cngothic 1 null null
2 cngothic null 24 2
3 cngothic null null 1 你要怎么合并?
2 cngothic null 24 2
3 cngothic null null 1
这种情况不存在。只有1 cngothic 1 null null
2 cngothic null 24 1
3 cngothic null null 1
if object_id('tb') is not null
drop table tb
go
create table tb(id int, name varchar(10), sex int, age int, student int)
insert tb select 1,'cngothic',1,null,null
insert tb select 2,'cngothic',null,24,null
insert tb select 3,'cngothic',null,null,1update a
set a.sex=b.sex,a.age=b.age,a.student=b.student
from
tb a,
(select min(id) id,name,max(isnull(sex,0)) sex,max(isnull(age,0)) age,max(isnull(student,0)) student
from tb
group by name
) b
where a.id=b.id and a.name=b.namedelete t from tb t where exists(select 1 from tb where name=t.name and id<t.id)select * from tb
/*
id name sex age student
----------- ---------- ----------- ----------- -----------
1 cngothic 1 24 1(1 行受影响)
*/