我有表 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语句,求解答。谢谢。
------------------------
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语句,求解答。谢谢。
sum(ifnull(point1,0)) as point1,
sum(ifnull(point2,0)) as point2
from pointgroup by id
sum(COALESCE(point2,0)) as point2
from point group by `id`
from Point a,Point b
where a.id=b.id
and a.point1 is not null
and b.point2 is not null
如果相同ID的point1 或point2的值都不为空,是加起来,还是?取哪个?
sum(ifnull(point1,0)) as point1,
sum(ifnull(point2,0)) as point2
from Pointgroup by id
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);