1.在Oracle中,将一行数据转为一列,其对应的字段名为另一列。
有名为"table_a"的表,表结构如下:
Field_A | Field_B | Field_C |
---|---|---|
Value_1 | Value_2 | Value_3 |
SELECT FieldName,Value
FROM (
SELECT Field_A,Field_B,Field_C
FROM table_a
)
UNPIVOT (
Value FOR FieldName IN (Field_A,Field_B,Field_C)
)
2.在Oracle中,使用CASE WHEN语句与SUM函数结合实现条件求和,或者与COUNT()函数结合实现条件计数,与AVG()函数结合,与MAX()函数结合,与MIN函数结合。
SELECT
category,
SUM(CASE WHEN condition THEN value ELSE 0 END) AS sum_value
FROM table_name
GROUP BY category
SELECT
column1,
COUNT(CASE WHEN condition THEN column2 END) AS count_value
FROM table_name
GROUP BY column1
SELECT
category,
AVG(CASE WHEN condition THEN value ELSE null END) AS avg_value
FROM table_name
GROUP BY category
SELECT
category,
MAX(CASE WHEN condition THEN value ELSE null END) AS max_value
FROM table_name
GROUP BY category
SELECT
category,
MIN(CASE WHEN condition THEN value ELSE null END) AS min_value
FROM table_name
GROUP BY category
3.在 Oracle 中,可以使用 PIVOT 或者条件聚合函数(CASE WHEN)来实现行转列的操作。
(1)使用PIVOT
SELECT *
FROM (
SELECT category, value
FROM table_name
)
PIVOT (
MAX(value)
FOR category IN ('A', 'B', 'C')
)
(2)使用条件聚合函数(CASE WHEN)
SELECT
MAX(CASE WHEN category = 'A' THEN value END) AS A,
MAX(CASE WHEN category = 'B' THEN value END) AS B,
MAX(CASE WHEN category = 'C' THEN value END) AS C
FROM table_name
4.在Oracle中,使用SUM(column3) OVER (PARTITION BY column1 ORDER BY column2)实现计算分组内每行数据的累积求和值。创建表名为sales的表。
column1 | column2 | column3 |
---|---|---|
A | 1 | 10 |
A | 2 | 20 |
A | 3 | 30 |
B | 1 | 5 |
B | 2 | 15 |
B | 3 | 25 |
使用如下SQL,将得到相应的结果。
SELECT column1, column2, column3,
SUM(column3) OVER (PARTITION BY column1 ORDER BY column2) AS cumulative_sum
FROM sales
column1 | column2 | column3 | cumulative_sum |
---|---|---|---|
A | 1 | 10 | 10 |
A | 2 | 20 | 30 |
A | 3 | 30 | 60 |
B | 1 | 5 | 5 |
B | 2 | 15 | 20 |
B | 3 | 25 | 45 |
5.在Oracle中,使用SUM(column2) OVER (ORDER BY column1) 实现计算每行数据的累积求和值。
SELECT column1, column2,
SUM(column2) OVER (ORDER BY column1) AS cumulative_sum
FROM sales
6.在Oracle中,使用ROW_NUMBER() OVER (PARTITION BY column1 ORDER BY column2)实现组内排序。
SELECT column1, column2,
ROW_NUMBER() OVER (PARTITION BY column1 ORDER BY column2) AS row_num
FROM sales