欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

【SQL】SQL语句积累

程序员文章站 2022-05-25 17:08:14
...

SQL函数

1.CONCAT()函数:用于拼接需要输出结果(注意中文需要用’'包住)

例:结果集中,name=胡聪 age=23 性别=男,想要输出diy字段

SELECT su.name ,su.age ,su.sex , CONCAT('姓名', su.name,'年龄',su.age, '性别',su.sex) AS memo
FROM sys_user

输出:

name age sex memo
胡聪 23 姓名胡聪年龄23性别男

2.ROUND()函数:用于把数值字段舍入为指定的小数位数

SQL ROUND() 语法:SELECT ROUND(column_name,decimals) FROM table_name
column_name:需要四舍五入的字段名 decimals:保留的小数位数

实际案例

1.通过SQL计算出某列数据所占总和百分比

需求:需要统计指定时间内,榜单材料的重量占比,并按高低进行排序
思路:按材料名称进行分组,统计出每种材料的总重量,再除以材料总的重量
SQL:子查询作为结果、ROUND()函数、分组GROUP BY、排序ORDER BY、字符串拼接CONCAT()

SQL语句:

SELECT
	a.materialDetailName,
	a.realWeight,
	CONCAT( ROUND( a.realWeight / b.totalRealWeight * 100, 2 ), '', '%' ) AS percent 
FROM
	(
SELECT
	bbd.material_name AS materialDetailName,
	IFNULL( sum( bbd.real_weight ), 0 ) AS realWeight 
FROM
	bi_bill_detail bbd
	LEFT JOIN bi_bill bb ON bb.id = bbd.bill_id 
WHERE
	1 = 1 
	AND bb.del_flag = 0 
	AND bbd.material_name IS NOT NULL 
	AND bb.office_id IN ( 033 ) 
	AND bb.in_date >= '2020-01-01 00:00:00' 
	AND bb.in_date <= '2020-12-31 23:59:59' 
GROUP BY
	bbd.material_name 
ORDER BY
	realWeight DESC 
	) AS a,
	(
SELECT
	IFNULL( sum( bbd.real_weight ), 0 ) AS totalRealWeight 
FROM
	bi_bill_detail bbd
	LEFT JOIN bi_bill bb ON bb.id = bbd.bill_id 
WHERE
	1 = 1 
	AND bb.del_flag = 0 
	AND bbd.material_name IS NOT NULL 
	AND bb.office_id IN ( 033 ) 
	AND bb.in_date >= '2020-01-01 00:00:00' 
	AND bb.in_date <= '2020-12-31 23:59:59' 
	) AS b

输出:

materialDetailName realWeight percent
道砟 1380.911 32.89%
机制砂 1062.64 25.31%
碎石 1010.375 24.06%
AB料 485.24 11.56%
水泥 208.06 4.95%
钢绞线 32.02 0.76%
柴油 19.86 0.47%

参考:

相关标签: Mysql 经验之谈