mysql - 单个或多个查询

Question

我有 3 张 table 。我需要输出所有三个结果，但按日期对它们进行分组并显示每天的记录数。

表格是:

business_actions
----------------
id
article_id
business_id
action


articles
----------------
id
user_id
item
date 
time

users
------------------
id
name
role

我不确定我是否应该尝试将所有这些都放入一个查询中，或者运行一个查询，在该查询中我将获得不同的日期和总记录数，然后运行其他查询以检索其他信息？..

我从这个开始，但没有走得太远。

SELECT
      COUNT(business_actions.id) AS cnt,
      business_actions.action,
      articles.user_id,
      articles.item,
      articles.date,
      articles.time,
      users.*
FROM business_actions
LEFT JOIN articles ON (business_actions.article_id = articles.id)
LEFT JOIN users ON (articles.user_id = users.id)
WHERE business_actions.business_id = ".$busID."
ORDER BY articles.time DESC
GROUP BY articles.date      
LIMIT 20 

限制实际上应该是天数，而不是记录...

最终结果应该是这样的:

July 20, 2015 (2)
    Some item -- 10:00 am
        Name: John M
        Role: Admin
        Action: login

    Another Item -- 10:30 am
        Name: Jeff M
        Role: User
        Action: publish

July 18, 2015 (3)
    Some item -- 2:00 am
        Name: Bill M
        Role: User
        Action: print

    Another Item -- 10:30 am
        Name: Bob W
        Role: Manager
        Action: exit

    One more item -- 2:00 pm
        Name: William N
        Role: User
        Action: logout

---

更新:这里是 Fiddle基于下面 Barmar 的回答。

Answer 1

为了限制天数，您需要加入一个获取这些日期的子查询。

SELECT
      cnt,
      business_actions.action,
      articles.user_id,
      articles.item,
      DATE(articles.date) AS date,
      articles.time,
      users.*
FROM business_actions
JOIN articles ON business_actions.article_id = articles.id
JOIN (SELECT DATE(date) AS date, count(*) as cnt
      FROM articles
      GROUP BY DATE(date)
      ORDER BY DATE(date) DESC
      LIMIT 20) AS dates
    ON DATE(articles.date) = dates.date
LEFT JOIN users ON (articles.user_id = users.id)
WHERE business_actions.business_id = 42
ORDER BY articles.date DESC, articles.time DESC

DEMO