员工表(employee)中存储了每个部门中的员工信息,部门表(department)中存储了部门的信息。假如我们想要统计每个部门的员工数量,并且以水平直方图的形式进行显示。以下是 MySQL 数据库中的实现:
-- MySQL/MariaDBSELECT d.dept_name "部门名称",count(e.emp_id)"员工数量",repeat('▇',count(e.emp_id))"柱状图"FROM department d
LEFTJOIN employee e ON(d.dept_id = e.dept_id)GROUPBY dept_name
ORDERBYcount(*)DESC;
-- Microsoft SQL ServerSELECT d.dept_name "部门名称",count(e.emp_id)"员工数量",
replicate('▇',count(e.emp_id))"柱状图"FROM department d
LEFTJOIN employee e ON(d.dept_id = e.dept_id)GROUPBY dept_name
ORDERBYcount(*)DESC;-- PostgreSQLSELECT d.dept_name "部门名称",count(e.emp_id)"员工数量",repeat('▇',count(e.emp_id)::integer)"柱状图"FROM department d
LEFTJOIN employee e ON(d.dept_id = e.dept_id)GROUPBY dept_name
ORDERBYcount(*)DESC;-- OracleSELECT d.dept_name "部门名称",count(e.emp_id)"员工数量",
lpad('▇',count(e.emp_id),'▇')"柱状图"FROM department d
LEFTJOIN employee e ON(d.dept_id = e.dept_id)GROUPBY dept_name
ORDERBYcount(*)DESC;-- SQLiteSELECT d.dept_name "部门名称",count(e.emp_id)"员工数量",replace(hex(zeroblob(count(e.emp_id))),'00','█')"柱状图"FROM department d
LEFTJOIN employee e ON(d.dept_id = e.dept_id)GROUPBY dept_name
ORDERBYcount(*)DESC;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
对于 Microsoft SQL Server,我们可以使用 replicate 函数替换 repeat 函数。