SELECT id, max(CASE WHEN month = 'Jan' THEN revenue ELSE NULL END) AS 'Jan_Revenue', max(CASE WHEN month = 'Feb' THEN revenue ELSE NULL END) AS 'Feb_Revenue', max(CASE WHEN month = 'Mar' THEN revenue ELSE NULL END) AS 'Mar_Revenue', max(CASE WHEN month = 'Apr' THEN revenue ELSE NULL END) AS 'Apr_Revenue', max(CASE WHEN month = 'May' THEN revenue ELSE NULL END) AS 'May_Revenue', max(CASE WHEN month = 'Jun' THEN revenue ELSE NULL END) AS 'Jun_Revenue', max(CASE WHEN month = 'Jul' THEN revenue ELSE NULL END) AS 'Jul_Revenue', max(CASE WHEN month = 'Aug' THEN revenue ELSE NULL END) AS 'Aug_Revenue', max(CASE WHEN month = 'Sep' THEN revenue ELSE NULL END) AS 'Sep_Revenue', max(CASE WHEN month = 'Oct' THEN revenue ELSE NULL END) AS 'Oct_Revenue', max(CASE WHEN month = 'Nov' THEN revenue ELSE NULL END) AS 'Nov_Revenue', max(CASE WHEN month = 'Dec' THEN revenue ELSE NULL END) AS 'Dec_Revenue', FROM Department GROUP BY id
时间: 2023-05-17 08:05:17 浏览: 45
这是一个 SQL 查询语句,目的是从 Department 表中获取每个部门的每个月份的收入数据,并将其转换为每个部门的一行记录。每个月份的收入数据使用 CASE WHEN 语句进行筛选,最终使用 GROUP BY 语句将每个部门的数据进行分组。
相关问题
sql case when 行转列
在SQL中,可以使用CASE WHEN语句实现行转列的功能。通过CASE WHEN语句,可以根据条件将一组数据显示在一行上。
下面是一个示例,演示如何使用CASE WHEN语句实现行转列:
假设有一个表格名为sales,包含以下字段:部门id(department_id)、月份(month)和收入(revenue)。
```sql
SELECT department_id,
MAX(CASE WHEN month = 'January' THEN revenue END) AS January,
MAX(CASE WHEN month = 'February' THEN revenue END) AS February,
MAX(CASE WHEN month = 'March' THEN revenue END) AS March,
MAX(CASE WHEN month = 'April' THEN revenue END) AS April,
MAX(CASE WHEN month = 'May' THEN revenue END) AS May,
MAX(CASE WHEN month = 'June' THEN revenue END) AS June,
MAX(CASE WHEN month = 'July' THEN revenue END) AS July,
MAX(CASE WHEN month = 'August' THEN revenue END) AS August,
MAX(CASE WHEN month = 'September' THEN revenue END) AS September,
MAX(CASE WHEN month = 'October' THEN revenue END) AS October,
MAX(CASE WHEN month = 'November' THEN revenue END) AS November,
MAX(CASE WHEN month = 'December' THEN revenue END) AS December
FROM sales
GROUP BY department_id;
```
上述查询将根据部门id将每个月的收入转换为对应的列,并将结果按部门id进行分组。
select case when else 在clickhouse中的使用
在ClickHouse中,SELECT CASE WHEN ELSE语句用于根据条件对结果进行分组或分类。它的基本语法如下所示:
```
SELECT
CASE
WHEN condition_1 THEN result_1
WHEN condition_2 THEN result_2
...
ELSE default_result
END
FROM table_name
```
其中,condition_n是一个条件表达式,result_n是与该条件匹配时返回的值,default_result是当所有条件都不匹配时返回的默认值。
例如,以下查询将根据销售额将订单分为高、中、低三个类别:
```
SELECT
order_id,
CASE
WHEN revenue > 10000 THEN 'High'
WHEN revenue > 5000 THEN 'Medium'
ELSE 'Low'
END AS sales_category
FROM orders
```
在上面的查询中,如果订单的收入大于10000,则将其分类为“高”,如果在5000到10000之间,则将其分类为“中”,否则将其分类为“低”。sales_category列包含每个订单的分类结果。