Ask a questions about the order by and case the when, for a great god-CodePudding

Novice small white, want to ask why didn't the results according to the SQL statement to perform

CodePudding user response:

You have this dynamic execution, similar to the following code, but the MYSQL is not very clear, however, this logic seems to be some problem, you have to different ID of each query to merge,

 
DECLARE @ SQL 

The SET @ SQL='SELECT... The ORDER BY '+ CASE WHEN ID=1 THEN STOCKNO DESC' ELSE 'STOCKNO' END 


The EXEC (@ SQL)

CodePudding user response:

It is funny to it, the same field in a statement given the different sort of way?

CodePudding user response:

To provide a reference method and may not meet your expectations as a result,
With TMP as
(select 1 as p_id, 1 as p_rev, 'Z01 as p_no from dual
Union all
Select 2 as p_id, 1 as p_rev, 'Z02 as p_no from dual
Union all
Select 1 as p_id, 2 as p_rev, 'Z03 as p_no from dual
Union all
Select 2 as p_id, 2 as p_rev, 'Z05 as p_no from dual
Union all
Select 1 as p_id, 3 as p_rev, 'Z04 as p_no from dual)
SELECT *
The FROM TMP
The ORDER BY CASE
The WHEN TMP. P_ID=1 THEN
TMP. P_NO
The ELSE
Null
END desc,
CASE
The WHEN TMP. P_ID=2 THEN
TMP. P_NO
The ELSE
Null
END asc.

CodePudding user response:

 select idThe from pub_products 
The order by (case when id

Digital column can be arranged in this way, but the character I can't help it

CodePudding user response:

After replace with character turn type sorting

; As with t (
Select 1 as id, 1 as rev, 'as the content,' z01 as stockNO 
Union all select 2, 1, ' 'and' z02 '
Union all select 1, 2, ' 'and' z03 '
Union all select, 2, 2 ', 'z04' 
Union all select 1, 3, ' 'and' z05 '
) 
Select * from t 
The order by (case when id=1 then 0 to convert (int, replace (stockNO, 'z', ')) else convert (int, replace (stockNO, 'z', ')) end)

Or sorting first, after get order number, again order

; As with t (
Select 1 as id, 1 as rev, 'as the content,' z01 as stockNO 
Union all select 2, 1, ' 'and' z02 '
Union all select 1, 2, ' 'and' z03 '
Union all select, 2, 2 ', 'z04' 
Union all select 1, 3, ' 'and' z05 '
) 
Select * from (
Select *, row_number () over (order by stockNO) as the rowid from t 
) a 
The order by (case when id=1 then 0 - rowid else rowid end)