菜鳥小白，想問一下為什么執行結果沒有按照SQL陳述句的來執行
uj5u.com熱心網友回復：
你這得動態執行，類似下面的代碼，不過MYSQL不是很清楚。但是，你這邏輯好像有些問題，是不是得不同ID的各自進行查詢再合并。
DECLARE @SQL
SET @SQL='SELECT ..... ORDER BY '+CASE WHEN ID=1 THEN 'STOCKNO DESC' ELSE 'STOCKNO' END
EXEC(@SQL)
uj5u.com熱心網友回復：
這是搞笑來的吧，同一個欄位在一個陳述句里面給定不同的排序方式？uj5u.com熱心網友回復：
提供一種參考寫法，不一定符合你的預期結果。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 *
FROM TMP
ORDER BY CASE
WHEN TMP.P_ID = 1 THEN
TMP.P_NO
ELSE
null
END desc,
CASE
WHEN TMP.P_ID = 2 THEN
TMP.P_NO
ELSE
null
END asc;
uj5u.com熱心網友回復：
select id
from pub_products
order by (case when id<2000 then id else 0-id end)
數字列可以用這種方式排列，但字符列我也無能為力
uj5u.com熱心網友回復：
用字符替換后轉型別進行排序;with t as (
select 1 as id,1 as rev,'' as content,'z01' as stockNO
union all select 2,1,'','z02'
union all select 1,2,'','z03'
union all select 2,2,'','z04'
union all select 1,3,'','z05'
)
select * from t
order by (case when id=1 then 0-convert(int,replace(stockNO,'z','')) else convert(int,replace(stockNO,'z','')) end)
或者先排序，得到排序序號后，再次排序
;with t as (
select 1 as id,1 as rev,'' as content,'z01' as stockNO
union all select 2,1,'','z02'
union all select 1,2,'','z03'
union all select 2,2,'','z04'
union all select 1,3,'','z05'
)
select * from (
select *,row_number() over(order by stockNO) as rowid from t
) a
order by (case when id=1 then 0-rowid else rowid end)
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/90566.html
標籤：基礎類