mysql - How to mix AND improve this SQL sentences? -


First of all, I am using the World Database for mySQL.

I am trying to create a section that returns to least inhabited countries from every continent, shows them all for every continent if the population is the same.

With the following sentence, I manage to get the population of at least settled people in each continent, but only one of them shows and the country shows the wrong. Select

Gives this result 

  'Africa', 'Angola', '0 HB' 'Antarctica', 'Antarctica', '0 HB' 'Asia' Afghanistan, '286,000 Hb' 'Europe', 'Albania', '1000 Hb' 'North America', 'Aruba', '7000 Hb' 'Oceania', 'American Samoa', '0 HB' South America ',' Argentina ',' 2000 HB '

OK, this code is very dumb, I think the query for every continent to return the least resident countries Will happen. I want to know if this is present, because there are only 7 continents, but this can be done, but if they were 1000000 ...

With these sentences, For the continent I would indicate, for example: 

  SELECT Name, population population from country = (SELECT MIN (population) from country where from country = continent = "Antarctica ");  
 Returns:  
 'Antarctica', '0'  
 'French Southern Region', '0' '0'  
 'Heard Island and McDonald Islands',' 0 ' 
' British Indian Ocean Area ',' 0 ' 
' South Georgia and 'South Sandwich Island', '0'.  
 'United States Minor Outlying Islands', '0'  
 I think there should be a way of combining both sentences and each of them The same as the last result for the continent It's what I'm telling in the beginning. I mean if the only continents in the world should add Antarctica and South America to this last result:  
 'Falkland Islands',' 2000]  
 'Tokelau', '2000'  
 'Norfolk Island', 2000 ' 
' New ',' 2000 ' P> I know that I can make a sentence to the union for each continent, but once again, it is only possible because There are only 7 continents.  
 What am I missing? thanks for your help.   
 
 
 To obtain the names of the smallest population countries for each continent, you identify those countries There is a need to do something like this: -  
  Continent from the continent of the continent of the continent, the continent select continent (population)  
 
 The country does not return the name, and you can not legally just add the country's name to the query (MySQL will not return the error, but the result will be Chit are). 
 
 As you may use as a sub-query and may contain it against the name of the continent and the population between the table 
 
  SELECT b.Continent , B.Name, b.Population (SELECT continent, min (population) A mass population by continent by country group) one INNER JOIN country B on one. Continent = B. Contact and A. Population = B. The bottom is that if there is a similar population in the population of more than one country then the continents are going to be weird.  
 Another option is to use GROUP_CONCAT to put all names together in the population order, then use only SUBSTRING_INDEX to catch the first one: -  
  GROUP_CONCAT (GROUP_CONCAT (population by population), ',', 1) Country group by continent  
  P> The bottom side of this method is that if there is a character used as a separator in a country name then the affair The result might be weird (default is a comma). 
 
 Adding a sequence number to the countries of the continent ordered by the census would also be possible to use user variables, and then hold the ones with those sequence numbers of 1.

Comments

Post a Comment