按轉換為 varchar 的列排序

情況是，我想為前端圖表準備數據集。表中的原始數據包含

LAC | FLUID_MERIT|FA_BDATE   |GRP |FARM_FK
------------------------------------------
0   |234.56      |2020-01-01 |12  |10048
1   |234.56      |2009-01-01 |13  |10048
10  |234.56      |2020-01-01 |13  |10048
0   |234.56      |2020-01-01 |13  |10049
2   |234.56      |2009-01-01 |12  |10049
3   |234.56      |2009-01-01 |12  |10048
0   |234.56      |2020-01-01 |12  |10048

所以要準備我想要的平均數據集是按 LAC 分組的，旁邊有 LAC = 0 的條件，它必須按月份範圍分隔。下面的查詢是一個範例

select N'0 ( 01-06 M )' as YDS ,CAST(AVG(FLUID_MERIT) as decimal(10, 2)) AS DataSet 
from HerdAnalytics_tbl 
where GRP = 12 and LAC = 0 and DATEDIFF(MONTH, FA_BDATE , GETDATE()) <= 6 

union 

select N'0 ( 07-12 M )' as YDS ,CAST(AVG(FLUID_MERIT) as decimal(10, 2)) AS DataSet 
from HerdAnalytics_tbl 
where GRP = 12 and LAC = 0 and DATEDIFF(MONTH, FA_BDATE , GETDATE()) > 6 and DATEDIFF(MONTH, FA_BDATE , GETDATE()) <= 12 

union 

select N'0 ( 12 < M )' as YDS ,CAST(AVG(FLUID_MERIT) as decimal(10, 2)) AS DataSet 
from HerdAnalytics_tbl 
where GRP = 12 and LAC = 0 and DATEDIFF(MONTH, FA_BDATE , GETDATE()) > 12 

union 

select CAST(LAC as varchar),CAST(AVG(FLUID_MERIT) as decimal(10, 2)) AS DataSet 
from HerdAnalytics_tbl where GRP = 12 and LAC in (select LAC from HerdAnalytics_tbl 
where FARM_FK = 10048 and LAC != 0)  
group by LAC

union 

select 'TOTAL' as YDS ,CAST(AVG(FLUID_MERIT) as decimal(10, 2)) AS DataSet 
from HerdAnalytics_tbl 
where GRP = 12

當我使用聯合來合併它們時，我無法在第四個查詢中使用 order by，並且它還按 LAC 作為 varchar 數據排序，因此 lac 10 在 1 之後，因為它是一個 varchar，如下所示

YDS           | DataSet
------------------------
0 ( 01-06 M ) | 117.78
0 ( 07-12 M ) | 465.26
0 ( 12 < M )  | NULL
1             | 292.58
10            | -62.55
2             | 321.40
3             | 278.24
4             | 308.68
5             | 267.48
6             | 229.36
7             | 165.18
8             | 105.14
9             | 65.68
TOTAL         | 149.95

有什麼方法可以正確排序第四個查詢

該Order by子句是唯一保證順序的子句。

但是如果你真的想要後端，那麼你可以添加一些額外的列/屬性來使它更容易。

我添加了一個 grp = group level 和一個 subgroup grp_2 =group level 2。

對於0 ( 01-06 M ), 0 ( 07-12 M ), 0 ( 12 < M ),grp = 0

對於月度記錄,grp = 1

對於TOTAL,grp = 2

對於 grp_2，我們有：0 ( 01-06 M )grp_2 = 1, 0 ( 07-12 M )grp_2 = 2, 0 ( 12 < M )grp_2 = 3

對於月度記錄，grp_2 = LAC

對於TOTAL,grp_2 = 0

所以，我們按ORDER BY a.grp,a.grp_2

SELECT a.YDS,a.DataSet
FROM
(
   select N'0 ( 01-06 M )' as YDS ,CAST(AVG(FLUID_MERIT) as decimal(10, 2)) AS DataSet ,0 as grp,1 as grp_2
   from HerdAnalytics_tbl 
   where GRP = 12 and LAC = 0 and DATEDIFF(MONTH, FA_BDATE , GETDATE()) <= 6 

   union 

   select N'0 ( 07-12 M )' as YDS ,CAST(AVG(FLUID_MERIT) as decimal(10, 2)) AS DataSet,0 ,2
   from HerdAnalytics_tbl 
   where GRP = 12 and LAC = 0 and DATEDIFF(MONTH, FA_BDATE , GETDATE()) > 6 and DATEDIFF(MONTH, FA_BDATE , GETDATE()) <= 12 

   union 

   select N'0 ( 12 < M )' as YDS ,CAST(AVG(FLUID_MERIT) as decimal(10, 2)) AS DataSet ,0,3
   from HerdAnalytics_tbl 
   where GRP = 12 and LAC = 0 and DATEDIFF(MONTH, FA_BDATE , GETDATE()) > 12 

   union 

   select CAST(LAC as varchar),CAST(AVG(FLUID_MERIT) as decimal(10, 2)) AS DataSet ,1,Lac
   from HerdAnalytics_tbl 
   where GRP = 12 
       and LAC in (select LAC from HerdAnalytics_tbl where FARM_FK = 10048 and LAC != 0)  
   group by LAC

   union 

   select 'TOTAL' as YDS ,CAST(AVG(FLUID_MERIT) as decimal(10, 2)) AS DataSet ,2,0
   from HerdAnalytics_tbl 
   where GRP = 12
)as a
ORDER BY a.grp,a.grp_2

輸出：

YDS             DataSet
0 ( 01-06 M )   NULL
0 ( 07-12 M )   NULL
0 ( 12 < M )    234.56
3               234.56
4               234.56
10              234.56
TOTAL           234.56

小提琴手

引用自：https://dba.stackexchange.com/questions/285858