按非空值分组

本文介绍了按非空值分组的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有一个数据库:

+--+----+---------+ |id|name|source_id| +--+----+---------+ |11|aaaa|null +--+----+---------+ |12|bbbb|1 +--+----+---------+ |13|cccc|1 +--+----+---------+ |14|dddd|null +--+----+---------+ |15|eeee|2 +--+----+---------+ |16|ffff|2 +--+----+---------+ |17|gggg|2 +--+----+---------+

我想选择所有条目，但例外:

I'd like to select all entries, with an exception:

• 所有带有source_id == somevalue(not null)的条目均应分组
此后应该存在带有source_id == null的
• 最后一个条目

• all entries with source_id == somevalue(not null) should be grouped
• last entry with source_id == null should exist afterwards

这是我期望的结果:

+----+--+----+---------+ COUNT|id|name|source_id| +----+--+----+---------+ .....|11|aaaa|null +----+--+----+---------+ .2...|13|cccc|1 +----+--+----+---------+ .....|14|dddd|null +----+--+----+---------+ .3...|17|ffff|2 +----+--+----+---------+

到目前为止，我已经完成了此操作(注意:"change_count" = COUNT):

So far I've done this (note: "change_count" = COUNT):

SELECT *, (To_days(date_expires)-TO_DAYS(NOW())) as dayDiff, COUNT(id) AS change_count FROM mytable GROUP BY source_id HAVING dayDiff < 4 ORDER BY date_created DESC

这就是我得到的:

+----+--+----+---------+ COUNT|id|name|source_id| +----+--+----+---------+ .2...|11|aaaa|null +----+--+----+---------+ .2...|12|bbbb|1 +----+--+----+---------+ .3...|15|eeee|2 +----+--+----+---------+

您可以看到结果有两个问题:

As you can see the result has 2 problems:

• 条目按第一个source_id出现分组(应为最后(最新))
• nulls也已分组(不应分组)

• entries are grouped by first source_id occurrence (should be last(latest))
• nulls are also grouped (shouldn't be grouped)

我上面描述的任务可以实现吗?我该怎么办?

Is the task that I've described above achievable? How do I do it?

SELECT *, COUNT(id) AS change_count FROM (SELECT *, (To_days(date_return_due)-TO_DAYS(NOW())) as dayDiff FROM mytable WHERE owner_id='1' HAVING dayDiff < 100 ORDER BY date_created DESC) AS newtable GROUP BY (CASE WHEN source_id IS NULL THEN id ELSE source_id END) ORDER BY (CASE WHEN source_id IS NULL THEN 1 ELSE 0 END), date_created DESC";

推荐答案

我认为以下内容可以满足您的需求:

I think the following does what you want:

SELECT *, (To_days(date_expires)-TO_DAYS(NOW())) as dayDiff, COUNT(id) AS change_count FROM mytable GROUP BY (case when source_id is null then id else source_id end) HAVING dayDiff < 4 ORDER BY (case when source_id is null then 1 else 0 end), date_created DESC

它执行条件group by，因此不会将NULL源ID分组.然后使用order by中的逻辑将它们置于最后.

It does a conditional group by so the NULL sourceids will not be grouped. It then puts them last using logic in order by.

我不明白您最后一次发生的意思.现在我想知道了:

I didn't understand what you meant by last occurrence. Now I think I do:

SELECT coalesce(s.id, mytable.id) as id, max(case when s.maxid is not null and s.maxid = myable.id then mytable.name when s.maxid is null then NULL else mytable.name end) as name, (To_days(date_expires)-TO_DAYS(NOW())) as dayDiff, COUNT(id) AS change_count FROM mytable left outer join (select source_id, MAX(id) as maxid from mytable where source_id is not null group by source_id ) s on mytable.id = s.maxid GROUP BY (case when source_id is null then id else source_id end) HAVING dayDiff < 4 ORDER BY (case when source_id is null then 1 else 0 end), date_created DESC

这将加入最新记录中的信息(基于最高ID).

This joins in the information from the latest record (based on highest id).