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;