Mysql，选择按不同列分组的 id(选择每个唯一域的所有 id) [英] Mysql, select id's grouped by distinct column (select all ids for each unique domain)

问题描述

所以我有一个 MySQL 表:

So I have a MySQL table:

Table Data:

id | domain     | title                | Full Url to Page | ... etc ...
1  | place.com  | Place Something      | http://place.com/1...
2  | place.com  | Place Something Else | http://place.com/2...
3  | place.com  | Some Other Place     | http://place.com/3...
4  | pets.com   | Cats Dogs Oh My      | http://pets.com/2....
5  | pets.com   | Bird Edition         | http://pets.com/3....

我需要(在 PHP/JQuery 中)是为每个唯一域获取一组 id.所以上表我想要的最终输出是:

What I need (in PHP / JQuery) is to get an array of id's for each unique domain. So the final output that I want for the above table is:

$finalArray = array('place.com' => array(1, 2, 3), 'pets.com' => array(4, 5));

我目前的解决方案是获取按域排序的所有行，这是 MySQL 语句:

My current solution is to grab all rows ordered by domain, heres the MySQL statment:

SELECT `id`, `domain` FROM `gsort_linkdata` ORDER BY `domain`

哪个返回:

1  | place.com
2  | place.com
3  | place.com
4  | pets.com
5  | pets.com

然后我遍历 PHP 中的行并将它们分解为数组.我更愿意从数据库中提取已经分解的数据.那可能吗?谢谢！

I then loop through the rows in PHP and break them into arrays. I would prefer to pull already broken up data from the database. Is that possible? Thank you!

推荐答案

你可以使用 GROUP_CONCAT:

SELECT GROUP_CONCAT(`id`), `domain` FROM `gsort_linkdata` GROUP BY `domain`

(注意group_concat_max_len.)

但是因为没有办法将数组从 MySQL 传递到 PHP，所以需要在 PHP 或 JS 中拆分结果字符串，所以我认为您当前的方法是更好的方法.您的方法是安全的，实际上只是 PHP 中的单行，其他任何内容(包括 GROUP_CONCAT)都更复杂.

But because there is no way to pass an array from MySQL to PHP, you need to split up the resulting string in PHP or JS, so I think your current method is the better one. Your method is safe and really just a one-liner in PHP, anything else (including GROUP_CONCAT) is more complicated .

这篇关于Mysql，选择按不同列分组的 id(选择每个唯一域的所有 id)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！