SELECT 
@ rownum1, 
@ rownum1:=@ rownum1 + 1 AS r_n, 
A.t rade_date as date_a, 
Ab. Trade_date as date_ab 
The FROM 
Stock_daily_data a, 
R: (SELECT @ rownum1=0), 
(
SELECT 
* 
The FROM 
Stock_daily_data 
WHERE 
Stock_code='300517 SZ' 
AND trade_date BETWEEN '2018-05-18' 
AND '2018-05-18 s' 
) ab 
WHERE 
A.s tock_code='300519 SZ' 
AND a.t rade_date & lt; Ab. Trade_date 
AND a.t rade_date BETWEEN '2018-05-10' 
AND '2018-05-14 s' 
The ORDER BY 
Ab. Trade_date DESC, a.t rade_date DESC 

Results:

The above r_n can according to the ascending order from big to small

But if the FROM inside of the inside of the subquery ab trade_date values range is more than a day, such as BETWEEN '2018-05-17' AND '2018-05-18', then the result will become is

The above r_n only since the childhood to ascending

Excuse me,, why is there such a question? Thank you for your comment!

CodePudding user response:

Results 1, first of all, these two statements are correct, ranking behind only guarantee the order by field
2, as to appear inconsistent r_n, because both perform optimization strategy is different, the former is according to the order by the order value, the latter is the value after the sorting, MYSQL will think in the second case faster in this way, you can add the explain to verify, in front of the as the optimization strategy is not the same, and in the future possible N kind of @ runnum method, so you @ rownum way to record the order value is not reliable,

CodePudding user response:

reference 1st floor trainee response:

1, first of all, the results of two statements are correct, ranking behind only guarantee the order by field
2, as to appear inconsistent r_n, because both perform optimization strategy is different, the former is according to the order by the order value, the latter is the value after the sorting, MYSQL will think in the second case faster in this way, you can add the explain to verify, in front of the as the optimization strategy is not the same, and in the future possible N kind of @ runnum method, so you @ rownum way to record the order value is not reliable,

Thank you,
I added the EXPLAIN according to what you have said to test, the results are as follows:
Scope is the single day



value range is two days

Is different, but I don't understand this?

In addition, if @ rownum is used to record the order values are not reliable, so what should use to record will be reliable? For further instruction, thank you!

CodePudding user response:

Then removed the rownum parcel layer
SELECT @ rownum1, @ rownum1:=@ rownum1 + 1 AS r_n, TMP. * the FROM (SELECT.. The FROM.. WHERE.. The ORDER BY.. ) as TMP

CodePudding user response:

You are the cartesian join