MySQL MySQL案例/如果/那么

MySQL Case/If/Then

提问by user2232373

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.

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

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.

例如，如果库存水平 <0，则为空，如果库存水平在 0 到 1000 之间，则为红色，如果库存水平在 1000 到 5000 之间，则为黄色，如果库存水平在 5000 到 10000 之间，则为绿色，如果库存水平为 >10000，则绿色。

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.

所以这是我的例子。我有一个保存啤酒信息的啤酒表和一个保存股票价值的 BeerStock 表。

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

That would give me something like this:

那会给我这样的东西：

Beer1 Brewery1 Style1 5%, 3, 10, 1238

And I want

而且我要

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

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?

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

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!

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

Any advice/pointers appreciated....

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

Thanks

谢谢

Andy

安迪

回答by John Woo

I thin you mean that if the stock > 1000then another color not green.

我瘦你的意思是，如果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' 
        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..

---

---

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

另一件事，您还需要使用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' 
        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

回答by sgeddes

Something like this should work using the CASEstatement:

像这样的事情应该使用以下CASE语句：

SELECT Beer, Brewery, Style, ABV, SRM, SummedQty, 
    CASE 
       WHEN SummedQty >=0 AND SummedQty <= 1000 THEN ' RED'
       WHEN SummedQty >1000 AND SummedQty <= 5000 THEN ' YELLOW'
       WHEN SummedQty >5000 THEN ' GREEN'
       ELSE ''
    END yourcolor
FROM (
    SELECT Beer.Beer, Beer.Brewery, Beer.Style, Beer.ABV, Beer.Hops, Beer.SRM,
       Sum(BeerStock.Quantity) SummedQty
    FROM Beer
       INNER JOIN BeerStock  
          ON Beer.Beer = BeerStock.Beer
    ) t

Be careful using BETWEEN-- it may not return your desired results. I prefer using greater than and less than. I also converted your JOIN syntax to an INNER JOIN.

小心使用BETWEEN- 它可能不会返回您想要的结果。我更喜欢使用大于和小于。我还将您的 JOIN 语法转换为INNER JOIN.