Question

SQL SERVER或ORACLE中是否有一种方法可以使用事例语句转换下面的度量单位？

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 pound = 16 ounces (OZS) 1 pound = 453.5924 gram (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

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

友情链接