大家好，第一次海報學習（MS）SQL :) - 我希望你能提供幫助。我有以下查詢，但想以最高薪類別排在第一位。
如果我在子查詢中嘗試按薪水排序，我會被告知這是不允許的。
訊息 1033，級別 15，狀態 1，第 60 行
ORDER BY 子句在視圖、行內函式、派生表、子查詢和公用表運算式中無效，除非還指定了 TOP、OFFSET 或 FOR XML。如果我在外面嘗試它告訴我的查詢也不允許，因為薪水不在組中
Select test.rating, COUNT(rating) as total
FROM (
select ID, name, salaries,
CASE
WHEN salaries \> 12345 THEN 'paid well'
WHEN salaries \< 12345 THEN 'underpaid'
WHEN salaries = 12345 THEN 'average'
ELSE 'null'
END AS rating
from dupes
) test
GROUP by test.rating
從上面看，這是我當前的輸出，正是我想要的輸出，但首先要支付良好的類別，然后是平均水平，然后是低薪。誰能幫幫我嗎？
rating total
average 2
null 5
underpaid 4
paid well
1
uj5u.com熱心網友回復：
嘗試這個：
SELECT test.rating, COUNT(rating) AS total
FROM (
select ID, name, salaries,
CASE
WHEN salaries \> 12345 THEN 'paid well'
WHEN salaries \< 12345 THEN 'underpaid'
WHEN salaries = 12345 THEN 'average'
ELSE 'null'
END AS rating
from dupes
) test
GROUP by test.rating
ORDER BY
CASE
WHEN test.rating = 'Paid Well' THEN 1
WHEN test.rating = 'Average' THEN 2
WHEN test.rating = 'Underpaid' THEN 3
WHEN test.rating = 'null' THEN 99
ELSE 4
END
uj5u.com熱心網友回復：
只需在 GROUP BY 之后添加一個 ORDER BY（即，這將是最后執行的操作）：
ORDER BY
CASE WHEN test.rating='null' then 99
WHEN test.rating='Paid Well' then 1
WHEN test.rating='Average' then 2
WHEN test.rating='Underpaid' then 3
ELSE 4
END
uj5u.com熱心網友回復：
WITH detaildata AS (SELECT test.rating, COUNT(rating) AS total FROM ( select ID, name, salaries, CASE WHEN salaries \> 12345 THEN 'paid well' WHEN salaries \< 12345 THEN 'underpaid' WHEN salaries = 12345 THEN 'average' ELSE 'null' END AS rating from dupes ) test GROUP by test.rating ) SELECT rating, total FROM detaildata ORDER BY rating
轉載請註明出處，本文鏈接：https://www.uj5u.com/gongcheng/448977.html