sql - 使用联合按子类别排序

Question

现在我按 4 个类别排序并显示它们。第一类，农业，需要先将拖拉机排序在顶部，然后才是其余的农业设备。拖拉机的类别 ID 为 1108、1109、1110、1111。

这是现在正在使用的查询:

SELECT category, manufacturer, model, serialnumber, stocknumber, owningbusinessunit, statecode, picturessortorder, yeartext, agriculturalequipmentid, listprice, locationidname, hours, detaileddescription, briefdescription
FROM AgriculturalEquipment 
ORDER BY category, manufacturer, model DESC, yeartext DESC

这是我目前在 ag 类别中首先展示拖拉机的内容:

SELECT TOP 100000 category, categoryid, manufacturer, model, serialnumber, stocknumber, owningbusinessunit, statecode, picturessortorder, yeartext, agriculturalequipmentid, listprice, locationidname, hours, detaileddescription, briefdescription FROM agriculturalequipment WHERE categoryid IN
(SELECT CategoryID FROM (
SELECT top 100000 CategoryID
FROM AgriculturalEquipment AS AgriculturalEquipment
Where CategoryID in ('1108','1109','1110','1111')
ORDER BY categoryid, manufacturer, model DESC, yeartext DESC) A Group by a.categoryid) 

UNION

SELECT TOP 100000 category, categoryid, manufacturer, model, serialnumber, stocknumber, owningbusinessunit, statecode, picturessortorder, yeartext, sads_agriculturalequipmentid, listprice, locationidname, hours, detaileddescription, briefdescription FROM agriculturalequipment WHEREcategoryid IN
(SELECT CategoryID FROM(
SELECT top 100000 CategoryID
FROM AgriculturalEquipment AS AgriculturalEquipment 
WHERE CategoryID not in ('1108','1109','1110','1111')
ORDER BY category, manufacturer, model DESC, yeartext DESC) B GROUP BY categoryid)

这是我得到的结果。类别应该排序并从 1108 到 1111。我在这里做错了什么？

  Category                     catid   manufact

Tractors - 100 HP to 174 HP     1109    AGRI-POWER
Tractors - 100 HP to 174 HP     1109    CASE IH
Tractors - 100 HP to 174 HP     1109    JOHN DEERE
Tractors - 100 HP to 174 HP     1109    NEW HOLLAND
Tractors - 175 HP Or Greater    1108    AGCO ALLIS
Tractors - 175 HP Or Greater    1108    JOHN DEERE
Tractors - Less than 40 HP      1111    AGTRAXX
Tractors - Less than 40 HP      1111    FORDSON
Tractors - 100 HP to 174 HP     1109    INTERNATIONAL
Tractors - 100 HP to 174 HP     1109    JOHN DEERE
Tractors - 100 HP to 174 HP     1109    NEW HOLLAND
Tractors - 175 HP Or Greater    1108    BELL
Tractors - 175 HP Or Greater    1108    DEUTZ
Tractors - 40 HP to 99 HP       1110    AGRI TRACTOR
Tractors - Less than 40 HP      1111    AGTRAXX
Tractors - Less than 40 HP      1111    ALLMAND BROS
Tractors - 100 HP to 174 HP     1109    AGCO WHITE
Tractors - 100 HP to 174 HP     1109    NEW HOLLAND
Tractors - 100 HP to 174 HP     1109    NEW HOLLAND
Tractors - 100 HP to 174 HP     1109    NEW HOLLAND
Tractors - 175 HP Or Greater    1108    WHITE
Tractors - 40 HP to 99 HP       1110    AGRI TRACTOR

Answer 1

许多方法之一:

SELECT *
FROM AgriculturalEquipment 
ORDER BY 
CASE 
  WHEN CategoryID in ('1108','1109','1110','1111') THEN CategoryID
  ELSE CategoryID + 2000 -- 'penalize' other categories
END,
category, manufacturer, model DESC, yeartext DESC