插入多条例数据
-- INSERT INTO sales_tab
-- SELECT TRUNC(DBMS_RANDOM.value(2010,2012)) AS year_id,
-- TRUNC(DBMS_RANDOM.value(1,12)) AS month_id,
-- TRUNC(DBMS_RANDOM.value(1,31)) AS day_id,
-- ROUND(DBMS_RANDOM.value(1,100),2) AS sales_value
-- FROM dual
-- CONNECT BY level <= 1000;
集合操作: 对结果集的操作,注意,只有字段数相同才能做集合操作
并集(UNION/UNION ALL),交集(INTERSECT)、差集(MINUS)
SELECT statement1
[UNION | UNION ALL | INTERSECT | MINUS]
SELECT statement2;
SELECT * FROM emp
WHERE ENAME='SMITH'
UNION
SELECT * FROM emp
WHERE ename='WARD'
高级聚合(分组)函数:
作用在GROUP BY 中,每个高级分组函数都有一套分组策略
1、ROLLUP()
不用聚合函数:
--查看每天的营业额
SELECT year_id,month_id,day_id,SUM(sales_value) FROM sales_tab
GROUP BY year_id,month_id,day_id
ORDER BY year_id,month_id,day_id
--查看每月营业额
SELECT year_id,month_id,SUM(sales_value) FROM sales_tab
GROUP BY year_id,month_id
ORDER BY year_id,month_id
--查看每年的营业额
SELECT year_id,SUM(sales_value) FROM sales_tab
GROUP BY year_id
ORDER BY year_id
ROLLUP() ,分组原则,参数逐次递减,一直到所有的参数都不要,每一种分组都要统计一次结果,并且并在
一个结果集显示。(参数一定是逐级递减,不然不能用)
GROUP BY ROLLUP(a,b,c)
等价于:
GROUP BY a,b,c
UNION ALL
GROUP BY a,b
UNION ALL
GROUP BY a
UNION ALL
全表
--
--查看上面的三种情况在一个表中
SELECT year_id,month_id,day_id,SUM(sales_value)
FROM sales_tab
GROUP BY ROLLUP(year_id,month_id,day_id)
参数逐级递减,每天,每月,每年,总共的营业额
往下参数递减时,都是补的NULL
2010 1 1 175.38
2010 1 2 37.51
2010 1 4 56.28
2010 1 5 64.44
2010 1 6 158.19
2010 1 7 112.64
2010 1 9 80.51
2010 1 10 204.83
2010 1 13 73.26
2010 1 14 42.49
2010 1 15 67.38
2010 1 17 66.30
2010 1 19 20.52
2010 1 20 22.42
2010 1 23 160.03
2010 1 25 74.56
2010 1 26 110.13
2010 1 27 14.74
2010 1 28 29.10
2010 1 29 45.24
2010 1 30 52.67
2010 1 1668.62
2010 2 1 252.80
2010 2 2 30.55
2010 2 3 115.40
2010 2 4 105.04
2010 2 5 92.20
2010 2 6 33.66
2010 2 7 48
2010 2 8 53.27
2010 2 9 5.05
2010 2 10 81.71
2010 2 11 100.64
2010 2 12 86.46
2010 2 14 95.31
2010 2 15 8.89
2010 2 16 69.83
2010 2 17 69.67
2010 2 18 63.08
2010 2 19 214.37
2010 2 20 37.67
2010 2 21 89.26
2010 2 22 81.35
2010 2 23 172.15
2010 2 24 180.51
2010 2 25 61.68
2010 2 27 152.49
2010 2 28 193.21
2010 2 29 45.64
2010 2 30 112.66
2010 2 2652.55
2010 3 2 93.43
2010 3 4 226.02
2010 3 5 29.92
2010 3 6 51.59
2010 3 7 1.04
2010 3 8 79.30
2010 3 9 186.17
2010 3 10 88.94
2010 3 13 154.35
2010 3 15 96.78
2010 3 17 115.33
2010 3 18 213.68
2010 3 19 93.57
2010 3 20 68.13
2010 3 22 145.34
2010 3 23 126.59
2010 3 24 6.72
2010 3 25 98.25
2010 3 26 96.08
2010 3 27 257.66
2010 3 28 147.94
2010 3 30 12.97
2010 3 2389.80
2010 4 1 260.56
2010 4 2 179.29
2010 4 3 65.51
2010 4 4 77.78
2010 4 5 179.42
2010 4 6 18.52
2010 4 7 58.29
2010 4 9 43.44
2010 4 10 139.54
2010 4 14 92.79
2010 4 15 154.14
2010 4 16 80.25
2010 4 17 100.18
2010 4 19 52.41
2010 4 20 33.76
2010 4 23 175.51
2010 4 24 64.88
2010 4 25 78.67
2010 4 28 115.39
2010 4 29 44.89
2010 4 30 64.33
2010 4 2079.55
2010 5 1 79.92
2010 5