常用SQL：修订间差异
跳到导航
跳到搜索
（→固定长度数字） |
（→MySQL） |
||
| （未显示同一用户的5个中间版本） | |||
| 第1行： | 第1行： | ||
== MySQL == | ==== 空值转为 0 ==== | ||
* MySQL | |||
case when (col > '') then col else 0 end | |||
==== 固定长度数字 ==== | |||
* MySQL | |||
substr(concat('00000', floor(rand() * 1000000)), -6) | |||
==== 近一百天内时间 ==== | |||
* Doris | |||
date_add(now(), - rand() * 100) | |||
* MySQL | |||
date_add(now(), interval - RAND() * 100 day) | |||
==== 生成连续数字 ==== | ==== 生成连续数字 ==== | ||
* MySQL | |||
with recursive seq(no) as( | with recursive seq(no) as( | ||
select 1 no | select 1 no | ||
| 第10行： | 第24行： | ||
select no | select no | ||
from seq | from seq | ||
* MySQL, Doris, Oracle | |||
select | with t1 as ( | ||
with a as ( | |||
SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL | |||
SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 | |||
), | |||
b as ( | |||
SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL | |||
SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 | |||
) | |||
select 1 from a, b | |||
), | |||
t2 as ( | |||
with a as ( | |||
SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL | |||
SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 | |||
), | |||
b as ( | |||
SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL | |||
SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 | |||
) | |||
select 1 from a, b | |||
), | |||
t3 as ( | |||
with a as ( | |||
SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL | |||
SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 | |||
), | |||
b as ( | |||
SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL | |||
SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 | |||
) | |||
select 1 from a, b | |||
limit 100 | |||
) | |||
select 1 from t1, t2, t3 | |||
[[分类:Develop]] | [[分类:Develop]] | ||
[[分类:DB]] | [[分类:DB]] | ||
2024年1月22日 (一) 13:13的最新版本
空值转为 0
- MySQL
case when (col > ) then col else 0 end
固定长度数字
- MySQL
substr(concat('00000', floor(rand() * 1000000)), -6)
近一百天内时间
- Doris
date_add(now(), - rand() * 100)
- MySQL
date_add(now(), interval - RAND() * 100 day)
生成连续数字
- MySQL
with recursive seq(no) as(
select 1 no
union all
select no+1 no
from seq
where no < 100
)
select no
from seq
- MySQL, Doris, Oracle
with t1 as (
with a as (
SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL
SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
),
b as (
SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL
SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
)
select 1 from a, b
),
t2 as (
with a as (
SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL
SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
),
b as (
SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL
SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
)
select 1 from a, b
),
t3 as (
with a as (
SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL
SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
),
b as (
SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL
SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
)
select 1 from a, b
limit 100
)
select 1 from t1, t2, t3