SQL的CASE語句通過條件表達(dá)式實(shí)現(xiàn)if-then邏輯，用于在查詢中直接進(jìn)行數(shù)據(jù)分類或轉(zhuǎn)換。1. CASE以CASE開頭，包含多個(gè)WHEN條件和可選的ELSE處理，默認(rèn)以END結(jié)束，并常與AS定義新列；2. 可結(jié)合聚合函數(shù)如SUM、COUNT使用，實(shí)現(xiàn)在單個(gè)查詢中統(tǒng)計(jì)多個(gè)指標(biāo)；3. 使用時(shí)需注意條件順序、ELSE處理、邊界測(cè)試及別名設(shè)置。例如，可用CASE對(duì)訂單金額分段并計(jì)數(shù)，增強(qiáng)報(bào)表生成靈活性。

The CASE statement in SQL is a conditional expression that lets you perform if-then logic directly in your queries. It’s super useful when you want to create new columns or categories based on existing data, without having to process the results outside of SQL.

Basic Structure of CASE

At its core, a CASE statement starts with the keyword CASE, followed by one or more WHEN conditions, and ends with END. You can optionally add an ELSE clause to handle any unmatched cases.

Here’s a simple skeleton:

SELECT 
  column1,
  CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ELSE default_result
  END AS new_column_name
FROM table_name;

This structure makes it easy to translate logic into SQL. For example, say you have a list of order totals and you want to label them as “Low”, “Medium”, or “High” based on their amount:

SELECT 
  order_id,
  total_amount,
  CASE
    WHEN total_amount < 100 THEN 'Low'
    WHEN total_amount BETWEEN 100 AND 500 THEN 'Medium'
    ELSE 'High'
  END AS category
FROM orders;

That’s how you start categorizing values right inside your query.

Using CASE with Aggregations

One of the most powerful uses of CASE is combining it with aggregate functions like SUM(), COUNT(), or AVG().

For instance, let's say you want to count how many orders fall into each category (“Low”, “Medium”, “High”) from the previous example. You could write:

SELECT
  COUNT(*) FILTER (
    WHERE total_amount < 100
  ) AS low_orders,
  COUNT(*) FILTER (
    WHERE total_amount BETWEEN 100 AND 500
  ) AS medium_orders,
  COUNT(*) FILTER (
    WHERE total_amount >= 500
  ) AS high_orders
FROM orders;

Or using CASE:

SELECT
  SUM(CASE WHEN total_amount < 100 THEN 1 ELSE 0 END) AS low_orders,
  SUM(CASE WHEN total_amount BETWEEN 100 AND 500 THEN 1 ELSE 0 END) AS medium_orders,
  SUM(CASE WHEN total_amount >= 500 THEN 1 ELSE 0 END) AS high_orders
FROM orders;

This works across most SQL dialects, even those that don’t support FILTER.

It’s especially handy for building reports or dashboards where you need multiple metrics in one go.

Common Mistakes and Tips

• Order matters
  SQL processes the WHEN clauses in order. Once a condition matches, it stops checking the rest. So be careful with overlapping conditions — put the most specific ones first.

• Use ELSE wisely
  If you leave out the ELSE clause, any unmatched rows will return NULL. That might be okay, but sometimes it’s better to explicitly assign a value like ‘Unknown’ or 0 depending on context.

• Alias the result
  Always name your CASE-derived columns using AS, so it’s clear what they represent in the output.

• Test edge cases
  Especially when dealing with ranges (like dates or numbers), test the boundaries. Does your logic include equal signs? Should it?

Also, remember that CASE can be used not just in SELECT statements, but also in ORDER BY, WHERE, and HAVING clauses — though that’s less common.

So yeah, CASE is basically SQL’s version of if-else. It gives you flexibility when transforming or summarizing data, and once you get comfortable with it, you’ll find yourself reaching for it often.

基本上就這些。

以上是SQL中的情況是什麼？您如何使用它？的詳細(xì)內(nèi)容。更多資訊請(qǐng)關(guān)注PHP中文網(wǎng)其他相關(guān)文章！