2021-09-25

Oracle 查询特性

排序

空值排序

Oracle将NULL作为无限大，MySQL将NULL作为无限小
Oracle order NULL LAST by default
How ORDER BY and NULL Work Together in SQL

WITH

语法

with w_dual as (
  select * from dual
)
SELECT * FROM w_dual

保留最新数据

delete from T_TZZ_TZZ_MEMBER where id in (
  with w_member_error as (
    select * from T_TZZ_TZZ_MEMBER where stu_id in (
	  select stu_id from T_TZZ_TZZ_MEMBER group by stu_id having count(1) > 1
    )
  )
  select id from w_member_error a where id not in(
    select id from w_member_error a where not exists (
        select 1 from w_member_error b where a.modify_date < b.modify_date and a.stu_id = b.stu_id
    )
  )
);

PARTITION BY

初始化分组编号

UPDATE omc_om_wtsafety a SET a.safetynum = (
    SELECT row_number() OVER(PARTITION BY wtnum ORDER BY wtsafetynum ASC) seqnum
    FROM omc_om_wtsafety b
    WHERE a.wtsafetyid = b.wtsafetyid
);

获取最新记录

update T_GUANGGAO_NR a set UPDATED_AT = (
  select b.OPERATORTIME from (
    select id, NOTICEID, OPERATORTIME, CONTENT,
		row_number() OVER(PARTITION BY NOTICEID ORDER BY OPERATORTIME desc) rn
    from T_ZIXUN_LOG
  ) b where rn = 1 and a.id = b.NOTICEID
), UPDATED_CONTENT = (
  select b.CONTENT from (
    select id, NOTICEID, OPERATORTIME, CONTENT,
		row_number() OVER(PARTITION BY NOTICEID ORDER BY OPERATORTIME desc) rn
    from T_ZIXUN_LOG
  ) b where rn = 1 and a.id = b.NOTICEID
);

Hierarchical Queries

START WITH specifies the root row(s) of the hierarchy.
CONNECT BY specifies the relationship between parent rows and child rows of the hierarchy.
ORDER SIBLINGS BY preserve ordering within the hierarchy.
CONNECT_BY_ROOT
SYS_CONNECT_BY_PATH

select last_name, employee_id, manager_id, level
from employees
start with employee_id = 100
connect by prior employee_id = manager_id
order siblings by last_name;

参考

Hierarchical Queries

DATABASE LINK

CREATE DATABASE LINK

(DESCRIPTION =
	(ADDRESS_LIST =
		(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.137.210)(PORT = 1521))
	)
	(CONNECT_DATA =
		(SERVER = DEDICATED)
		(SERVICE_NAME = orcl)
	)
)

2021-09-23

China Removed Movies

截至2022年06月状态

外语

Chung Kuo - Cina (1972)
Seven Years in Tibet (1997)
South Park (1997-)
V for Vendetta (2005)
Mao’s Last Dancer (2009)
The Lady (2011)
광해, 왕이 된 남자 光海，成为王的男人 (2012)
Red Dawn (2012)
The Interview (2014)
택시운전사 A Taxi Driver (2017)
1987: When the Day Comes (2017)
Years and Years (2019)
One Child Nation (2019)
The Laundromat (2019)
Chernobyl (2019)
Monster Hunter (2020)
인민을 위해 복무하라 为人民服务 (2022)

华语

再見中國 (1978)
皇天后土 (1981)
省港旗兵4：地下通道 (1990)
天浴 (1998)
蝴蝶 (2004)
20 30 40 (2004)
颐和园 (2006)
夹边沟 (2010)
他们在毕业的前一天爆炸 (2010)
垃圾围城 (2012)
我还有话要说 (2012)
天注定 (2013)
少年＊小趙 (2015)
穹顶之下 (2015)
十年 (2015)
大同 (2015)
塑料王国 (2016)
凶年之畔 (2017)
返校 (2019)
時代革命 (2021)

补充

根据个人观察，豆瓣的粗略级别划分有：

内容只读，不允许对电影做出评论
- 比如《建国大业》
仅对登录用户可查看和可搜索
- 比如《少年＊小趙》
主条目不存在，可搜索少量相关内容
- 比如《颐和园》
关键字过滤，完全禁止搜索
- 比如《西藏七年》

参考

2021-09-21

日光和健康

The ultraviolet radiation in sunlight has both positive and negative health effects, as it is required for the synthesis of vitamin D3 and is a mutagen. Vitamin D has been suggested as having a wide range of positive health effects, which include strengthening bones and possibly inhibiting the growth of some cancers. UV exposure also has positive effects for endorphin levels, and possibly for protection against multiple sclerosis.

Long-term sunlight exposure is known to be associated with the development of some types of skin cancer, skin aging, immune suppression, and eye diseases such as cataracts and macular degeneration. Conversely, sun avoidance is associated with increased mortality.

Ultraviolet

Ultraviolet index

2021-09-21

Human

Keywords

Coevolution

Sexual reproduction & Asexual reproduction

Red Queen hypothesis

The Red Queen hypothesis is a hypothesis in evolutionary biology proposed in 1973, that species must constantly adapt, evolve, and proliferate in order to survive while pitted against ever-evolving opposing species. The hypothesis was intended to explain the constant (age-independent) extinction probability as observed in the paleontological record caused by co-evolution between competing species; however, it has also been suggested that the Red Queen hypothesis explains the advantage of sexual reproduction (as opposed to asexual reproduction) at the level of individuals, and the positive correlation between speciation and extinction rates in most higher taxa.

2021-09-18

Automotive Industry

Daimler AG (1886)

Brand

Mercedes-Benz

Ford (1903)

General Motors (1908)

Mazda Motor (1920)

Volkswagen Group (1937)