php - MySQL:聚合和分组

Question

这里有点 MySQL 和 PHP 的问题。

我有这两个表:

CREATE Table users (
  id BIGINT(100) AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) NOT NULL, 
  password VARCHAR(50) NOT NULL
);

CREATE Table articles (
  id BIGINT(100) AUTO_INCREMENT PRIMARY KEY,
  author_id BIGINT(100) NOT NULL, 
  title VARCHAR(50) NOT NULL, 
  content TEXT, 
  published DATETIME NOT NULL
);

插入:

INSERT INTO users (username, password) 
VALUES ('Bob', '5f4dcc3b5aa765d61d8327deb882cf99');

INSERT INTO users (username, password) 
VALUES ('David', '630bf032efe4507f2c57b280995925a9');

INSERT INTO articles (author_id, title, content, published)
VALUES ('1', 'Science 101', 'Science is the concerted human effort to understand, or to understand better, the history of the natural world and how the natural world works, with observable physical evidence as the basis of that understanding1. It is done through observation of natural phenomena, and/or through experimentation that tries to simulate natural processes under controlled conditions.',
        '2015-11-30 09:18:43');

INSERT INTO articles (author_id, title, content, published)
VALUES ('1', 'Health Care', 'Health care or healthcare is the maintenance or improvement of health via the diagnosis, treatment, and prevention of disease, illness, injury, and other physical and mental impairments in human beings.',
        '2016-01-10 15:20:43');

INSERT INTO articles (author_id, title, content, published)
VALUES ('2', 'Physics 101', 'Physics is a natural science based on experiments, measurements and mathematical analysis with the purpose of finding quantitative physical laws for everything from the nanoworld of the microcosmos to the planets, solar systems and galaxies that occupy the macrocosmos.',
        '2016-01-17 14:18:43');

查询:

SELECT 
  articles.id, 
  articles.author_id,
  users.username,
  COUNT(articles.id) AS number_of_articles,
  GROUP_CONCAT(DISTINCT articles.id) AS articles_published
FROM articles 
LEFT JOIN users ON articles.author_id = users.id
GROUP BY articles.author_id 
ORDER BY articles.published DESC;

结果:

id  author_id   username    number_of_articles  articles_published
3   2           David       1                   3
2   1           Bob         2                   2,1

这是我的 SQLfiddle:http://sqlfiddle.com/#!9/964ed/1

articles_published 列包含如上所示的 ID，使用这些 ID，我想获取附加信息，例如:属于各自作者的每篇文章的标题和内容。

我在某处读到可以使用 explode (PHP)，但这只会为您提供 ID 数组，而不是我想要的结果。我想要这个聚合，如显示的那样(参见预期结果部分)。

$r = $result[1]['articles_published']; //2,1 
$array = explode(',',$r);
print_r($array);

//Output:
Array
(
    [0] => 2
    [1] => 1
)

包含爆炸后结果的数组并不能真正帮助我获得额外的结果，例如:文章表中 ID 为 1 和 2 的文章的标题和内容。

预期结果:

David wrote 1 article

Physics 101
Physics is a natural science based on experiments, measurements and mathematical analysis with the purpose of finding quantitative physical laws for everything from the nanoworld of the microcosmos to the planets, solar systems and galaxies that occupy the macrocosmos.

-----

Bob wrote 2 articles

Health Care
Health care or healthcare is the maintenance or improvement of health via the diagnosis, treatment, and prevention of disease, illness, injury, and other physical and mental impairments in human beings.

Science 101
Science is the concerted human effort to understand, or to understand better, the history of the natural world and how the natural world works, with observable physical evidence as the basis of that understanding1. It is done through observation of natural phenomena, and/or through experimentation that tries to simulate natural processes under controlled conditions.

------

编辑: 是的，至于 MySQL，我知道我可以做到(见下文)，但我不希望通过 MySQL 聚合和分组内容。

SELECT users.username
     , articles.title 
     , articles.content
  FROM users
INNER
  JOIN articles 
    ON articles.author_id = users.id
ORDER 
    BY users.username
     , articles.title

Answer 1

两种选择:

首先

运行第二个查询以获取文章数据。

SELECT * FROM `articles` WHERE `id` IN (articles_published)

第二个

在文章标题和内容上也使用 GROUP_CONCAT，就像在 id 上一样。与不会出现在文章标题或内容中的内容分开。例如:

SELECT 
  articles.id, 
  articles.author_id,
  users.username,
  COUNT(articles.id) AS number_of_articles,
  GROUP_CONCAT(DISTINCT articles.id) AS articles_id,
  GROUP_CONCAT(articles.title SEPARATOR '<--next article-->') AS articles_title,
  GROUP_CONCAT(articles.content SEPARATOR '<--next article-->') AS articles_content
FROM articles 
LEFT JOIN users ON articles.author_id = users.id
GROUP BY articles.author_id 
ORDER BY articles.published DESC;

您的 PHP 看起来像这样。

foreach ($results as $result) {
  $article_ids      = explode(',', $result['articles_id']);
  $article_titles   = explode('<--next article-->', $result['articles_title']);
  $article_contents = explode('<--next article-->', $result['articles_content']);

  echo $result['username'].' wrote '.$result['number_of_articles'].' '.($result['number_of_articles']>1 ? 'articles' : 'article').'<br /><br />';

  foreach ($article_ids as $key => $article_id) {
    $title   = $article_titles[$key];
    $content = $article_contents[$key];

    echo $title.'<br />';
    echo $content.'<br /><br />';
  }

  echo '-----<br />';
}