【重学 MySQL】八十二、深入探索 CASE 语句的应用

CASE value_expression
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    ...
    ELSE resultN
END

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE resultN
END

SELECT ProductName, Sales,
    CASE
        WHEN Sales < 100 THEN 'Low'
        WHEN Sales BETWEEN 100 AND 500 THEN 'Medium'
        ELSE 'High'
    END AS SalesCategory
FROM SalesData;

SELECT * FROM Products
ORDER BY CASE
    WHEN UserPreference = 'Price' THEN Price
    WHEN UserPreference = 'Rating' THEN Rating
END;

SELECT COUNT(*) AS count, product_type
FROM video_view
WHERE product_type != 0
    AND create_time BETWEEN '2024-01-27 11:02:10' AND '2024-03-27 11:02:10'
    AND (CASE
        WHEN view_count LIKE '%万%' THEN CAST(REPLACE(view_count, '万', '') AS DECIMAL(10, 2)) * 10000
        ELSE CAST(view_count AS DECIMAL(10, 2))
    END) > 10000
GROUP BY product_type
ORDER BY count DESC;

SELECT employee_id, job_title, 
    CASE job_title 
        WHEN 'Engineer' THEN 'IT Department'
        WHEN 'Manager' THEN 'Management'
        ELSE 'Other Department' 
    END AS department

SELECT * FROM products
WHERE CASE 
    WHEN product_type = 'Electronics' THEN category_id = 1
    WHEN product_type = 'Clothing' THEN category_id = 2
    -- 可以添加更多条件
END

SELECT employee_id, job_title, salary 
FROM employees
ORDER BY CASE 
    WHEN job_title = 'Manager' THEN 1
    -- 可以添加更多条件
END, salary DESC; -- 如果job_title不是'Manager'，则按salary降序排序