[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

DK7 switch’s support for string

Before JDK7, switch can only support byte, short, char, int or their corresponding encapsulation classes and enum types. After JDK7, switch supports string type. In the switch statement, the value of the expression cannot be null, otherwise NullPointerException will be thrown at runtime. Null cannot be used in the case clause, otherwise compilation errors will […]