Database common Operation collation

Example one:

In the database there are often the following statistical operations, to count the number of samples of a certain type, and to find more than 500 samples of the type, as well as the number of samples. For example, in SQL, a table is defined as follows:

CREATE TABLE t_account (account     varchar (+),     account_type tinytext,     PRIMARY KEY (account),};

Account accounts, Account_type for the type of account, write a SQL, statistics account number accumulated more than 5,000 account types, and display the corresponding number of accounts, that is, the results of each line is (account type, account number)

Select Account_type, Count from T_account Group by Account_type have count (account) >5000;

Example two: (Leetcode196:delete Duplicate Emails)

Write a SQL query to delete all duplicate e-mail entries in a table named Person , keeping unique emails based on its smallest Id.

+----+------------------+| Id | Email            |+----+------------------+| 1  | [Email protected] | | 2  | [email protected]  | | 3  | [Email protected] |+----+------------------+id is the primary key, column for this table.

For example, after running your query, the above Person table should has the following rows:

+----+------------------+| Id | Email            |+----+------------------+| 1  | [Email protected] | | 2  | [email protected]  | +----+------------------+

Locate the data that you want to keep and then use not in to delete the records in that data. It is easy to think of the following SQL statement:

Delete from the person where the ID isn't in (the select * FROM (Id) from the person Group by Email));

However, the MySQL delete action can not carry the query action of this table, meaning that you delete the users table of things can not be the person table information as a condition so this statement will error, can not execute. As long as the query condition is created by creating a temporary table. The specific implementation is as follows:

Delete from the person where the ID is not in (SELECT * FROM (select min (Id) from the person group by Email) as W);

　　

Database common Operation collation