sql的union与on duplicate key update与row number()

union 会去重。union all会保留重复值

select count(1) as num from detail where bid = :x1 
union select count(1) as num detail where cid = :x3 and bid is null

两个count都是1时就只会返回一个1，使用union all会返回两个1

如果需要排序和分页，必须在union 后再包一层select再进行排序和分页，注意给别名n，没有别名会报错

select * from (select id,type,info,createdate from detail where bid = :x1 
union select id,type,info,createdate from detail where cid = :x3 and bid is null) n 
order by createdate desc, id desc limit :x1,:x2

row_number() over(partition by 分组列 order by 排序列 desc)

row_number() over()分组排序功能：
在使用 row_number() over()函数时候，over()里头的分组以及排序的执行晚于 where 、group by、 order by 的执行。
https://blog.csdn.net/qq_25221835/article/details/82762416

select * from (
    select orderid, row_number() over(order by updatedate asc) as index_number from order) c
where index_number > 100 AND index_number <= 200

这里会按更新时间正序排好，取排好后的100到200条

on duplicate key update
如果没有就新增，有就修改某几个字段

INSERT INTO t (did, aid, createdate, ip,status,type,updatedate) 
VALUES (:x1, :x2, NOW(), :x3, :x4, :x5, :x6) 
on duplicate key update status=values(status),updatedate=values(updatedate)'