sql常用语法合集

sql常用语法合集

1. case when 的用法sum(case when x = x then 1 else 0)

select sum (case when isintroduce = '0' then 1 else 0 end) noreg,sum (case when isintroduce ='1' then 1 else 0 end )reg from lae05 where 1=1

2. 时间格式转换 date_format(field,'%Y-%m-%d')

后面的格式可以自定义修改比如%m-%d 或者%m.%d

select count(1) as total ,
    sun(case when status ='end' then 1 else 0 ) as finish
from 
    fault where date_format(create_time,'%Y-%m-%d') >='2020-10-01' 
    and  date_format(create_time,'%Y-%m-%d') < '2020-12-12'

3. 百分比结果计算 round((a/b),2)

round函数,第一个参数是对结果进行计算,第二位是保留的小数点

select 
   *,round((a.finish/a.total),2)*100 as percent
from (
    select 
        count(1) as total,
        sum(case when status =1 then 1 else 0) as finish 
    from fault where sorce between 12 and 50
) as a

4. 分组(聚合)group by

select 
    name,age,course
from job group by course order b by userid

5. 对两张表相同字段求和并计算百分比

两个表拥有相同的字段

-- 单独一张表求和
select sum(case when status ='end') as end ,count(1) as total from fault;
select sum(case when status ='end') as end ,count(1) as total from repair;
-- 两张表结果求和并计算百分比
select sum(a.end) end,sum(a.total) total,round((end/total),2) as percent from (
select sum(case when status ='end' then 1 else 0 end) end,count(1) as total from fault
union all 
select sum(case when status ='end' then 1 else 0 end) end,count(1) as total from repair)a

6. 两张表相同字段求和并分组计算

思路,先将字段相同的全连接查询,然后根据指定的条件进行分组

SELECT
    *, round((b. END / b.total), 2) * 100 AS percentage
FROM
    (
        SELECT
            date_format(a.create_date, '%m-%d') AS date,
            sum(
                CASE
                WHEN a. STATUS = 'end' THEN
                    1
                ELSE
                    0
                END
            ) AS
        END,
        count(1) AS total,
        processing_person_id AS processId
    FROM
        (
            SELECT
                create_date,
                STATUS,
                process_id,
                processing_person_id
            FROM
                fault
            UNION ALL
                SELECT
                    create_date,
                    STATUS,
                    process_id,
                    processing_person_id
                FROM
                    REPAIR
        ) a
    WHERE
        date_format(a.create_date, '%Y-%m-%d') >= '2020-03-01'
    AND date_format(a.create_date, '%Y-%m-%d') >= '2020-03-01'
    GROUP BY
        processing_person_id
    ORDER BY
        processing_person_id
    ) b

-- 结果如下所示
date | end| total| percentage
--------------------------
03-22   7   12      58.00
--------------------------
04-09   0   3   3   0.00
--------------------------
04-09   0   1   4   0.00
--------------------------
03-05   0   6   18  0.00
--------------------------
04-01   0   2   22  0.00
--------------------------

7. CAST函数说明以及使用

cast函数的作用是实现数据类型的转换,函数包括两个参数,元数据和新的数据类型
示例:

    -- 将整型类型转换成字符串
    select cast(9, as CHAR);
    -- 将字符串转换成时间格式 
    selec case(NOW() as DATE);

需要注意的事 cast函数对转换的类型也是有限值的

• 二进制，同带binary前缀的效果 : BINARY
• 字符型，可带参数 : CHAR()
• 日期 : DATE
• 时间: TIME
• 日期时间型 : DATETIME
• 浮点数 : DECIMAL
• 整数 : SIGNED
• 无符号整数 : UNSIGNED

8. ConCAT函数的使用

concat函数的作用是对结果集进行字符拼接,一般出现比较多的事在百分比计算中

-- 计算小明的所有成绩及格率  
select *,concat(cast(round((a.pass/a.count),2) as CHAR),'%') as percentag from (
select 
    count(1) as count,
    sum(case when grade > 60 then 1 else 0 end) as pass 
from result) a 

9. 讲多个表的结果集，组装新的字段以一条数据显示

select 
    a1.count sw,
    a2.count org,
    a3.count project  
from (
    SELECT 
        count(*) 
    from 
        lap21
    where 
        areaid >= 'areaid' and 
        areaid < 'maxAreaid') a1,
    (
    select 
        count(*) 
    from 
        lae01 
    where 
        areaid >= ' areaid ' and 
        areaid < ' maxAreaid ') a2,
    (
    SELECT 
        count(1) 
    from 
        lax03 
    where 
        areaid >= ' areaid ' and 
        areaid < 'maxAreaid ') a3;

文章永久链接：https://tech.souyunku.com/40828