MySQL Case/If/Then [英] MySQL Case/If/Then

问题描述

我正在尝试在MySQL中构建查询，在该查询中我具有某种数字的库存水平，并且根据该库存水平是什么，我希望它返回另一个值，该值将是颜色代码.

I am trying to build a query in MySQL, where I have a numeric stock level of something and depending on what that stock level is, I want it to return another value that would be a colour code.

例如如果库存水平小于0，则为空；如果库存水平在0至1000之间，则为红色；如果库存水平在1000至5000之间，则为黄色；如果库存水平在5000至10000之间，则为绿色；如果库存水平大于10000，则为空.绿色.

E.g. If the stock level is <0 then EMPTY if stock level is Between 0 and 1000 then RED, If stock level is Between 1000 and 5000 then YELLOW, If Stock level is Between 5000 and 10000 then GREEN, If Stock level is >10000 then GREEN.

所以这是我的例子.我有一个Beer表，其中包含Beer信息，而BeerStock表中，包含Stock Values.

So here is my example. I have a Beer table that holds info on the Beer and a BeerStock table that holds the Stock Values.

SELECT Beer.Beer, Beer.Brewery, Beer.Style, Beer.ABV, Beer.Hops, Beer.SRM,
Sum(BeerStock.Quantity)
FROM Beer, BeerStock
Where Beer.Beer = BeerStock.Beer

那会给我这样的东西:

Beer1 Brewery1 Style1 5%, 3, 10, 1238

我想要

Beer1 Brewery1 Style1 5%, 3, 10, 1238 YELLOW

我似乎根本无法弄清楚，我应该使用CASE还是可以使用IF/THEN类型的东西?

I can't seem to figure this out at all, should I be using a CASE or can I use an IF/THEN type of thing?

我的SQL技能很生疏，通常我会使用Access并弄乱自动生成的SQL，而不是从头开始写...我可以应付MySQL的基础知识，但不能应付这类事情！

My SQL skills are rusty, normally I use Access and mess with the auto-generated SQL rather than writing from scratch...I can cope with the basics of MySQL but not this kind of stuff!

任何建议/指针都表示赞赏....

Any advice/pointers appreciated....

谢谢

安迪

推荐答案

我的意思是，如果stock > 1000则使用另一种颜色not green.

I thin you mean that if the stock > 1000 then another color 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' 
        END
FROM    Beer
        INNER JOIN BeerStock
            ON Beer.Beer = BeerStock.Beer
-- Where    ..other conditions..

但是如果你真的是那样，

but if you really mean that,

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'
        END
FROM    Beer
        INNER JOIN BeerStock
            ON Beer.Beer = BeerStock.Beer
-- Where    ..other conditions..

---

还有一件事情，您还需要使用GROUP BY子句，否则即使您有不同的记录，您也只会返回一条记录，

---

One more thing, You also need to use GROUP BY clause or else you will be returning only one record even if you have different records,

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' 
        END
FROM    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

这篇关于MySQL Case/If/Then的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！