如何查询合并一条记录。

[frank630]

我有表 Point
------------------------
id | point1 | point2|
------------------------
1 | 2.23 | null |
------------------------
1 | null | 4.34 |
------------------------
2 | null | 3.34 |
------------------------
2 | 4.34 | null |
------------------------

现在我想查询合并id相同的记录，已知表中有每两条记录的id相同，其中一条记录的point1为空，另一条记录的point2为空，我想的到的结果是这样 1,2.23,4.34 和 2,4.34,3.34 应该怎么样写sql语句，求解答。谢谢。

[rucypli]

引用 1 楼 u010581018 的回复:

select id,
       sum(ifnull(point1,0)) as point1,
       sum(ifnull(point2,0)) as point2
 
from point

group by id

[yo_yo1120]

select id,
       sum(ifnull(point1,0)) as point1,
       sum(ifnull(point2,0)) as point2
 
from point

group by id

[齐岳]

select id,
       sum(ifnull(point1,0)) as point1,
       sum(ifnull(point2,0)) as point2
 
from Point

group by id

[WWWWA]

select `id`, sum(COALESCE(point1,0)) as point1, sum(COALESCE(point2,0)) as point2 from point group by `id`

[ACMAIN_CHM]

每两条记录的id相同，其中一条记录的point1为空，另一条记录的point2为空 select a.id,a.point1,b.point2 from Point a,Point b where a.id=b.id and a.point1 is not null and b.point2 is not null

[tashiwoweiyi]

我想说的是，你只要你上面的这种结果语句吗？ 如果相同ID的point1 或point2的值都不为空，是加起来，还是？取哪个？

[知道就是你]

一条SQL搞定 ： SELECT concat(`sid`,',',GROUP_CONCAT(point1) ,',', GROUP_CONCAT(point2) ) FROM ` point` WHERE 1 group by sid ; concat(`sid`,',',GROUP_CONCAT(point1) ,',', GROUP_CONCAT(point2) ) 1,3.34,4.34 2,2.34,4.34

[知道就是你]

上边表结构如下： CREATE TABLE IF NOT EXISTS ` point` ( `id` int(11) NOT NULL AUTO_INCREMENT, `sid` int(11) NOT NULL, `point1` float DEFAULT NULL, `point2` float DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=5 ; -- -- 转存表中的数据 ` point` -- INSERT INTO ` point` (`id`, `sid`, `point1`, `point2`) VALUES (1, 2, 2.34, NULL), (2, 2, NULL, 4.34), (3, 1, 3.34, NULL), (4, 1, NULL, 4.34);