Shulou(Shulou.com)06/01 Report--

How to solve the same problem in 10 different ways.

One of the common problems to solve in SQL is "Get row with the group-wise maximum". Getting just the maximum for the group is simple, getting the full row which is belonging to the maximum is the interesting step.

Finding the maximum value of each group may be simple, but finding all the information about the corresponding maximum value is a very interesting topic.

SELECT MAX (population), continent

FROM Country

GROUP BY continent

+-+ +

| | MAX (population) | continent | |

+-+ +

| | 1277558000 | Asia |

| | 146934000 | Europe |

| | 278357000 | North America |

| | 111506000 | Africa |

| | 18886000 | Oceania |

| | 0 | Antarctica |

| | 170115000 | South America |

+-+ +

We use the 'world' database from the MySQL manual for the examples.

We tried the 'world' database' in the MySQL manual as a test

The next step is to find the countries which have the population and the continent of our gathered data.

Next, we can find all the data corresponding to a piece of data.

* SELECT continent, name, population

FROM Country

WHERE population = 1277558000

AND continent = 'Asia'

+-+

| | continent | name | population | |

+-+

| | Asia | China | 1277558000 | |

+-+

Instead of doing this row by row we just do a JOIN between the two by using a temporary table:

To avoid a line-by-line search, we can create a temporary table and then use the join operation

CREATE TEMPORARY TABLE co2

SELECT continent, MAX (population) AS maxpop

FROM Country

GROUP BY continent

SELECT co1.continent, co1.name, co1.population

FROM Country AS co1, co2

WHERE co2.continent = co1.continent

AND co1.population = co2.maxpop;*

+-+

| | continent | name | population | |

+-+

| | Oceania | Australia | 18886000 | |

| | South America | Brazil | 170115000 | |

| | Asia | China | 1277558000 | |

| | Africa | Nigeria | 111506000 | |

| | Europe | Russian Federation | 146934000 | |

| | North America | United States | 278357000 | |

| | Antarctica | Antarctica | 0 | |

| | Antarctica | Bouvet Island | 0 | |

| | Antarctica | South Georgia and the South Sandwich Islands | 0 | |

| | Antarctica | Heard Island and McDonald Islands | 0 | |

| | Antarctica | French Southern territories | 0 | |

+-+

DROP TEMPORARY TABLE co2

Delete temporary table

Instead of using a temporary table as internal steps we can write the same also as simple sub-query which is creating a temporary table internally.

Of course, we can also use subqueries instead of temporary tables

* SELECT co1.continent, co1.name, co1.population

FROM Country AS co1

(SELECT continent, MAX (population) AS maxpop

FROM Country

GROUP BY continent) AS co2

WHERE co2.continent = co1.continent

And co1.population = co2.maxpop;***

+-+

| | continent | name | population | |

+-+

| | Oceania | Australia | 18886000 | |

| | South America | Brazil | 170115000 | |

| | Asia | China | 1277558000 | |

| | Africa | Nigeria | 111506000 | |

| | Europe | Russian Federation | 146934000 | |

| | North America | United States | 278357000 | |

| | Antarctica | Antarctica | 0 | |

| | Antarctica | Bouvet Island | 0 | |

| | Antarctica | South Georgia and the South Sandwich Islands | 0 | |

| | Antarctica | Heard Island and McDonald Islands | 0 | |

| | Antarctica | French Southern territories | 0 | |

+-+

The sub-query is executed in the exact same way as the temporary table we created by hand. Instead of JOINing against the temporary table we JOIN against the result of the sub-query.

Hmm, this was too simple? Let's take a look at the alternatives:

SELECT co1.continent, co1.name, co1.population

FROM Country AS co1

WHERE co1.population =

(SELECT MAX (population) AS maxpop

FROM Country AS co2

WHERE co2.continent = co1.continent)

To be read as: 'Get the countries which have the same population as the maximum population of the current country'. Using such a sub-qeury results in more readable sub-queries. BUT... They a 'DEPENDENT' as the inner query is refering to a field of the outer query. This means that for each row of the outer query the inner query is executed.

The same query can be written in two other ways:

SELECT continent, name, population

FROM Country

WHERE ROW (population, continent) IN (

SELECT MAX (population), continent

FROM Country

GROUP BY continent)

SELECT co1.continent, co1.name, co1.population

FROM country as co1

WHERE co1.population > = ALL

(SELECT co2.population

FROM country AS co2

WHERE co2.continent = co1.continent)

If you don't want to use sub-queries and prefer pure JOINs perhaps there are for you:

SELECT co1.continent, co1.name, co1.population

FROM country AS co1 LEFT JOIN country AS co2

ON co1.population < co2.population AND

Co1.continent = co2.continent

WHERE co2.population is NULL

SELECT co1.Continent, co1.Name

FROM Country AS co1 JOIN Country AS co2

ON co2.Continent = co1.Continent AND

Co1.Population

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.