sql记录

关联更新 通用写法 适合 mysql oracle 高斯 

update table_a a
set a.name=(select b.name from table_b b where a.id=b.id)
where a.id in (select b.id from table_b b)

 

 

窗口函数 

drop table if exists tt;
CREATE TABLE tt (
id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
video_id INT NOT NULL COMMENT '视频ID',
author INT NOT NULL COMMENT '创作者ID',
datedt date
)
;

 

INSERT INTO tt (id, video_id, author, datedt) VALUES(1, 1, 1, '2021-01-01');
INSERT INTO tt (id, video_id, author, datedt) VALUES(3, 1, 4, '2021-01-01');
INSERT INTO tt (id, video_id, author, datedt) VALUES(2, 1, 3, '2021-01-02');
INSERT INTO tt (id, video_id, author, datedt) VALUES(4, 1, 6, '2021-01-03');
INSERT INTO tt (id, video_id, author, datedt) VALUES(5, 2, 3, '2021-01-01');
INSERT INTO tt (id, video_id, author, datedt) VALUES(6, 2, 4, '2021-01-04');
INSERT INTO tt (id, video_id, author, datedt) VALUES(7, 2, 4, '2021-01-05');
INSERT INTO tt (id, video_id, author, datedt) VALUES(8, 3, 6, '2021-01-06');
INSERT INTO tt (id, video_id, author, datedt) VALUES(9, 3, 4, '2021-01-07');
select
a.*,
sum(author)over (partition by video_id
order by
datedt) as '分组+排序+累加',
sum(author)over (partition by video_id
order by
datedt rows 1 preceding)as '分组+排序+最近2个'
,sum(author) over(
order by datedt rows between 1 preceding and current row) as '当前行+前一行'
,sum(author) over(
order by datedt rows between 1 preceding and 1 following ) as '后一行+当前行+前一行'
,sum(author) over(
order by datedt rows between unbounded preceding and 1 following ) as '后一行+当前行+前面所有'
,sum(author) over( order by datedt range between interval '2' day preceding and interval '2' day following )
from
tt a;