[SQL] data De duplication partition or group by?

Time:2021-1-17

For the use of distinct and group by:
1. When the performance of the system is high and the amount of data is large, group by is used
2. When the performance of the system is not high and the amount of data used is small, both can be used
3. Try to use group by

Simple example:

Find out all employees' current (to_ Specific salary
The same salary is displayed only once and in reverse order

CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`))

code:

SELECT DISTINCT salary FROM salaries

WHERE to_date ='9999-01-01' ORDER BY salary DESC

Equivalent to

SELECT salary FROM salaries
WHERE to_date='9999-01-01'
GROUP BY salary
ORDER BY salary desc

Recommended Today

Introduce regular expressions in ruby in detail

A regular expression is a special sequence of characters that matches or finds other strings or collections of strings by using patterns with special syntax. grammar A regular expression is literally a pattern between slashes or any separator after% R, as follows: ? 1 2 3 4 5 6 7 8 9 10 11 12 […]