SQL 查询实例

字符串处理

数据准备：

create table n10(
  pos int4
);
insert into n10 (pos) values (1);
insert into n10 (pos) values (2);
-- ... 1 到 10
insert into n10 (pos) values (10);
--
create table table3(
  name text,
  address text
);
insert into table3 (line) values ('aladdin', 'china');

• 串联字符串

-- Postgres / Oracle
select name || ' live in ' || address as msg from table3; -- aladdin live in china
-- MySQL / Postgres
select concat(name, ' live in ', address) as msg from table3; -- aladdin live in china
select concat_ws('_', name, address) from table8; -- aladdin_china

• 查找匹配项

-- PostgreSQL / MySQL
select * from emp where emp_sal in (13000, 30000); -- 使用 in
-- 使用 like
select * from emp where emp_name like 'ala%'; -- 匹配单个字符使用 '_'

• 遍历字符串

-- Postgres
select
       substring(t3.name, iter.pos, 1) as c
from
     table3 t3, n10 iter
where
      iter.pos <= length(t3.name);
-- a
-- l
-- a
-- d
-- d
-- i
-- n

• 统计字符出现的次数

-- Postgres
select
       (length(name) - length(replace(name, 'a', ''))) as num
       -- 统计字符 a 出现的次数
from table3; -- 2

• 删除不想要的字符

-- Postgres
select
       replace(translate(line, 'AEIOUaeiou', 'aaaaaaaaaa'), 'a', '')
       -- 将元音字符删除
from table3;

• 分离字符串和数字

-- 准备工作
create table table1(
  line text
);
insert into table1 (line) values ('Jim1006');
-- Postgres
select
       replace(translate(line, '0123456789', '0000000000'), '0', '') as ename,
       cast(replace(translate(lower(line), 'asdfghjklzxcvbnmqwertyuiop', repeat('z', 26)), 'z', '') as integer) as year
from table1 t1;

• 按字符串中的数字排序

-- Postgres
select line
from table1
order by cast(replace(translate(lower(line), 'asdfghjklzxcvbnmqwertyuiop', repeat('z', 26)), 'z', '') as integer);

• 解析IP地址

create table table2(
  ip text
);
insert into table2 (ip) values ('144.132.11.3');
-- MySQL
select
       substring_index(substring_index(ip, '.', 1), '.', -1) as a,
       substring_index(substring_index(ip, '.', 2), '.', -1) as b,
       substring_index(substring_index(ip, '.', 3), '.', -1) as c,
       substring_index(substring_index(ip, '.', 4), '.', -1) as d
from table2;
-- PostgreSQL
select
       split_part(ip, '.', 1) as a,
       split_part(ip, '.', 2) as b,
       split_part(ip, '.', 3) as c,
       split_part(ip, '.', 4) as d
from table2;

数值处理

数据准备：

create table table3(
  id int4,
  score double precision
);
insert into table3 (id, score) values (1001, 97);
insert into table3 (id, score) values (1001, 95);
insert into table3 (id, score) values (1001, 98);
insert into table3 (id, score) values (1002, 93);
insert into table3 (id, score) values (1002, 99);
insert into table3 (id, score) values (1003, 94);
insert into table3 (id, score) values (1003, 99);

• 计算均值

select avg(score) from table3;
-- 分组求均值
select id, round(avg(score)::numeric, 2) from table3 group by id;
select id, round(avg(coalesce(t3.score, 0))::numeric, 2) from table3 t3 group by id;

• 去掉极值求均值

select
       avg(score)
from
     table3
where
      score not in (
                    (select max(score) from table3),
                    (select min(score) from table3)
                   );

• 最大值与最小值

select min(score) as min, max(score) as max from table3;
-- 分组求最大值与最小值
select id, min(score) as min, max(score) as max from table3 group by id;
select id, min(coalesce(score, 0)) as min, max(coalesce(score, 0)) as max from table3 group by id;

• 求和

select sum(score) as sum from table3;
-- 分组求和
select id, sum(score) as sum from table3 group by id;
select id, sum(coalesce(score, 0)) from table3 group by id;

• 计算行数

-- count(*) 统计行数，无论非 null 否
select count(*) as count from table3;
-- 计算某列值个数
select count(score) as count -- 如果 score 为 null 则不计数
from table3;
-- 分组求行数
select id, count(*) as count from table3 group by id;

• 求众数

select score
from
     (
       select
              score, (dense_rank() over (order by e.count desc)) as rnk
       from
            (
              select score, count(score) as count
              from table3
              group by score
            ) as e
     ) as t
where t.rnk = 1;

• 计算百分比

-- 计算 id 为 1001 学生分数占全部分数的百分比
select (sum(case when id = 1001 then score end) / sum(score)) * 100 as pct from table3;

日期处理

• 提取年月日

-- Postgres
select extract(year from now()) as year;
-- mon: 月
-- day: 日
-- week: 一年中第几周
-- doy: 一年中第几天

对 NULL 值处理

• 查找 NULL 值

-- PostgreSQL / MySQL
select * from emp where emp_name is null;

• 将 NULL 值转换为实际值

-- PostgreSQL / MySQL
select coalesce(emp_name, 'Secret Superstar') as name from emp;

• 比较中有 NULL 值

-- 数据准备
create table table2(
  name text,
  sal double precision
);
create table table3(
  name text,
  sal double precision
);
insert into table2 (name, sal) values ('aladdin', 12000), ('bilib', null);
insert into table3 (name, sal) values ('chrome', 3000);
-- 使用 coalesce 函数进行 null 值的转换
select * from table2 t2
where coalesce(t2.sal, 0) > (select t3.sal from table3 t3 where t3.name = 'chrome');

CASE...WHEN

• PostgerSQL / MySQL /Oracle

select
     emp_name,
     emp_sal,
     case
       when emp_sal < 10000.0 then 'low'
       when emp_sal >= 10000.0 and emp_sal < 20000.0 then 'mid'
       when emp_sal >= 20000.0 and emp_sal < 50000.0 then 'high'
       else 'very high'
     end as "status"
from emp;

返回指定行数

• PostgreSQL / MySQL

-- 返回指定行数，PostgreSQL 和 MySQL 一样
select * from emp order by emp_sal limit 3;
-- 随机返回 N 行，Postgre 实现
select * from emp order by random() limit 3;
-- 随机返回 N 行，MySQL 实现
select * from emp order by rand() limit 3;

• Oracle

select * from emp where rownum <= 3;

排序

• 基本排序

-- PostgreSQL / MySQL
-- 单字段
select * from emp order by emp_sal desc; -- 将序，默认升序 / asc
-- 多字段
select * from emp order by emp_sal desc, emp_name;
-- 子字段
select * from emp order by substr(emp_name, length(emp_name) - 2);

• 对字段中的数字 / 字母排序

-- 数据处理
create table public.table2(
msg text
);
insert into public.table2 values ('aladdin 800'), ('bilib 300'), ('chrom 600');

-- 下面连个方法对 MySQL 不适用
-- 对字段中的数字排序
select
     *
from
   public.table2
order by
       replace(msg, replace(translate(msg, '0123456789', '#########'), '#', ''), '');
-- 对字段中的字母进行排序
select
     *
from public.table2
order by
       replace(translate(msg, '0123456789', '#########'), '#', '');

• 对 NULL 进行更高级的排序

-- 数据准备
create table table4(
name text,
sal double precision
);
insert into
public.table4(name, sal)
values 
       ('alad', 0),
       ('bilib', 500),
       ('chrome', 1000),
       ('dell', null),
       ('echo', null);


-- 最基本的排序
select * from public.table4 order by sal desc; -- 这样 null 值就会到最上面
-- 让 null 值下来
with temp as(
select name,
       sal,
       case
         when sal is null
           then 0 else 1
         end as is_null
from public.table4
)
select name, sal from temp order by is_null desc, sal desc; -- 其它解决方案类似

• 根据条件排序

select * from table4
order by
       case 
         when name = 'alad'
           then sal
         else name
       end desc;

集合操作

• 合并
  UNION 会去重复项，UNION ALL 不会，如果不是特殊要求，一般都使用 UNION ALL，UNION 相当于在 UNION ALL 的基础上做一次 distinct 操作:

select name, sal from table5
union all
select name, sal from public.table6;

• 交集与差集

-- 数据准备
create table public.table1(
name text,
hobby text
);
insert into
public.table1 (name, hobby)
values
       ('aladdin', 'Ping pong'),
       ('aladdin', 'Computer games'),
       ('bilil', 'Basketball'),
       ('bilib', 'Computer games');

-- Postgres
-- intersect 求交集
select
     hobby
from
   table1
where
    name = 'aladdin'
intersect
select
     hobby
from
   table1
where name = 'bilib'; -- Computer games

-- 使用基础语句实现
select
     t1.hobby
from table1 t1
inner join table1 t2 
  on
    t1.name = 'aladdin' and
    t2.name = 'bilib' and
    t1.hobby = t2.hobby;

-- (A - B) 求差集，Oracle 使用 minus 关键字实现
select
     hobby
from
   table1
where
    name = 'aladdin'
except
select
     hobby
from table1
where
    name = 'bilib'; -- Ping pong

-- 使用子查询实现
select
     t1.hobby
from
   table1 t1
where
    t1.name = 'aladdin' and t1.hobby not in (select t2.hobby from table1 t2 where t2.name = 'bilib');