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)'