类似于此问题:by not-null值组我正在尝试将具有groupID
列的记录分组为null:
+--+-------+------+-----+-----+----------+
|id|groupId|isMain|name |stars|created |
+--+-------+------+-----+-----+----------+
..1..abcd....1.....john....5...2018-06-01.
..2..NULL....0.....albert..3...2018-05-01.
..3..abcd....0.....clara...1...2018-06-01.
..4..NULL....0.....steph...2...2018-07-01.
使用此查询,我只能将groupId
不为null的记录进行分组:
SELECT *, SUM(stars) as stars
FROM table AS
GROUP BY (case when `groupId` is null then id else `group` end)
ORDER BY created DESC
这给了我结果:
4..NULL....0.....steph...2...2018-07-01
3..NULL....0.....clara...6...2018-06-01
2..NULL....0.....albert..3...2018-05-01
我试图选择分组的那些记录,即isMain
是1
,但我不知道如何实现这一目标。
我尝试使用HAVING
,但这给了我完全不同的结果。
您可以使用案例或IFNULL但是,您应该使用适当的聚合功能和组列的组条款,例如:
select ifnull(groupID, id), name, sum(stars), max(created) as my_create
from table
group by ifnull(groupID, id), name
order by my_create