A delivery card, first take the delivery of April to may shipments in the table top five mode of transportation, and in the five transportation means in each mode of transportation before shipment of five countries, can take to the relevant data at a time, please

CodePudding user response:

Can you put the table structure and the sample data to the part out? My train of thought is to use a subquery, first by the subquery, collect shipments in the shipping way, find the top five, and then the associated outside the original table, collect shipments, according to the shipping method and the country again and get to the top five countries; Pseudo code is as follows:
Select
The delivery way
,
, the sum (shipments)
The from table1 a
Inner join (select
The delivery way
, the sum (shipments)
The from table1 a
Where the time between April and may
Group by shipping method
The order by the sum (shipments) desc
Limit 5) b
Way=b. delivery on a. delivery way
Way=b. delivery where a. delivery way
And where the time between April and may
Group by shipping method, the state
The order by the sum (shipments) desc


Is wrong, this method can take to each shipping method delivery of shipments of the order, but can't get to the top five countries

CodePudding user response:

Wanted to think, want to a the most stupid method, can't think of a better way, the others if there is a better way, still hope glad:
Select
The delivery way
,
, shipments _sum
, the if (@ objno=a. countries,
@ rank:=@ rank + 1,
@ rank:=1) as row_number
, @ objno=a. countries
The from
(select
The delivery way
,
, the sum (shipments) as shipments _sum
The from table1 a
Inner join (select
The delivery way
, the sum (shipments)
The from table1 a
Where the time between April and may
Group by shipping method
The order by the sum (shipments) desc
Limit 5) b
Way=b. delivery on a. delivery way
Way=b. delivery where a. delivery way
And where the time between April and may
Group by shipping method, the state
The order by shipping method
, the state, the sum (shipments) desc
) a
, (@ objno:=NULL, @ rank:=0) b
Where row_number & lt;=5


Because mysql itself does not support such as oracl support over (partition by) function, so write up a little bit difficult

CodePudding user response:

reference

refer to the following question method, help to improve the probability of the question to get accurate answer

1. Provide test data sample (do not map to the text is posted, so others can be tested directly copied to your own EXCEL)
2. Based on these examples data correct results are you looking for?
3. Describe how the results, the algorithm, as far as possible need not "jargon".
4. For example, when you need to fully don't other people's methods, the results say "I'm sorry, there is a"
5. One-time finish problem description, don't points several section, or section of the words in the QQ more easily by other speech separation,

,