preface

Use the lunch break to write some tips about SQL, I hope it can be seen and used by students who need it.

One time I was on StackOverflow and saw this question from a girl,

No one answer, it seems not difficult, I thought it was time for me to install X, a good answer to get the favor of the sister, maybe you can know, after learning English with somebody else what.

So I quickly clicked on the Translate plugin for Chrome and translated the question:

Basically, she wants to group values with the same ID and label the contents of each group with an extra column.

For example, in the figure, two rows of data with ID 10000 are marked 10000-1 in the first row and 10000-2 in the second column.

To solve

SELECT
	id,
	concat(id,'_'.row_number(a)over ( PARTITION BY id)) extra
FROM
	table
Copy the code

The row_number() over (PARTITION BY ID) statement is the focus of this article. It is a simple statement that can group a field first and then mark it within the group.

further

PARTITION BY column1, column2,… Which columns do you group by? This is consistent with the common group by syntax.

At the same time, it can use the order by syntax to implement the group reorder.

As shown in the figure, the group with PID = 1 has seven data. If id= 196 is sorted by id, where is it in the group pid=1? (The obvious answer is No. 3). Row_number () over PARTITION BY column_name ORDER BY column_name

SELECT
	id,
	pid,
	row_number(a)over ( PARTITION BY pid) pid_rank,
	concat(pid,'_'.row_number(a)over ( PARTITION BY pid)) result
FROM
	lock_test_order
Copy the code

The result is correct.

Row_number () in the previous example means the number of rows in a group. There are also other functions that can be replaced before the over, and other functions that can be used for grouping.

Such as:

count(pid) over(partition by pid) total_count
Copy the code

Calculate the total number of rows in the group.

instructions

This tutorial is based on MySQL databases. Some other databases do not support this syntax.

Other references: www.sqlshack.com/sql-partiti…