MySQL Case/If/Then

问题:

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.

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.

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

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?

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....

Thanks

Andy

回答1:

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..

---

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

回答2:

Something like this should work using the CASE statement:

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.