我用的是mysql
table a(A,B,C,D,E)
数据如下
('a','b',null,null,'e')
(null,null,'c','d','e')
('aa','bb',null,null,'ee')
(null,null,'cc','dd','ee')
请问如何能将以上4行数据合并成如下两行
('a','b','c','d','e')
('aa','bb','cc','dd','ee')
table a(A,B,C,D,E)
数据如下
('a','b',null,null,'e')
(null,null,'c','d','e')
('aa','bb',null,null,'ee')
(null,null,'cc','dd','ee')
请问如何能将以上4行数据合并成如下两行
('a','b','c','d','e')
('aa','bb','cc','dd','ee')
insert into a values('a','b',null,null,'e')
insert into a values(null,null,'c','d','e')
insert into a values('aa','bb',null,null,'ee')
insert into a values(null,null,'cc','dd','ee')
goselect *,id=identity(int,1,1) into tmp from aselect isnull(m.a,n.a) a,
isnull(m.b,n.b) b,
isnull(m.c,n.c) c,
isnull(m.d,n.d) d,
isnull(m.e,n.e) e
from tmp m full join tmp n
on (m.id-1)/2 = (n.id-1)/2
where m.id%2 = 1 and n.id%2 = 0drop table a ,tmp/*
a b c d e
---------- ---------- ---------- ---------- ----------
a b c d e
aa bb cc dd ee(所影响的行数为 2 行)*/
COL1 VARCHAR(50),
COL2 VARCHAR(50),
COL3 VARCHAR(50),
COL4 VARCHAR(50),
COL5 VARCHAR(50)
)
INSERT INTO TB
SELECT 'a','b',null,null,'e' UNION ALL
SELECT null,null,'c','d','e' UNION ALL
SELECT 'aa','bb',null,null,'ee' UNION ALL
SELECT null,null,'cc','dd','ee'
SELECT IDENTITY(INT,0,1) 'ID',* INTO #TEMP FROM TB
SELECT MAX(COL1),MAX(COL2),MAX(COL3),MAX(COL4),MAX(COL5) FROM #TEMP
GROUP BY ID/2
DROP TABLE #TEMP
DROP TABLE TB
/*
a b c d e
aa bb cc dd ee
*/
(null,null,'c','d','e')
两行同一个字段除了有值就是null,我以前看过,大概是union all,然后groupby’e',但是现在不好用,
请各位高手,给个答案,谢谢!!!