SQL queries the records corresponding to the max () value in the Category field of a table

The problem is:

The database has a table code with a clickthrough field click_num, a category field kind, and other information fields,

Now we need to find the record with the largest clicks in each category. If there are 10 categories, the result should be 10 records,

If the maximum number of clicks is the same, only one.

After N searches and N queries, I finally found an elegant SQL statement with correct results. This was found by a blog author in the MySQL official documentation.

You can't help adding your favorites for future use.

 

1. SelectID, kind, click_numFromCodeAsA
2. WhereClick_num = (Select Max(B. click_num)
3. FromCodeAsB
4. WhereA. Kind = B. Kind
5. );

 

Note:

This statement runs normally on SQLite and is fast. However, the execution on mysql5.0 on my machine crashes, and other MySQL versions do not know what the situation is.

The preceding statement does not meet my second requirement: if the maximum clicks in a category have two identical records, only one.

The following is my attached solution:

Filter to ensure that there is only one row for each category. (If kind and ID are changed to all columns, the result is the corresponding record)

 

2. Select*
4. From(SelectID, kind, click_numFromCodeAsA
6. WhereClick_num = (Select Max(B. click_num)
8. FromCodeAsB
10. WhereA. Kind = B. Kind
12. )
14. )AsA
16. Group ByKind

 

 

 

Follow-up:

One of the biggest problems that MySQL has encountered when using MySQL is that if the WHERE clause in a query contains queries to the previously selected table, the CPU usage will soar and the MySQL service will stop. It can be said that it is a self-connection query problem. I don't know if this is a bug. versions 5.0 and 5.1 all have this problem. Let's look back and see that the above SQL statements exactly match this problem, the solution is to make the sub-query table into a temporary table or view. In short, it cannot be the same as the previous select table. Then the problem is solved, you can run MySQL with a slight modification to the preceding SQL statement.

Modification Process:

1. Create a max_click view that stores the maximum clicks for each category

1. Create ViewMax_clickAs Select Max(Click_num)AsClick_num, kind
2. FromCode
3. Group ByKind;

2. Filter to ensure that each category has only one record

 

 

2. Select*
4. From(SelectID, kind, click_num
6. FromCode
8. WhereClick_num = (SelectB. click_num
10. FromMax_clickAsB
12. WhereCode. Kind = B. Kind
14. )
16. )AsA
18. Group ByKind;

Running properly. The speed is 0.78 seconds. (23000 data entries in the table) Okay! Success!

I don't know if the first SQL statement can run on MSSQL. I think it should be okay if SQLite can run normally on MSSQL. It seems that MySQL needs to be improved a lot, the same is true for open-source SQLite, And the lightweight SQLite is several hundred kb. MySQL should be able to achieve this.

 

Transferred from:Http://blog.csdn.net/xiurui12345/article/details/7448744