摘要

写一些SQL时，经常会出现疑问，这么写对不对，然后因此又去花时间搜或者验证。此处便记录一下，后续会持续更新。

正文

本文基于PostgreSQL

一、单表操作

1.) 更新：更新表中字段A的值为字段B的倍数

sql 
1

update test set money = age * 10

2.) 插入：若冲突则更新

以postgresql为例

sql 
1
2
3
4

-- 插入时若冲突则更新
insert into ta(id,name,age) value(?,?,?) on conflict(id) do update set name=?,age=?
-- 插入时若冲突则啥也不干
insert into ta(id,name,age) value(?,?,?) on conflict(id) do nothing

id需要添加唯一约束

3.) case when else end

这个应该算是个基操，但是我用的还是比较少。因此记录

sql 
1
2
3
4
5
6

select case
when sname like '熊%' then '熊'
when sname like '红%' then '红'
else '百家姓'
end as "姓氏" 
from student limit 1000;

二、多表操作

1.) 更新：从历史表数据提取最新数据更新到实时表

有两张表，一张表是new_info一张表是history_info，现在需要从history_info中，将每条数据的最新值更新到new_info

方式一：子查询。效率低

sql 
1
2
3
4

update new_info as n set
money = (select money from history_info hi where hi.id=n.id order by hi.time desc limit 1),
age = (select age from history_info hi where hi.id=n.id order by hi.time desc limit 1),
time = (select time from history_info hi where hi.id=n.id order by hi.time desc limit 1)

方式二：多表联动update。效率高

sql 
1
2
3
4
5

update new_info as n set
money = m.money,
age = m.age 
from (select id,money,age,time from history_info hi where (id,time) in (select id,max(time) from history_info group by id) ) as m
where n.id=m.id

注意要set的值，一定不要使用.用法，否则会报错。

2.) 更新：计算Point表所属的Polygon

我有两张表，分别为t_polygon面数据、t_point点数据。现在需要计算出t_point的所属的面。

sql 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

create table t_polygon(
name varchar,
geom geometry
);
create table t_point(
name varchar,
geom geometry,
polygon_name varchar
);
INSERT INTO t_polygon ("name", geom) VALUES('上海', 'SRID=4326;POLYGON ((121.03687783916614 31.35143857490192, 121.09979860176381 30.916327066770677, 121.84656771248656 30.93621266279581, 121.70416817564622 31.27646431189153, 121.2852491280545 31.41787528457955, 121.03687783916614 31.35143857490192))'::geometry);
INSERT INTO t_polygon ("name", geom) VALUES('台湾', 'SRID=4326;POLYGON ((121.19607835215324 25.042293758294534, 121.77524881337806 25.129716861505443, 121.958651447267 24.7708865549712, 121.78490021610654 24.208687567961775, 121.23469045857911 22.730079110419624, 120.9451038360713 22.31099356753988, 120.88718799625815 21.962283602183632, 120.6169088188587 22.042832668743173, 120.36593430277674 22.489483511080607, 120.17287748236848 22.970251783151056, 120.15357282105094 23.360731469806055, 120.58794997102186 24.39343611664809, 120.93545150541252 24.779651312622903, 121.19607835215324 25.042293758294534))'::geometry);
INSERT INTO t_point ("name", geom, polygon_name) VALUES('1', 'SRID=4326;POINT (121.40122844116506 31.140063768088478)'::geometry, NULL);
INSERT INTO t_point ("name", geom, polygon_name) VALUES('2', 'SRID=4326;POINT (120.99973936203358 23.673676426717503)'::geometry, NULL);
INSERT INTO t_point ("name", geom, polygon_name) VALUES('3', 'SRID=4326;POINT (121.19607835215324 25.042293758294534)'::geometry, NULL);
INSERT INTO t_point ("name", geom, polygon_name) VALUES('4', 'SRID=4326;POINT (109.31110958420386 25.607382997704818)'::geometry, NULL);

常用空间关系函数比较。

函数作用是否允许在边界上关系
ST_Intersects(A, B)判断 A 和 B 是否相交允许相交即可
ST_Covers(A, B)A 是否完全覆盖 B允许B 在 A 内部或边界上
ST_Contains(A, B)A 是否严格包含 B不允许B 只能在 A 内部
ST_Within(A, B)A 是否在 B 内部不允许B 严格包围 A

方式一：子查询

sql 
1
2
3
4

update t_point as d
set polygon_name = (
select m.name from t_polygon as m where st_covers(m.geom,d.geom) limit 1
);

方式二：联动update

sql 
1
2
3
4

update t_point as d
set polygon_name = m.name 
from t_polygon m 
where st_covers(m.geom,d.geom);

3.) 查询：从历史表中提取出最新最大的数据

我有一张学生成绩单表。结构如下

sql 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

CREATE TABLE scores (
	id varchar NULL, -- 学生学号
	"name" varchar NULL, -- 学生姓名
	score int8 NULL, -- 学生分数
	subject varchar NULL, -- 考试科目
	createtime int8 NULL -- 考试时间
);
COMMENT ON COLUMN public.scores.id IS '学生学号';
COMMENT ON COLUMN public.scores."name" IS '学生姓名';
COMMENT ON COLUMN public.scores.score IS '学生分数';
COMMENT ON COLUMN public.scores.subject IS '考试科目';
COMMENT ON COLUMN public.scores.createtime IS '考试时间';

需求：查询出每个学生最近考试成绩最高的那一次，要求将所有字段输出。

实现

sql 
 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

with MaxScorePerStudent as (
select
	id,
	name,
	score,
	subject,
	createtime,
	row_number() over (
partition by id
order by
	score desc,
	createtime desc
) as row_num
from
	scores
where
	score > 80

)

select
	id,
	name,
	score,
	subject,
	createtime
from
	MaxScorePerStudent
where
	row_num = 1

4.) 查询：分片表union成视图，进行数据分析

学生成绩表，每天会将昨天的历史成绩归档到 scores$yyyyMMdd 的表中。

但是，在一些场景中，还是需要将这些分片表合并成视图后，进行数据分析。此处最简单的做法就是 union all 实现。

先了解 union all 与 union 的区别。

特性UNION ALLUNION
去重不去除重复行，保留所有行去除重复行，只保留唯一的行
性能影响比较低，因为不需要额外的去重操作比较高，因为需要额外的去重操作
结果集行数结果集行数等于所有查询结果行数总和结果集行数可能少于所有查询结果行数总和，取决于去重操作
使用场景当不需要去重并且需要尽快获取结果时使用当需要合并结果并确保唯一行时使用
语法SELECT ... FROM ... UNION ALL SELECT ... FROM ...SELECT ... FROM ... UNION SELECT ... FROM ...

实现

sql 
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23

-- 创建一个函数来动态生成 UNION ALL 查询语句
CREATE OR REPLACE FUNCTION generate_union_all_view()
RETURNS VOID AS $$
DECLARE
    query_text TEXT := '';
    today DATE := current_date;
    archive_date DATE;
BEGIN
    FOR archive_date IN SELECT generate_series((today - interval '30 days')::date, today, interval '1 day')::date
    LOOP
        query_text := query_text || 'SELECT * FROM scores$' || to_char(archive_date, 'YYYYMMDD') || ' UNION ALL ';
    END LOOP;

    -- 去除末尾的 ' UNION ALL ' 部分
    query_text := left(query_text, length(query_text) - length(' UNION ALL '));

    -- 创建视图
    EXECUTE 'CREATE OR REPLACE VIEW scores_archive AS ' || query_text;
END;
$$ LANGUAGE plpgsql;

-- 调用函数生成并创建视图
SELECT generate_union_all_view();

视图会对其引用的基础表进行强连接（strong reference）。这意味着，只要视图存在，引用的基础表就不能被删除，因为视图依赖这些表。

5.) 查询插入：将某表查询出来的数据插入新表

sql 
1
2
3
4
5

-- 常规的插入语句
insert into test_data(name,age) values('1',1),('2',2)
-- 将查询出来的内容，插入到表里
insert into test_data (name, age)
select ('Name_',generate_series(1, 2000)),  (random() * 100)::int4

6.) 删除插入：删除的数据返回为一张新表

sql 
1
2
3
4
5
6
7
8

-- 将删除的内容，插入到表里
with deleted_rows as (
  delete from test_data
  where id in (select id from test_data limit 1) 
  returning name,age
)
insert into test_data (name, age)
select name,age from deleted_rows;