mysql - SQL 按条件计数排序，显示历史记录

Question

考虑以下简单的 MySQL 查询:

SELECT date, count(*) cnt, ip 
FROM fwlog
GROUP BY ip, date
HAVING cnt>100
ORDER BY date DESC, cnt DESC

它给了我这样的东西:

date    cnt     src
2013-06-20      14441   172.16.a
2013-06-20      8887    172.16.b
....
2013-06-19      14606   172.16.b
2013-06-19      12455   172.16.a
2013-06-19      5205    172.16.c

也就是说，它按照指示按日期排序 IP，然后按计数排序。

现在我希望结果是:

• 首先显示TODAY计数最高的IP，
• 然后显示该 IP 最近几天的计数(独立于 cnt)，
• 然后显示今天计数第二高的IP，
• 然后是该 IP 的历史，
• etc.pp.

期望结果示例:

date    cnt     src
2013-06-20      14441   172.16.a
2013-06-19      12455   172.16.a
2013-06-18      ....    172.16.a
2013-06-17      ....    172.16.a
....
2013-06-20      8887    172.16.b
2013-06-19      14606   172.16.b
2013-06-18      ....    172.16.b
2013-06-17      ....    172.16.b
...
2013-06-20      ....    172.16.c
2013-06-19      ....    172.16.c
2013-06-18      ....    172.16.c
2013-06-17      ....    172.16.c
...
...

这甚至可以使用普通 SQL 完成吗？

再见，

马基

==========================================

@戈登利诺夫:

SELECT 
    datex,
    cnt,
    ip
FROM
    fwlog
WHERE
    ...
GROUP BY ip , datex
ORDER BY SUM(case
    when datex = DATE(NOW()) then 1
    else 0
end) DESC , src, date DESC, cnt DESC

2013-06-20      47      10.11.y
2013-06-19      47      10.11.y
2013-06-18      45      10.11.y
2013-06-17      42      10.11.y
2013-06-16      14      10.11.y
....
2013-06-20      592     172.16.a
2013-06-19      910     172.16.a
2013-06-18      594     172.16.a
2013-06-17      586     172.16.a
2013-06-20      299     172.16.b

这还不太对，下面的 block 应该在顶部。

Answer 1

尝试:

SELECT a.`date`, count(*) cnt, a.ip 
FROM fwlog a
JOIN (SELECT ip, count(*) today_count
      FROM fwlog 
      where `date` = date(now()) 
      group by ip) t
  ON a.ip = t.ip and t.today_count > 100
GROUP BY a.ip, a.date
ORDER BY t.today_count DESC, a.ip, a.`date` DESC