我瘦你的意思是，如果
stock > 1000再那么另一种颜色
not green。
SELECt Beer.Beer, Beer.Brewery, Beer.Style, Beer.ABV, Beer.Hops, Beer.SRM, Sum(BeerStock.Quantity) totalQuantity, CASE WHEN Sum(BeerStock.Quantity) < 0 THEN '' WHEN Sum(BeerStock.Quantity) BETWEEN 0 AND 999 THEN 'Red' WHEN Sum(BeerStock.Quantity) BETWEEN 1000 AND 4999 THEN 'Yellow' WHEN Sum(BeerStock.Quantity) BETWEEN 5000 AND 9999 THEN 'GREEN' WHEN Sum(BeerStock.Quantity) >= 10000 THEN 'Another Color' ENDFROM Beer INNER JOIN BeerStock ON Beer.Beer = BeerStock.Beer-- Where ..other conditions..
但是如果你真的是那个意思，
SELECt Beer.Beer, Beer.Brewery, Beer.Style, Beer.ABV, Beer.Hops, Beer.SRM, Sum(BeerStock.Quantity) totalQuantity, CASE WHEN Sum(BeerStock.Quantity) < 0 THEN '' WHEN Sum(BeerStock.Quantity) BETWEEN 0 AND 999 THEN 'Red' WHEN Sum(BeerStock.Quantity) BETWEEN 1000 AND 4999 THEN 'Yellow' WHEN Sum(BeerStock.Quantity) >= 5000 THEN 'GREEN' ENDFROM Beer INNER JOIN BeerStock ON Beer.Beer = BeerStock.Beer-- Where ..other conditions..
还有一件事，您还需要使用
GROUP BY子句，否则即使您有不同的记录，您也将只返回一条记录，
SELECt Beer.Beer, Beer.Brewery, Beer.Style, Beer.ABV, Beer.Hops, Beer.SRM, Sum(BeerStock.Quantity) totalQuantity, CASE WHEN Sum(BeerStock.Quantity) < 0 THEN '' WHEN Sum(BeerStock.Quantity) BETWEEN 0 AND 999 THEN 'Red' WHEN Sum(BeerStock.Quantity) BETWEEN 1000 AND 4999 THEN 'Yellow' WHEN Sum(BeerStock.Quantity) BETWEEN 5000 AND 9999 THEN 'GREEN' WHEN Sum(BeerStock.Quantity) >= 10000 THEN 'Another Color' ENDFROM Beer INNER JOIN BeerStock ON Beer.Beer = BeerStock.Beer-- Where ..other conditions..GROUP BY Beer.Beer, Beer.Brewery, Beer.Style, Beer.ABV, Beer.Hops, Beer.SRM
