如何使用CASE语句在SQL SERVER或ORACLE中转换度量单位

Question

在SQL SERVER或ORACLE中，有没有一种方法可以使用case语句来转换下面的度量单位？

select UoM, sum(Quantity) as 'QTY'
from mytable
group by UoM


UoM QTY
LBS 2.4
LBS 2
LBS 0.233
LBS 0.97
OZS 1.8
GMS 1236
LBS 120.459
LBS 59.1
LBS 252.82
LBS 175.23
LBS 3.42
LBS 455.4
LBS 57.6
LBS 146.8
LBS 117.78
LBS 197.92
LBS 40.245
GMS 9
LBS 15.78
LBS 12.6
LBS 125.1
LBS 42.3
LBS 1292.3

1磅= 16盎司(盎司)1磅= 453.5924克(GMS)

Answer 1

我想你可以这样做

SELECT sum( (case when UoM = 'LBS' then quantity
                  when UoM = 'OZS' then quantity/16
                  when UoM = 'GMS' then quantity/453.5924
                  else null
               end) ) weight_in_lbs,
       sum( (case when UoM = 'LBS' then quantity*16
                  when UoM = 'OZS' then quantity
                  when UoM = 'GMS' then quantity*16/453.5924
                  else null
               end) ) weight_in_ozs,
       sum( (case when UoM = 'LBS' then quantity*453.5924
                  when UoM = 'OZS' then quantity*453.5924/16
                  when UoM = 'GMS' then quantity
              end) ) weight_in_gms
  FROM myTable

Answer 2

你只是想要一个总和吗？或者是每个不同度量的小计？还是用3种不同的度量来表示相同的总数？

SELECT SUM(CASE UoM
       WHEN 'GMS' THEN Quantity
       WHEN 'LBS' THEN Quantity * 453.5924
       WHEN 'GMS' THEN Quantity * 453.5924 / 16
       END) AS TOTALGrams
FROM myTable

应该会给你以克为单位的总数。