php序列号(按id组)

Question

我想创建一个来自mysql查询的按ID组编号的序列。

mysql表如下所示：

--------------------------
id | user_id | company_id 
--------------------------
1  |   61    |     1 
2  |   71    |     1 
3  |   81    |     1 
4  |   91    |     2 
5  |   10    |     2
6  |   11    |     2

我想输出这样的东西：

Company: 1 , User: 61, position: 1
Company: 1 , User: 71, position: 2
Company: 1 , User: 81, position: 3
Company: 2 , User: 91, position: 1
Company: 2 , User: 10, position: 2
Company: 2 , User: 11, position: 3

我想出了这个代码，但它没有像我想的那样工作。

$sql = 'SELECT `id`, `user_id`, `company_id` FROM `user_company` ORDER BY `company_id`';
$result = $conn->query($sql);

if($result->num_rows){
$id = '';
while($obj = $result->fetch_object()){
if($id != $obj->id){
$seq = 1;
$id = $obj->id;
}else{
$seq++;
}

echo 'Company: '.$obj->company_id.', User: '.$obj->user_id.', position: '.$seq.'<br/>';
}
}

我该怎么做？

Answer 1

您可以使用row_number来获得所需的结果：

select company_id as company, 
user_id  as user_id ,
row_num  as row_num 
from 
( select *,row_number() over(partition by company_id order by id asc) as row_num
from user_company
) as tbl 
order by company_id asc,row_num asc

或者，如果您想直接从查询中获得所需的结果，请尝试：

select concat('Company: ',company_id,' , User: ',user_id, ', position: ',row_num ) as my_row 
from 
( select *,row_number() over(partition by company_id order by id asc) as row_num
from user_company
) as tbl 
order by company_id asc,row_num asc 

哪个会给出：

my_row
Company: 1 , User: 61, position: 1
Company: 1 , User: 71, position: 2
Company: 1 , User: 81, position: 3
Company: 2 , User: 91, position: 1
Company: 2 , User: 10, position: 2
Company: 2 , User: 11, position: 3

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=5a251db9dc2099f0ae0cdb96c3b7125

Answer 2

附带说明MySQL自8.0版本起引入了ROW_NUMBER((函数。

另一种方法是利用ROW_NUMBER((，它为结果集中的每一行分配一个序列号。第一个数字以一开头。

因此，在内部查询中，它为查询中的每个记录分配一个rownoROW_NUMBER((OVER(按company_id划分，按company_id排序(为rowno

最后，分组允许对公司和用户进行分组，以获得每个分组的顺序迭代

SELECT company_id,user_id,rowno as position
FROM
( 
SELECT company_id,user_id,
ROW_NUMBER() OVER(PARTITION by company_id order by company_id) as rowno
FROM playground.Positions 
) as tbl
group by company_id,user_id;

Fiddle